select multirecs from single recs without cursor

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)

select multirecs from single recs without cursor

Author

Topic

shell_l_d
Starting Member

17 Posts

Posted - 2010-09-10 : 04:36:16

Wondering if there's a way to do a select query without a cursor (although easy enough to do, curious if can be done without one) to do something like this... here's a small scale sample of what I'm trying to do with a select query...

Sample recs in PublicHols table:
26Jan2010,26Jan2010 -- = 1 day
25Apr2010,25Apr2010 -- = 1 day
25Dec2010,26Dec2010 -- = 2 days

Sample results expected:
26Jan2010,1
25Apr2010,1
25Dec2010,1
26Dec2010,1

Sample results at moment without cursor:
26Jan2010,1
25Apr2010,1
25Dec2010,2 -- want this split into 2 records instead

At moment I'm using this but it can return days > 1, so considering cursor to select them broken up into single records:
select
StartDate,
cast(EndDate-StartDate as integer)+1 as Days
from PublicHols

SQL Server 2000 (& 2005)

shell_l_d
Starting Member

17 Posts

Posted - 2010-09-11 : 02:28:16

Here's test data & a solution from another forum. :)


--===== If test table exists, drop it
 IF OBJECT_ID('TempDB..PublicHols','U') IS NOT NULL
	DROP TABLE PublicHols

--===== Create test table 
 CREATE TABLE PublicHols 
        (
        ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	Descr CHAR(64),
        StartDate DATETIME,
	EndDate DATETIME
        )

--===== Special conditions 
 SET DATEFORMAT DMY

--===== Insert test data into test table
 INSERT INTO PublicHols (Descr,StartDate,EndDate)
 SELECT 'Fiestas Patrias','17/09/2007', '19/09/2007'
 UNION ALL
 SELECT 'Thanksgiving Break','22/11/2007', '23/11/2007'
 UNION ALL
 SELECT  'Australia Day','26/01/2010', '26/01/2010'
 UNION ALL
 SELECT  'Anzac Day','25/04/2010', '25/04/2010'
 UNION ALL
 SELECT  'Christmas Break','25/12/2010', '26/12/2010'

--==== Gather the data
select 
	h.ID,
	h.Descr,
	h.StartDate,
	h.EndDate,
	cast(h.EndDate-h.StartDate as integer)+1 as Days
from PublicHols h 

--==== One solution to the problem (from another forum)
select 
	h.ID,
	h.Descr,
	dateadd(dd, n.number, h.StartDate) as HolDate,
	1 as Days
from master..spt_values n
	join PublicHols h on n.type = 'P' 
			  and dateadd(dd,n.number,h.StartDate) between h.StartDate and h.EndDate

SQL Server 2000 (& 2005)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources