Help with Tricky Select - 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)

Help with Tricky Select

Author

Topic

slserra
Starting Member

19 Posts

Posted - 2003-10-30 : 17:27:50

I have the following table:

create table #Event
(
PKId INT,
Title VARCHAR(50),
RecurId INT,
RecurException CHAR(1),
StartTime DATETIME,
EndTime DATETIME
)

INSERT #Event VALUES (1336,'Playdate',76,'','2003-10-30 9:00:00.000','2003-10-30 10:00:00.000')
INSERT #Event VALUES (1335,'Reg Event',NULL,'','2003-10-30 9:00:00.000','2003-10-30 10:00:00.000')
INSERT #Event VALUES (1337,'Playdate-Ex',76,'Y','2003-11-06 12:00:00.000','2003-11-06 14:00:00.000')
INSERT #Event VALUES (1338,'Playdate',76,'','2003-11-13 9:00:00.000','2003-11-13 10:00:00.000')
INSERT #Event VALUES (1339,'Playdate',76,'','2003-11-20 9:00:00.000','2003-11-20 10:00:00.000')
INSERT #Event VALUES (1340,'Playdate',76,'','2003-11-27 9:00:00.000','2003-11-27 10:00:00.000')
INSERT #Event VALUES (1400,'Meeting',77,'','2003-11-13 9:00:00.000','2003-11-13 10:00:00.000')
INSERT #Event VALUES (1401,'Meeting',77,'','2003-11-14 9:00:00.000','2003-11-14 10:00:00.000')

I'm trying to create a query that will return:
1. All non-recurring events (ie. RecurId is null)
2. All recurring event excpetions (RecurId > 0 and RecurException=Y)
3. Only ONE recurring event for each recurrance.

For example, the query should produce the following:
PKId Title RecurId RecurException StartTime End Time
1336 Playdate 76 2003-10-30 9:00 2003-10-30 10:00
1335 Reg Event NULL 2003-10-30 9:00 2003-10-30 10:00
1337 Playdate-Ex 76 Y 2003-11-06 12:00 2003-11-06 14:00
1400 Meeting 77 2003-11-13 9:00 2003-11-13 10:00

Any help would be appreciated.

Steve

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-10-30 : 17:50:36

UNION is your friend......

Business Rules 1 & 2 in first SELECT and Rule 3 in second SELECT


Select * 
from #Event
where RecurId is null OR  (RecurId > 0 AND RecurException='Y')
UNION ALL
Select MIN(PKID) as PKID, Title, MIN(RecurID) RecurID,''  RecurException,  min(StartTime) StartTime, MIN(EndTime) EndTime
from #Event
where (RecurId > 0 AND RecurException!='Y')
group by Title

DavidM

"SQL-3 is an abomination.."

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2003-10-31 : 00:38:39

Second select statement should be:

select * from #Event
where PKID in (
Select MIN(PKID)
from #Event
where (RecurId > 0 AND RecurException!='Y')
group by Title)

As you can see that earlier one gives you wrong date values if you have this record in your table.

INSERT #Event VALUES (1403,'Meeting',77,'','2003-11-12 9:00:00.000','2003-11-12 10:00:00.000')

slserra
Starting Member

19 Posts

Posted - 2003-10-31 : 01:09:08

Thanks for the quick response. It works great!

Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources