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 is possible to make index over datepart in datetime column?

For example, i want to make index over dd-mm-yyyy not including hh:mm:ss, or sometimes only mm-yyyy. Is this possible?Yes, that's possible, create a view and then index your view.

Is are a way to convert the SQL datetime to MFC CTime

Hi
Here is the time in SQL : 2/21/2003 12:00:00 PM (DataType :datetime)
How can i convert this into CTime .the MFC class
Help inthis regard is appreciated,
Thanks in AdvanceA datetime is not that format. It is a decimal number - that is just the way it is converted for display.

What format do you need it in to put into a CTime.|||Thanks 'nigelrivett' for replying me
Yes...I reading the date(datetime) from the Data Base as the String..I want Month,Year,Date,Hour and Minute to build the Time for my application.

i like to build the CTime as below:

CTime Ts(nYear,nMonth,nDate,nHour,nMinute,nSecond,o);

So please tell me how get the Hour,minute,Day,Year from the Data Base.

Thanks in advance,
Sirish|||try:
select getdate() as 'Original Date'
, datepart(yy,getdate()) as 'Year'
, datepart(mm,getdate()) as 'Month'
, datepart(dd,getdate()) as 'Day'
, datepart(hh,getdate()) as 'Hour'
, datepart(mi,getdate()) as 'Minute'
, datepart(ss,getdate()) as 'Second'
, datepart(ms,getdate()) as 'Millisecond'