Results 1 to 3 of 3

Thread: Count by Week

  1. #1
    Join Date
    Apr 2013
    Posts
    2

    Unanswered: Count by Week

    I want to count a list of policies that exist on Monday of each prior week. Policies have a start date and end date. For a policy to be counted, the date on Monday must be >= start date and < end date. I want the report to start pulling from 1/1/2013. It will run each week, filling in all prior weeks back to 1/1/2013. I've been trying to use to_char to turn sysdate into yyyymmdd. That works fine and I can obtain a count based on the current date, but I'm having trouble looping through all prior weeks. Any suggestions on how I can get a list of all Monday dates from 1/1/2013 through the most recent Monday and compare those dates to the start/end dates for policies to get a count?
    Last edited by smithson1; 04-24-13 at 18:13.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Below should get you going in the desired direction
    Code:
      1  select to_date('2012-12-31','YYYY-MM-DD')+(7*LEVEL) FROM DUAL
      2* CONNECT BY LEVEL < 17
    SQL> /
    
    TO_DATE('2012-12-31
    -------------------
    2013-01-07 00:00:00
    2013-01-14 00:00:00
    2013-01-21 00:00:00
    2013-01-28 00:00:00
    2013-02-04 00:00:00
    2013-02-11 00:00:00
    2013-02-18 00:00:00
    2013-02-25 00:00:00
    2013-03-04 00:00:00
    2013-03-11 00:00:00
    2013-03-18 00:00:00
    
    TO_DATE('2012-12-31
    -------------------
    2013-03-25 00:00:00
    2013-04-01 00:00:00
    2013-04-08 00:00:00
    2013-04-15 00:00:00
    2013-04-22 00:00:00
    
    16 rows selected.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2013
    Posts
    2
    Thanks for the reply. I'll give that a shot tomorrow.

Posting Permissions

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