Sum of values per month divided by days of month

Question

I have a Sales table:

id_item, quantity, date_time

What I need is to sum the items sold in a month and divide them by the days of the month from a selected period of months.

Example - The user selects the dates of Oct 1 to Dec 31. I need to show the items_sold/days_of_month:

Month  Items sold  Days of month  Items/Day
Sep        25           30           0.83333
Oct        36           31           1.16
Dec        15           31           0.4838

I have to specify by Kind of item. the kind is obtained from another table called Items. I use dateformat dd/mm/yy.

select
   month(date_time),
   sum(quantity) / (select(datepart(dd,getdate())))
from
   sales v
   join items a on v.id_item=a.id_item
where
   a.kind='Kind of Item'
   and cast(Convert(varchar(10), date_time, 112) as datetime)
      between '01/10/2012' and '31/12/2012'
group by
   month(date_time)

My problem is selecting the days of the months, how can I select x number of months and divide the sum(quantity) of each month by the days of each?

I know this part of the code only selects the days of the current month:

(select(datepart(dd,getdate())))

why not use `(select(datepart(dd,date_time)))` instead of `(select(datepart(dd,getdate())))` ? — pbhd, Dec 20 '12 at 19:29
@pbhd - `datepart(dd, date_time)` will only return the day number of the date supplied, not the total number of days in the month. See here for determining the number of days in a month: http://stackoverflow.com/questions/691022/how-to-determine-the-number-of-days-in-a-month-in-sql-server — LittleBobbyTables - Au Revoir, Dec 20 '12 at 19:31
What is a "day": a calendar day or a business day? Either way, creating a table with the month number and number of days would be useful for you. And what happens if the user selects a date in the middle of a month as the parameter? Your example uses 20121118, so should the November total be divided by 30 (total days in Nov), by 13 (days to end of Nov, including the 18th), or by something else? — Pondlife, Dec 20 '12 at 19:37
what about that? `DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,,date_time)+1,))`, found it there: http://wiki.lessthandot.com/index.php/Getting_the_First_and_last_days_of_month,_quarter_and_week — pbhd, Dec 20 '12 at 19:39
@pbhd No you didn't. There's no `DateAdd(s ...)` on that page. Why would you calculate the last *second* of the month? Nonsense. — ErikE, Dec 20 '12 at 19:40
Edgar there are many questions to answer: 1) What version of SQL Server. 2) Your example query uses 20121118 - 20121220 but your description says Oct 1 to Dec 31. When the user selects less than a full month period do you want to expand to the full month? 3) If not expanding to the full month, do you want the denominator to be the days in the whole month or the days selected such as 20 days for December? — ErikE, Dec 20 '12 at 19:45
@ErikE 1)Im using Sql Server 2005, and yes i will have to mod how the user selects the two dates, right now other reports are selected this way and i wanted to use the interface already made and mod it later because i need this info. Lets say im the user. 2)My mistake there, im using dd/mm/yy and it would be better if it expands to the full month. — Edgar Holguin, Dec 20 '12 at 19:48
So you always want to expand the query selection range to the full month? You weren't really clear. — ErikE, Dec 20 '12 at 19:48
Agree with @ErikE. Getting the last second of the month is absolutely senseless. What data type does that work with? If you have SMALLDATETIME, it gets rounded up to the next minute; if you have DATETIME or DATETIME2, you potentially miss data. Always use an open-ended range (>= start AND < the day after start). See https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common and https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries — Aaron Bertrand, Dec 20 '12 at 19:49

ErikE · Accepted Answer · 2012-12-21T01:18:28.800

Try this on for size:

DECLARE
   @FromDate datetime,
   @ToDate date; -- inclusive

SET @FromDate = DateAdd(month, DateDiff(month, 0, '20121118'), 0);
SET @ToDate = DateAdd(month, DateDiff(month, 0, '20121220') + 1, 0);

SELECT
    Year = Year(S.date_time),
    Month = Month(S.date_time),
    QtyPerDay =
       Sum(s.quantity) * 1.0
       / DateDiff(day, M.MonthStart, DateAdd(month, 1, M.MonthStart))
FROM
    dbo.Sales S
    INNER JOIN dbo.Items I
       ON S.id_item = I.id_item
    CROSS APPLY (
       SELECT MonthStart = DateAdd(month, DateDiff(month, 0, S.date_time), 0)
    ) M
WHERE
    I.kind = 'Kind of Item'
    AND S.date_time >= @FromDate
    AND S.date_time < @ToDate
GROUP BY
    Year(S.date_time),
    Month(S.date_time),
    M.MonthStart

It will select any full month that is partially enclosed by the FromDate and ToDate. The * 1.0 part is required if the quantity column is an integer, otherwise you will get an integer result instead of a decimal one.

Some stylistic notes:

Do NOT use string date conversion on a column to ensure you get whole days. This will completely prevent any index from being used, require more CPU, and furthermore is unclear (what does style 112 do again!?!?). To enclose full date periods, use what I showed in my query of DateCol >= StartDate and DateCol < OneMoreThanEndDate. Do a search for "sargable" to understand a very key concept here. A very safe and valuable general rule is to never put a column inside an expression if the condition can be rewritten to avoid it.
It is good that you're aliasing your tables, but you should use those aliases throughout the query for each column, as I did in my query. I recognize that the aliases V and A came from another language so they make sense there--just in general try to use aliases that match the table names.
Do include the schema name on your objects. Not doing so is not a huge no-no, but there are definite benefits and it is best practice.
When you ask a question it is helpful to explain all the logic so people don't have to guess or ask you--if you know (for example) that users can input mid-month dates but you need whole months then please indicate that in your question and state what needs to be done.
Giving the version of SQL server helps us zero in on the syntax required, as prior versions are less expressive. By telling us the version we can give you the best query possible.

Note: there is nothing wrong with putting the date calculation math in the query itself (instead of using SET to do it). But I figured you would be encoding this in a stored procedure and if so, using SET is just fine.

I just had to add MonthStart in the Group BY and worked perfectly, thank you!!!!, and thanks for the notes, will take them into consideration for next time. Have a nice day!! — Edgar Holguin, Dec 20 '12 at 20:31

Sum of values per month divided by days of month

1 Answers1