Sales and transaction totals by part, grouped by last 24 months

Question

SELECT det.partNum,
SUM(det.quantity) AS Demand1,
COUNT(det.partNum) AS Call1

FROM details det

JOIN invoice inv ON det.invoice_id = inv.id
WHERE inv.invoice_date
BETWEEN '2015-11-01 00:00:00'
AND '2015-11-31 23:59:59'

GROUP BY partNum

The above sql returns all part numbers, the total number sold (Demand), and the total number of transactions the parts were involved in (Call) for the current month.

What our vendor wants is this information for every part, but also grouped for each of the past 24 months. The csv they are requesting would look like the following (if only viewing the last 3 months):

Part# | Demand1 | Demand2 | Demand3 | Call1 | Call2 | Call3
123   |       0 |       2 |       0 |     0 |     1 |     0
345   |       6 |       3 |       4 |     1 |     2 |     3

Part# 123: 0 transactions this month (Call1) 0 quantity sold (Demand1)
       1 transaction last month (Call2) 2 quantity sold (Demand2).
       0 transactions two months ago (Call3) 0 quantity sold (Demand3).

Part# 345: 1 transaction this month (Call1) for qty sold of 6 (Demand1)
       2 transactions last month (Call2) for qty sold of 3 (Demand2)
       3 transactions two months ago (Call3) for qty sold of 4 (Demand3)

Realize that they want this extended out for the past 24 months. Demand1/Call1 are always the current month.

I used the WHERE/BETWEEN statement to show where the date is coming from and to demonstrate how I can get an accurate report of the parts for the current month.

What I can't figure out how to do is to fill Demand and Call for 24 months. And this is the format that the vendor expects the data to be in. This wasn't my choice. Any help in getting this working as expected would be greatly appreciated.

Thanks

EDIT

I removed the sql-server tag. Sorry about that. This is only MySQL.

Also, I'm adding my reply from below...

Looking into DATEDIFF, TIMESTAMPDIFF, and even PERIOD_DIFF. But none actually seem to return what I need. What needs to happen is the first demand column should search for the current month, day 1 (inclusive) through the next month, day 1 (exclusive). The next demand column should search the current month - one month, day 1 (inclusive) through next month - one month, day 1 (exclusive). And each subsequent column should search the same parameters, subtracting an additional month each column. I don't think that can be accomplished with precision simply using DATEDIFF.

I hope that makes sense.

And again, thanks for any help.

I would suggest grouping by partnum, year and month, and then pivot to get the data in the shape the vendor is requesting. — Mat Richardson, Nov 18 '15 at 21:14
I need all information gathered by part number. There will be 48 columns for every part number. I don't see how I can accomplish that by grouping by year+month. — user3221479, Nov 18 '15 at 21:50
Mat, I'll search for pivots, but while I'm doing that, could you provide some type of example? — user3221479, Nov 18 '15 at 21:52

score 0 · Answer 1 · answered Nov 18 '15 at 21:16

If I understood your problem correctly, you can do it like this:

SELECT 
  det.partNum,
  SUM(case when inv.invoice_date >= dateadd(month, -3, @currMonth) and inv.invoice_date < dateadd(month, -2, @currMonth) then det.quantity else 0) AS Demand1,
  SUM(case when inv.invoice_date >= dateadd(month, -2, @currMonth) and inv.invoice_date < dateadd(month, -1, @currMonth) then det.quantity else 0) AS Demand2,
...
FROM details det

JOIN invoice inv ON det.invoice_id = inv.id
WHERE 
  inv.invoice_date >= '2015-11-01 00:00:00' AND inv.invoice_date < '2015-12-01'

GROUP BY partNum

This uses a variable that has the start date of current month to make the SQL more simple. I also changed the where clause, you should really use >= + < with dates instead of between.

Thanks for the quick reply. Is the WHERE clause necessary since we're not filtering out the records for the current month? I only put that in the original example to show an acceptable range for the current month. — user3221479, Nov 18 '15 at 21:42
I'm getting a syntax error at line 3. Additionally, This gives no columns for the Calls. Not only do I need to sum the sales, but also count the number of times a sale is made. — user3221479, Nov 18 '15 at 21:48

JamieD77 · Answer 2 · 2015-11-18T22:27:42.270

This might get you started with Pivot query.

;WITH cte AS 
(
    SELECT  det.partNum,
            SUM(det.quantity) AS DemandSum,
            COUNT(det.partNum) AS CallCount,
            DATEDIFF(MONTH,inv.invoice_date, GETDATE()) + 1 MonthDiff
    FROM    details det
            JOIN invoice inv ON det.invoice_id = inv.id
    GROUP BY det.partNum, DATEDIFF(MONTH,inv.invoice_date, GETDATE()) + 1

)
SELECT t.partNum,
       [Demand1],[Demand2],[Demand3],[Demand4],[Demand5],[Demand6],[Demand7],[Demand8],[Demand9],[Demand10],[Demand11],[Demand12],
       [Demand13],[Demand14],[Demand15],[Demand16],[Demand17],[Demand18],[Demand19],[Demand20],[Demand21],[Demand22],[Demand23],[Demand24],
       [Call1],[Call2],[Call3],[Call4],[Call5],[Call6],[Call7],[Call8],[Call9],[Call10],[Call11],[Call12],
       [Call13],[Call14],[Call15],[Call16],[Call17],[Call18],[Call19],[Call20],[Call21],[Call22],[Call23],[Call24]
