problem query

Question

problem query

Tracy Avis

Old Hand

Points: 332
More actions
August 1, 2006 at 8:00 pm

#173659

Hi there,
I have a query where there are rows of consecutive dates and I need to count these. My result set loks like this:
2006-07-09 00:00:00.000   0
2006-07-02 00:00:00.000   0
2006-03-19 00:00:00.000   14
2006-03-12 00:00:00.000   0
2006-03-05 00:00:00.000   0
2006-02-26 00:00:00.000   0
2006-02-19 00:00:00.000   0
2006-02-12 00:00:00.000   0
2006-02-05 00:00:00.000   0
2006-01-29 00:00:00.000   0
2006-01-22 00:00:00.000   0
2006-01-15 00:00:00.000   0
2006-01-08 00:00:00.000   0
2006-01-01 00:00:00.000   0
2005-12-18 00:00:00.000   1
2005-11-13 00:00:00.000   4
2005-10-30 00:00:00.000   1
2005-10-23 00:00:00.000   0
2005-10-16 00:00:00.000   0
2005-10-09 00:00:00.000   0
2005-10-02 00:00:00.000   0
2005-09-25 00:00:00.000   0
2005-09-18 00:00:00.000   0
2005-09-11 00:00:00.000   0
2005-09-04 00:00:00.000   0
2005-08-21 00:00:00.000   1
2005-08-14 00:00:00.000   0
2005-08-07 00:00:00.000   0
where there is a number in the right hand column that is the period of weeks where the person has not been in employment (breaks). Where there is a zero, they are the consecutive weeks of employment.
This is code for a report that will show weeks worked, break, weeks worked, break.
I hope this makes sense
Thanks
Tracy

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lance Sanders SSC Eights! Points: 903 More actions · Answer 1

Here's something that may work for you.

I took some liberties in the code due to my lack of understanding of the data. Where you've got periods of unemployment, the dates don't seem to add up. For instance, the 2005-08-21 entry shows 1 week of unemployment, yet the date span between that entry and the next is two weeks. Because of that, I've added 1 to the number of weeks for each unemployment listing (programatically, annotated below), in order for the dates to all be accounted for. Just be aware of that and adjust accordingly if I've misunderstood.

Assuming a table loaded with your data:

  declare @tbl table ( wk_beg smalldatetime, wks_unemp int )

The following may fill your needs:

  select status, beginning, total_wks 
  from (
    select prev_state, status, min(wk_beg) beginning, sum(wks) total_wks 
    from (
      select t.wk_beg, 'EMPLOYED  ' as status, 1 as wks, 
             (select max(wk_beg) from @tbl x where x.wks_unemp != 0 and x.wk_beg < t.wk_beg) as prev_state
      from @tbl t 
      where wks_unemp = 0
        union all
      select t.wk_beg, 'UNEMPLOYED' as status, 
             wks_unemp+1 as wks, --UNEMPL ADJUSTMENT HERE
             (select max(wk_beg) from @tbl x where x.wks_unemp = 0 and x.wk_beg < t.wk_beg) as prev_state
      from @tbl t 
      where wks_unemp != 0
    ) agg
    group by prev_state, status
  ) disp 
  order by beginning

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 2

Check out:

http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=298832

The topic is Measuring Performance, but the query being tweaked performs the task you describe, or at least as I understand it.

Andy

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

David, I think you posted the wrong URL... The one you posted is for THIS thread...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions