Getting results using Union

[dougancil]

I have the following query:

SELECT mOnCallAdd.OpName,mOnCallAdd.SchedName,mOnCallAdd.FirstListing, moncallAdd.Initials,
DATEADD(MINUTE, mOnCallAdd.AddTime, DATEADD(DAY, mOnCallAdd.AddDate, '12/31/1899')) as Added,
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime, DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) as OnCallDate,
DATEADD(MINUTE, mOnCallAdd.duration, DATEADD(MINUTE, mOnCallAdd.StartOnCallTime, DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) as OnCallEnd,
convert(smalldatetime,convert(float,T2.Timestamp)/1440-1) as Deleted
FROM mdr.dbo.mOnCallAdd
INNER JOIN (SELECT mOnCallDelete.Timestamp, mOnCallDelete.SchedName FROM mOncallDelete WHERE mOnCallDelete.TimeStamp >= datediff(mi, '1899-12-31 00:00:00.000', @datesince) AND mOnCallDelete.SchedName = @schedname) T2
ON mOnCallAdd.SchedName = T2.SchedName
WHERE mOnCallAdd.TimeStamp >= datediff(mi, '1899-12-31 00:00:00.000', @datesince) AND mOnCallAdd.SchedName = @schedname
ORDER BY OnCallDate

and what I'm trying to do is instead of showing the "added" field and "deleted" field is just to simply have a field called "activity" which will show anything that "activity" (either that something has been added or deleted, and the time and date when this was done) and it was suggested to me to use Union. Here is the DDL:

CREATE TABLE [dbo].[mOnCallAdd] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[RecID] [decimal](18, 0) NOT NULL ,
	[Timestamp] [int] NULL ,
	[SchedName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Rank] [int] NULL ,
	[StartOnCallDate] [int] NULL ,
	[StartOnCallTime] [int] NULL ,
	[Override] [int] NULL ,
	[FirstListing] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Duration] [decimal](18, 0) NULL ,
	[TimeDifference] [decimal](18, 0) NULL ,
	[AddDate] [int] NULL ,
	[AddTime] [int] NULL ,
	[Initials] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Opname] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Comment] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field0] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field1] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field2] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field3] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field4] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field5] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field6] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field7] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field8] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field9] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field10] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field11] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MainSchedName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

Both the moncalladd and moncalldelete tables are the same as far as structure goes.


Here is a sample line of data:

1	70550	55426893	Schedname	0	38490	1020	0	Dr Doctor	840	0	38490	1293	TG	8199

The output I want would be this:

Op Name SchedName       FirstListing             Activity                       OncallStart             OncallEnd
___________________________________________________________________________________________________________________________________________
8467	BUTTERCUP	Data, Sample	2011-03-20 03:57:00 Added 	2011-03-17 17:00:00     2011-03-1808:00:00	
8467    BUTTERCUP	Data, Sample    2011-03-20 03:57:00 Deleted     2011-03-17 17:00:00     2011-03-18 08:00:00

Can anyone please assist.

Thank you.

[ck9663]

Trigger is your best bet...

Good Luck!!!

~~ CK