Is it possible to merge date from 2 or 3 tables into a View?

Suppose I have three tables Type1Events, Type2Events, and Type3Events where
each table has a DateTime column for the date and time the event occurs,
along with some data specific to the event. Can I pull all events from the
three tables into a single View so that the data appears as if it were one
single table, and use a where condition for the date range so that only
events in a certain date range for all three tables appear? So my output
might look like this
EventDate Type
=================
1/1/2001 1
1/2/2001 2
2/1/2003 3
... etc...
Not sure if this makes sense but. I really don't care about the data in the
tables for this example, only the ocurrances in dates ranges for all three.
thanks,
JIM
EventTables
CREATE TABLE dbo.Type1Events (
id int NOT NULL IDENTITY (1, 1),
eventDate datetime NOT NULL,
somedata text NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE dbo.Type2Events (
id int NOT NULL IDENTITY (1, 1),
eventDate datetime NOT NULL,
somedata text NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE dbo.Type3Events (
id int NOT NULL IDENTITY (1, 1),
eventDate datetime NOT NULL,
somedata text NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO Type1Events ( eventDate, someData ) values ( '1/1/2001',
'data1' )
INSERT INTO Type1Events ( eventDate, someData ) values ( '1/1/2002',
'data2' )
INSERT INTO Type2Events ( eventDate, someData ) values ( '1/1/2003',
'data3' )
INSERT INTO Type2Events ( eventDate, someData ) values ( '2/1/2002',
'data4' )
INSERT INTO Type3Events ( eventDate, someData ) values ( '3/1/2001',
'data5' )
INSERT INTO Type3Events ( eventDate, someData ) values ( '4/1/2002',
'data6' )
INSERT INTO Type2Events ( eventDate, someData ) values ( '5/1/2006',
'data7' )
INSERT INTO Type1Events ( eventDate, someData ) values ( '6/1/2004',
'data8' )
INSERT INTO Type3Events ( eventDate, someData ) values ( '7/1/2005',
'data9' )
GOcreate view events
as
select eventDate, someData from Type1Events
union all
select eventDate, someData from Type2Events
union all
select eventDate, someData from Type3Events
go|||Sorry, a more complete solution is:
create view events
as
select 1 as eventType, eventDate, someData from Type1Events
union all
select 2, eventDate, someData from Type2Events
union all
select 3, eventDate, someData from Type3Events
go|||Jeff,
Thanks a lot!, I figured it should be easy but havn't used union much so...
thanks,
JIM
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1142016783.270339.21280@.u72g2000cwu.googlegroups.com...
> Sorry, a more complete solution is:
> create view events
> as
> select 1 as eventType, eventDate, someData from Type1Events
> union all
> select 2, eventDate, someData from Type2Events
> union all
> select 3, eventDate, someData from Type3Events
> go
>

Is it possible to get the month name with only an integer representation of the number

Hi,

Is it possible to get the month name with only an integer representation of the number.

i.e January ,February..... the DATENAME only takes a date as a value.

thanks in advance

Yes, you can use a combination of the DATEADD and DATENAME functions for this. Something like this:

Code Snippet

select number,
datename(mm, dateadd(mm, number - 1, 0))
as monthName
from master.dbo.spt_values (nolock)
where name is null
and number > 0
and number < 13

/*
number monthName
--
1 January
2 February
3 March
4 April
5 May
6 June
7 July
8 August
9 September
10 October
11 November
12 December
*/

|||

Thank you that realy worked perfectly.

can you explain what is name =null

and what is

master.dbo.spt_values (nolock)

sorry for picking your brain I just want to understand it.

|||

Well, the MASTER.DBO.SPT_VALUES (NOLOCK) is a reference to the table that I used. This is an undocumented table and my use here is also undocumented and in general should NOT be propagated! Really, this is a case of me being lazy. You remember here earlier today? I suggested that you make a 12-entry inline table with values 1-12 for each month of the year. This is what I SHOULD be doing here. I am really doing the same thing with this particular reference only in this case my method is completely "unsafe" -- my solution is "quick and dirty" -- like REAL dirty.

The "NAME IS NULL" is just a method of making sure that I am narrowing the list of numbers that will be returned such that no number will appear more than once -- again, "quck and dirty". All I was trying to do was to quickly give you numbers 1-12 (in this case from a very dirty source) so that I could show you how to use the DATEADD and DATENAME functions to correspond to the numeric values for each month (GRRRR this KEYBOARD!!!). The (NOLOCK) portion is designating the "NOLOCK OPTIMIZER HINT" so that I do not incur any locks on the SPT_VALUES table.

I really should have explained this rather than leave you hanging to wander about it. Please forgive me for this infraction.

Kent

Please, someone add commentary to this.

|||

I thought about it a bit over night and realized that you might want to give a look to this article about the virtues of having a "table of numbers":

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

Since twice we have discussed queries that utilizes numbers 1-12, it might be appropriate to give this a look.

|||

Kent Nice work. I closed the thread.

Is it better work with date/time in same field or separated?

Hi,
Is it better work with date/time in same field or separated as below:
varX = '12/12/2003' + '12:45' »» in same field in the table
OR
varX = '12/12/2003' + '12:45' »» in separated fields in the table
varY = '12:45'
Thanks,
VILMAR
BRAZIL
PRAIA GRANDE/SPUse a DATETIME column. This will make the most out of indexing, date range
queries, date comparisons, built in functions like datepart(), dateadd() and
datediff(), and easier legibility.
Since SQL Server doesn't have separate DATE and TIME datatypes, moving them
to separate columns would either mean (a) having useless date information in
the "time" column and useless time information in the "date" column, or (b)
converting the columns to a different datatype. For more information, see
http://www.aspfaq.com/2206
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Vilmar Brazão de Oliveira" <suporte@.hitecnet.com.br> wrote in message
news:#60t$RZxDHA.1704@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it better work with date/time in same field or separated as below:
> varX = '12/12/2003' + '12:45' »» in same field in the table
> OR
> varX = '12/12/2003' + '12:45' »» in separated fields in the table
> varY = '12:45'
> Thanks,
> VILMAR
> BRAZIL
> PRAIA GRANDE/SP
>

Is it a bug?

I have a calendar control in my report
I am clicking on it in a few times and selecting same date. After two or
three times I am getting a run-time error:
Object does not support this property or method.if it looks like a bug and it sounds like a bug
but some obsolete company sits there and says it's a feature?
it's yet another symptom you should have gone with crystal reports
Mark Goldin wrote:
> I have a calendar control in my report
> I am clicking on it in a few times and selecting same date. After two or
> three times I am getting a run-time error:
> Object does not support this property or method.