Dividing by number of days not referencing correct month/number of days

Question

I have a table that users enter a daily population. How many people in a particular facility that day. The table looks similar to this:

select * from stat_summary where MONTH(report_date) = 9

results:

stat_summary_id | report_date | facility | adp
----------------------------------------------------
29              |2015-09-01   | YORK     |  1855
30              |2015-09-02   | YORK     |  1750
31              |2015-09-04   | YORK     |  1655
32              |2015-09-04   | YORK     |  1699

What I want to do is calculate the average daily population grouped by month. I want to take the MAX(report_date) in case a corrected value has to be re-entered. My query looks like:

SELECT
    MONTH(t.report_date) as 'report_month',
    SUM(ss1.adp)/DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,MONTH(t.report_date)),0))),
    DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,MONTH(t.report_date)),0)))
FROM
    stat_summary ss1
    INNER JOIN 
    (SELECT MAX(stat_summary_id) as 'stat_summary_id', report_date  
     FROM stat_summary  
     GROUP BY report_date       
     ) t ON t.stat_summary_id = ss1.stat_summary_id
WHERE
    ss1.facility_id = 'YORK'
    AND MONTH(t.report_date) = 9
GROUP BY    
    MONTH(t.report_date)
ORDER BY
    MONTH(t.report_date)

I've referenced this thread: Dividing a value by number of days in a month in a date field in SQL table

And I was able to see how to dynamically divide by the number of days in the month, but it looks like it is dividing by the current month (October) which has 31 days, when I need the query to divide by the referenced month of September which has 30 days.

Currently my results look like:

The adp value should be 176.8 since there are 30 days in September, not 31.

You could create a scalar UDF that returns the number of days in a month based on the month that you pass to it. — Tab Alleman, Sep 29 '15 at 19:59

score 0 · Accepted Answer · edited May 23 '17 at 10:27

0

~~So quick check it looks like that formula returns 31 for all months. The proper formula can be found here: How to determine the number of days in a month in SQL Server?~~

datediff(day, dateadd(day, 1-day(@date), @date),
          dateadd(month, 1, dateadd(day, 1-day(@date), @date)))

More precisely use:

datediff(day, dateadd(day, 1-day(MIN(t.report_date)), MIN(t.report_date)),
          dateadd(month, 1, dateadd(day, 1-day(MIN(t.report_date)), MIN(t.report_date))))

EDIT: Note the original formula was in fact correct, the problem was that you were passing in a month instead of a day. Months are numbers from 1-12, so all of your dates were in January.

edited May 23 '17 at 10:27

Community

1
1

answered Sep 29 '15 at 20:00

Adam Martin

1,188
1
11
24

Don't use Month(t.report_date), try using Min/Max instead. Month returns a number from 1-12, whereas the function expects a day of the year. – Adam Martin Sep 29 '15 at 20:09
Thanks for the input Adam, I've tried your suggestion: http://pastebin.com/r5KnyzDC But am getting these errors: `Msg 8120, Level 16, State 1, Line 3 Column 't.report_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.` – etm124 Sep 29 '15 at 20:09
You need to take the min/max of t.report_date instead. Note your first version should work with that as well, I got tripped up by that when I quickly checked it out. – Adam Martin Sep 29 '15 at 20:13

score 0 · Answer 2 · answered Sep 29 '15 at 20:58

I should use

day(DateAdd(day, DateAdd(month, MONTH(t.report_date), DateAdd(Year, YEAR(t.report_date)-1900, 0)), -1)) as monthDays

Also sounds to me that to obtain average, it is wrong to divide by that number, it is only correct if the number of records match the number of days in the month, in other case, just the count is enough

SUM(ss1.adp)/count(ss1.adp) as average

Dividing by number of days not referencing correct month/number of days

2 Answers2