using Union - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

using Union

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2011-04-12 : 08:52:51

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. Can someone tell me where the best place to put the union operator?

Thank you

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-12 : 09:09:42

With UNION you will have 2 rows in your resultset instead of 1 row with 2 columns.
Without table structure, sample data and wanted output it is not easy to understand your needs.

No, you're never too old to Yak'n'Roll if you're too young to die.

dougancil
Posting Yak Master

217 Posts

Posted - 2011-04-12 : 10:03:27

Webfred,

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

I hope that explains things better.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources