SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


select multirecs from single recs without cursor


select multirecs from single recs without cursor

Author
Message
shell_l_d
shell_l_d
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 121
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)
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42075 Visits: 20008
This 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?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
shell_l_d
shell_l_d
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 121
No problem thnx for the posting tip, good idea, I'll do that shortly...
however got this answer from another forum & it works perfectly. . Smile


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


shell_l_d
shell_l_d
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 121
Here it is Smile


--===== 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


steve-893342
steve-893342
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1201 Visits: 2670
A variation using CROSS APPLY

;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



WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21908 Visits: 10653
steve-893342 (9/11/2010)
A variation using CROSS APPLY


This is a SQL 7/2000 forum... the cross apply won't work unless posted in the wrong forum.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

steve-893342
steve-893342
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1201 Visits: 2670
WayneS (9/12/2010)
steve-893342 (9/11/2010)
A variation using CROSS APPLY


This is a SQL 7/2000 forum... the cross apply won't work unless posted in the wrong forum.

shell_l_d (9/10/2010)
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)


Yeh, but it says &2005 in the original post, right?
WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21908 Visits: 10653
steve-893342 (9/12/2010)
WayneS (9/12/2010)
steve-893342 (9/11/2010)
A variation using CROSS APPLY


This is a SQL 7/2000 forum... the cross apply won't work unless posted in the wrong forum.

shell_l_d (9/10/2010)
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)


Yeh, but it says &2005 in the original post, right?


I'll grant you that. My interpretation is that it would need to run on both versions.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search