Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2009
    Posts
    23

    Question Unanswered: Can I select multi recs from single recs without cursor?

    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

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    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

  3. #3
    Join Date
    Dec 2009
    Posts
    23

    Talking

    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
    Last edited by shell_l_d; 09-11-10 at 02:49.
    SQL Server 2008/2010/2012
    Sybase 12.5.4 (has no functions)

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by pdreyer View Post
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •