Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Posts
    4

    Question Unanswered: Break down query by week?

    Hi All,

    Not too experienced with this - so asking for some thoughts...

    I've been asked to create a report, and instead of using start date, and end date as criteria, they want it broken down by week....

    So for instance I will provide a page where they can select 03/2017 (March/2017) as a start date and 5/2017 as an end date, and I need to show the results for each week in between. (results just being a count of records for each week).

    Can this be done in a query? If so, I would love to see any examples...

    Thank you in advance!

  2. #2
    Join Date
    Oct 2007
    Posts
    154
    Provided Answers: 9

  3. #3
    Join Date
    Nov 2002
    Posts
    4

    Week Query

    Thanks Dnance - I think I need more than that - that is a function of PHP to give the week number of a given date....

    My query will return results between full months, using the example above, let's say all records between MARCH 2017 and MAY 2017

    In pseudo code, the query would look like:

    Query all records between March 1, 2017 and May 1, 2017

    I'm guessing it's not possible (with one query), to break the results down by weeks? So the query results would be:

    Record 1 = week 1 of March
    Record 2 = week 2 of March
    Record 3 = week 3 of March
    Record 4 = week 4 of March
    Record 5 = week 1 of April
    etc...

    Didn't know if this was possible or how I can even return results by week...

  4. #4
    Join Date
    Oct 2007
    Posts
    154
    Provided Answers: 9
    it is very easily done. Yes, they do show some PHP code, but the function can be used in your SQL. Similar to:

    Code:
    select week(a_date_col)
         , sum(my_amt_col)
      from my_table
    where a_date_col between :date1 and :date2
    group by week(a_date_col)

  5. #5
    Join Date
    Nov 2002
    Posts
    4

    Almost there...

    I've been playing with this....I think I'm close....two questions with this query:

    select week(datetimestamp), sum(id)
    from table
    where stamp between '2017/4/1 00:00:00' and '2017/5/1 00:00:00' group by week(datetimestamp)

    1) Regarding "sum" in the select - I don't have a column I want to add, rather, I need the count of how many rows are returned for that week. I believe I can do this with either solution below:

    select week(datetimestamp), sum(1)
    select week(datetimestamp), count(*) as mycount

    This returns these results:
    15,243
    16,35
    17,55

    Notice there is no week #14, which is my second question....

    2) The only other question I have is, if there is a week that has no records, it doesn't return a row/count - is there a way to change the query to say if there are no records for that week, to return 0

    Using the above example, I was hoping for this result:
    14,0
    15,243
    16,35
    17,55


    Thanks so much for your help -

  6. #6
    Join Date
    Oct 2007
    Posts
    154
    Provided Answers: 9
    you should be able to do something like the following. keep in mind this is off top of my head and will not work, just to point you in the direction you want to go

    Code:
    select wk_nbr, coalesce(count(*), 0)
       from (select week(datetimestamp) as wk_nbr
                   from dual
                where stamp between '2017/4/1 00:00:00' and '2017/5/1 00:00:00' ) as wk
    left outer join my_table
       on wk.wk_nbr = week(date_col)
      and date_col between '2017/4/1 00:00:00' and '2017/5/1 00:00:00'
    You're building the list of all the week numbers in your range, then outer joining to your table to get the counts. With the outer join you would get the week 14 and a null, the coalesce will replace a null with 0

  7. #7
    Join Date
    Nov 2002
    Posts
    4
    Thank you!

Posting Permissions

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