CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Edgar Holguin
Edgar Holguin

Reputation: 187

Sum of values per month divided by days of month

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())))

Upvotes: 3

Views: 4589

Answers (1)

ErikE
ErikE

Reputation: 50231

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.

Upvotes: 4

Related Questions