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