Can I select multi recs from single recs without cursor?

shell_l_d · #1 (**permalink**) 09-10-10, 03:31

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

pdreyer · #2 (**permalink**) 09-10-10, 10:37

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

shell_l_d · #3 (**permalink**) 09-11-10, 01:40

Thanks heaps pdreyer, that's awesome.

Didn't even know about master..spt_values looks like it will come in handy for other stuff too, cool taa

Wim · #4 (**permalink**) 09-13-10, 04:00

Quote:

Originally Posted by pdreyer

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

Can you point me to a page that explains the values in spt_values? I did a SELECT * from spt_values, but I don't know what all those values stand for.

When Googleing for it, I found many places where it is used (or bashed) but I haven't been able to find a description of it. Nor can I find it in any MSSQL book I have.

pootle flump · #5 (**permalink**) 09-13-10, 05:09

It is a system table for internal use. The only application I have ever seen is by developers as an auxilary numbers table. Personally I prefer a bespoke, user defined numbers table but it is useful when answering questions on forums.

pootle flump · #6 (**permalink**) 09-13-10, 05:13

In fact, as an example of the information it stores look at:

Code:

SELECT  name
      , number
FROM    master.dbo.spt_values
WHERE   type = 'A'

and compare to the options in the BoL entry for sp_serveroption

Internet Services

Web Development

Digital Marketing

Consumer Tech