Summarize by week with COUNT of two date columns

Question

I want to summarize some COUNT data by week. The week starts on Sunday. The query must account for weeks where the COUNT is 0. The counts are on two dates. A referral date and the service date. All I want to do is count the number of referrals for each week and how many service dates there are in the same week. These two COUNTS are not linked. A referral may come in 1 week but the service get done another week.

My attempt: 1) I created a calendar table with a entry for each day for the period I am working. The date entries cover more than the period being used. 2) I have a field in my Select that summarizes by 'Week Beginning' 3) I have COUNT fields on each of the date fields (Referral, Visits). Note that I use Distinct because there are multiple service codes for a visit but I only need to count them as 1.

Here is the code:

select
STR_TO_DATE(DATE_FORMAT(FROM_DAYS(TO_DAYS(calendar.datefield) - MOD(TO_DAYS(calendar.datefield) -1, 7)),'%m/%d/%Y'), '%m/%d/%Y' ) AS `Week Beginning`,

COUNT(DISTINCT opsData.`Patient Last Name`, opsData.`Patient First Name`, opsData.Discipline, opsData.`Referral Date`) as `Referrals`,

COUNT(DISTINCT opsData.`Patient Last Name`, opsData.`Patient First Name`, opsData.`Date of Service`) as Visits

from opsdata RIGHT JOIN calendar ON (DATE(opsData.`Referral Date`) = calendar.datefield)

where (calendar.datefield BETWEEN (SELECT MIN(DATE(opsData.`Referral Date`)) FROM opsdata) AND (SELECT MAX(DATE(opsData.`Referral Date`)) FROM opsdata))
group by `Week Beginning`
order by `Week Beginning` ASC

The problem i'm having is that I can only get 1 of the COUNTS to be accurate at a time based on the RIGHT JOIN. If I use DATE(opsData.Referral Date) then the Referral COUNT is correct and the Visits is wrong. If I use DATE(opsData.Date of Service) then the Visits is correct and the Referral COUNT is wrong.

Any ideas?

score 0 · Answer 1 · answered Jun 07 '16 at 21:20

0

You need to use the earlies / latest date of the 2 fields. You can use least() and greatest() functions to achieve this:

...BETWEEN (SELECT least(MIN(DATE(opsData.`Referral Date`)),min(date(opsData.`Date of Service`))) FROM opsdata)
   AND (SELECT greatest(MAX(DATE(opsData.`Referral Date`)), max(date(opsData.`Date of Service`))) FROM opsdata))

answered Jun 07 '16 at 21:20

Shadow

33,525
10
51
64

I got the same result with the new code you recommended. To run a test to see if the where clause is the issue I changed it to: calendar.datefield >= date('2016/01/01') and calendar.datefield <= date('2016/06/10') and I got the same result as with your code and my original code. – Gsm Jun 07 '16 at 21:47
In this case you need to provide sample data, actual output you receive from your query and expected outcome as an edit to the question. – Shadow Jun 08 '16 at 00:45

score 0 · Answer 2 · edited May 23 '17 at 12:31

I answered a similar question recently on MySQL: multiple count in one row

What you basically need to do is generate a list of weeks. If you want week commencing and a date as the groups then you can use.

(SELECT date(adddate(datetime, INTERVAL 1-DAYOFWEEK(datetime) DAY)) as week
FROM (
    select (curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) Week) as datetime
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) AS t
where datetime > '2016' -- enter your earliest year here
ORDER BY datetime ASC) week_list

Then what you do is left join queries of counts that you want. ie

(select date_format(timestamp,'%X-%V') theweek, count(id) from table where ______ group by theweek) on week_list.week=table.theweek

NB that the formats of the join have to match if you get what I mean.

Any issues let me know. But hopefully this and the previous post helps you out.

Brett

Summarize by week with COUNT of two date columns

2 Answers2