InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 08:09:17 GMT20http://www.sqlservercentral.com/Forums/Topic983603-8-1.aspx[quote][b]steve-893342 (9/12/2010)[/b][hr][quote][b]WayneS (9/12/2010)[/b][hr][quote][b]steve-893342 (9/11/2010)[/b][hr]A variation using CROSS APPLY[/quote]This is a SQL 7/2000 forum... the cross apply won't work unless posted in the wrong forum.[/quote][quote][b]shell_l_d (9/10/2010)[/b][hr]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...SQL Server 2000 (& 2005)[/quote]Yeh, but it says &2005 in the original post, right?[/quote]I'll grant you that. My interpretation is that it would need to run on both versions.Sun, 12 Sep 2010 16:20:02 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic983603-8-1.aspx[quote][b]WayneS (9/12/2010)[/b][hr][quote][b]steve-893342 (9/11/2010)[/b][hr]A variation using CROSS APPLY[/quote]This is a SQL 7/2000 forum... the cross apply won't work unless posted in the wrong forum.[/quote][quote][b]shell_l_d (9/10/2010)[/b][hr]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...SQL Server 2000 (& 2005)[/quote]Yeh, but it says &2005 in the original post, right?Sun, 12 Sep 2010 13:46:46 GMTsteve-893342http://www.sqlservercentral.com/Forums/Topic983603-8-1.aspx[quote][b]steve-893342 (9/11/2010)[/b][hr]A variation using CROSS APPLY[/quote]This is a SQL 7/2000 forum... the cross apply won't work unless posted in the wrong forum.Sun, 12 Sep 2010 13:36:41 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic983603-8-1.aspxA variation using CROSS APPLY[code="sql"];WITH cteTally (N) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM master..syscolumns ) SELECT P.ID, P.Descr, Z.HolDate, 1 AS Days FROM PublicHols AS P CROSS APPLY ( SELECT DATEADD(DAY, N, StartDate) - 1 FROM cteTally WHERE N < DATEDIFF(DAY, StartDate, EndDate) + 2 ) AS Z (HolDate) ORDER BY HolDate[/code]Sat, 11 Sep 2010 01:53:14 GMTsteve-893342http://www.sqlservercentral.com/Forums/Topic983603-8-1.aspxHere it is :)[code]--===== 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 dataselect h.ID, h.Descr, h.StartDate, h.EndDate, cast(h.EndDate-h.StartDate as integer)+1 as Daysfrom 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 Daysfrom master..spt_values n join PublicHols h on n.type = 'P' and dateadd(dd,n.number,h.StartDate) between h.StartDate and h.EndDate[/code]Sat, 11 Sep 2010 00:26:26 GMTshell_l_dhttp://www.sqlservercentral.com/Forums/Topic983603-8-1.aspxNo problem thnx for the posting tip, good idea, I'll do that shortly... however got this answer from another forum & it works perfectly. . :)[code]select dateadd(dd, n.number, h.StartDate)from master..spt_values n join PublicHols h on n.type = 'P' and dateadd(dd,n.number,StartDate) between h.StartDate and h.EndDate[/code]Fri, 10 Sep 2010 23:57:19 GMTshell_l_dhttp://www.sqlservercentral.com/Forums/Topic983603-8-1.aspxThis couldn't have come at a better time, Wayne & I had something similar this week. Can you post the ddl for the table, along with a few INSERTs to get some sample data into it?Fri, 10 Sep 2010 02:59:44 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic983603-8-1.aspxWondering 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...[u]Sample recs in PublicHols table:[/u]26Jan2010,26Jan2010 -- = 1 day25Apr2010,25Apr2010 -- = 1 day25Dec2010,26Dec2010 -- = 2 days[u]Sample results expected:[/u]26Jan2010,125Apr2010,125Dec2010,126Dec2010,1[u]Sample results at moment without cursor:[/u]26Jan2010,125Apr2010,125Dec2010,2 -- want this split into 2 records instead[u]At moment I'm using this but it can return days > 1, so considering cursor to select them broken up into single records:[/u]selectStartDate,cast(EndDate-StartDate as integer)+1 as Daysfrom PublicHolsSQL Server 2000 (& 2005)Fri, 10 Sep 2010 02:44:01 GMTshell_l_d