FROM (SELECT DISTINCT partNum FROM cte) t
LEFT JOIN (
        SELECT * FROM (
            SELECT partNum, DemandSum, CONCAT('Demand',MonthDiff) ColName FROM cte
        ) c PIVOT (SUM(DemandSum) FOR ColName IN ([Demand1],[Demand2],[Demand3],[Demand4],[Demand5],[Demand6],[Demand7],[Demand8],[Demand9],[Demand10],[Demand11],[Demand12],
                                                [Demand13],[Demand14],[Demand15],[Demand16],[Demand17],[Demand18],[Demand19],[Demand20],[Demand21],[Demand22],[Demand23],[Demand24])    
        ) p
    ) ds ON ds.partNum = t.partNum
LEFT JOIN (
        SELECT * FROM (
            SELECT partNum, CallCount, CONCAT('Call',MonthDiff) ColName FROM cte
        ) c PIVOT (COUNT(CallCount) FOR ColName IN ([Call1],[Call2],[Call3],[Call4],[Call5],[Call6],[Call7],[Call8],[Call9],[Call10],[Call11],[Call12],
                                                [Call13],[Call14],[Call15],[Call16],[Call17],[Call18],[Call19],[Call20],[Call21],[Call22],[Call23],[Call24])    
        ) p
    ) cc ON cc.partNum = t.partNum

if that's too confusing, you can use the CASE method. I'd do it a little different than the other answer though..

SELECT 
    det.partNum,
    SUM(case WHEN DATEDIFF(MONTH, inv.invoice_date, GETDATE()) = 0 then det.quantity else 0 end) AS Demand1,
    SUM(case WHEN DATEDIFF(MONTH, inv.invoice_date, GETDATE()) = 1 then det.quantity else 0 end) AS Demand2,
    SUM(case WHEN DATEDIFF(MONTH, inv.invoice_date, GETDATE()) = 2 then det.quantity else 0 end) AS Demand3,
    COUNT(case WHEN DATEDIFF(MONTH, inv.invoice_date, GETDATE()) = 0 then det.partNum end) AS Call1,
    COUNT(case WHEN DATEDIFF(MONTH, inv.invoice_date, GETDATE()) = 1 then det.partNum end) AS Call2,
    COUNT(case WHEN DATEDIFF(MONTH, inv.invoice_date, GETDATE()) = 2 then det.partNum end) AS Call3
FROM 
    details det
    JOIN invoice inv ON det.invoice_id = inv.id
GROUP BY 
    det.partNum

you can get the full script for all 24 months here.. SQL Fiddle

Thanks for the help but I get #1582 - Incorrect parameter count in the call to native function 'DATEDIFF' when I run the statement. — user3221479, Nov 19 '15 at 16:32
you had mysql and sql-server tagged.. i assume by the error you're only using mysql — JamieD77, Nov 19 '15 at 17:04
I was able to run the statement without the MONTH parameter and changing GETDATE to CURDATE. But it's not returning monthly totals in the Demand/Call columns. — user3221479, Nov 19 '15 at 17:04
Looking into DATEDIFF, TIMESTAMPDIFF, and even PERIOD_DIFF. But none actually seem to return what I need. What needs to happen is the first demand column should search for the current month, day 1 (inclusive) through the next month, day 1 (exclusive). The next demand column should search the current month - one month, day 1 (inclusive) through next month - one month, day 1 (exclusive). And each subsequent column should search the same parameters, subtracting an additional month each column. I don't think that can be accomplished with precision simply using DATEDIFF. — user3221479, Nov 19 '15 at 17:48
you're probably going to need to convert the invoice date to yymm and then use period_diff or something. i dont have a working copy of mysql and sql fiddle is acting up.. this has a bunch of ways to figure out the month difference.. http://stackoverflow.com/questions/288984/the-difference-in-months-between-dates-in-mysql — JamieD77, Nov 19 '15 at 19:19
Thanks Jamie. I had actually found that before and started the search into DATEDIFF, TIMESTAMPDIFF, and PERIOD_DIFF. The following is what I've come up with, but I'm having trouble with syntax. — user3221479, Nov 19 '15 at 19:23
I need the SUM of quantity for the date range between CONCAT(DATE_FORMAT(inv.invoice_date, '%Y-%m-'),'01') and DATE_SUB(DATE_ADD(inv.invoice_date, INTERVAL 1 MONTH), INTERVAL 1 DAY) — user3221479, Nov 19 '15 at 19:29

Sales and transaction totals by part, grouped by last 24 months

2 Answers2