Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: Can this be calculeted without a cursor?

    We get data about the hours people have worked at different places. I must give the total hours worked and the dates when a person has worked (at least) 10 hours and (at least) 30 hours.

    I have created a table that stores all the data from all those different systems.

    I can solve this using a cursor, but I would prefer to solve it without one.
    Code:
    create table hoursWorked(
    person	int not null,
    hours	int not null,
    WorkDate datetime not null
    )
    
    INSERT INTO hoursWorked (person, hours, WorkDate)
    select 1, 4, '2009-01-01' UNION ALL 
    select 1, 2, '2009-01-01' UNION ALL --6
    select 1, 3, '2009-01-02' UNION ALL --9
    select 1, 4, '2009-01-03' UNION ALL --13 <- 10 hrs
    select 1, 5, '2009-01-10' UNION ALL --18
    select 1, 1, '2009-01-11' UNION ALL 
    select 1, 2, '2009-01-11' UNION ALL --21
    select 1, 3, '2009-01-12' UNION ALL 
    select 1, 5, '2009-01-12' UNION ALL --29
    select 1, 12, '2009-01-13' UNION ALL --41 <-30 hrs
    
    select 2, 1, '2009-01-01' UNION ALL
    select 2, 12, '2009-01-01' UNION ALL --13 <-10 hrs
    select 2, 1, '2009-01-02' UNION ALL --14 (never 30 hrs)
    
    select 3, 2, '2009-01-01' -- 2
    -- behind -- the cumulated hours a person has worked 
    
    DROP table hoursWorked
    The result should be
    Code:
    person	hours	date_10hrs	date_30hrs
    1	41	2009-01-03	2009-01-13
    2	14	2009-01-01	
    3	2
    It is possible that a person has worked at different locations on the same day.

    I was thinking of writing it in recursive SQL, but I don't seem to get it working.
    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

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Until SQL Server gets the full ISO implementation of the windows functions (OVER clause), cursors are actually more efficient than set based methods for cumulative aggregates. I have a doc authored by Itzik Ben Gan bookmarked somewhere if you want more details...
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh yeah - just reread the title. Yes it can, but inefficiently
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Thank you for your response. The cursor solution is fast.

    I gave the recursive SQL a thought, and I couldn't come up with an elegant (efficient) algorithm to summarise only the hours of the next day (greater than the highest day of the previously calculated cumulative day-hours and the smallest day of the not yet calculated days).
    I have a doc authored by Itzik Ben Gan bookmarked somewhere if you want more details...
    Yes please.
    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
    Dunno about recursive SQL, but you can do it with a theta join.

    Hmm. This looks like the article, but the one I meant is years old. Maybe a rehash?
    http://www.sqlmag.com/Article/Articl...er_101736.html
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - this is the one I meant:
    OVER()

    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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