1

I am trying to get Month wise trend of sales data from following table.

Table VendorTrading:

Id   TradeDate
1    2015-11-25
2    2015-12-10

2nd table CustomerProduct:

VendorTradeId ProductName ProductQuantity
1             ABC         5
1             XYZ         0
1             QWE         3
2             ABC         2
2             XYZ         4
2             QWE         6

What I am trying to achieve is:

Product Jan-15 Feb-15 Mar-15 April-15 May-15 June-15 . . . 
ABC     ###    ####   ###    ####     ####    ###
XYZ     ###    ####   ###    ####     ####    ###
QWE     ###    ####   ###    ####     ####    ###

Where parameter I want to pass to stored procedure is just year (2015)

This is what I have tried so far but doesn't retrieve expected results

select 
    CP.ProductName, 
    SUM( Cp.ProductQuantity ) as Quantity, 
    VT.Tradedate
from 
    VendorTrading VT 
inner join 
    CustomerProducts CP on VT.Id = Cp.VendorTradingId
where
    (VT.Tradedate between isnull(@StartDate, VT.Tradedate) 
                      and isnull(@EndDate, VT.Tradedate))
group by
    Cp.ProductName, VT.Tradedate

This is result of my query:

enter image description here

Affan Shahab
  • 838
  • 3
  • 17
  • 39
  • What you are looking for is called Pivoting table. There are tons of answers here on SO and I'm pretty sure that I already saw something pretty equal to you case here. Take a look. – Jorge Campos Feb 28 '16 at 21:33
  • Thanks @JorgeCampos, It will be great if you can refer a link or two similar to my case. Thanks – Affan Shahab Feb 28 '16 at 21:33
  • One: http://stackoverflow.com/questions/15274305/is-it-possible-to-have-multiple-pivots-using-the-same-pivot-column-using-sql-ser ; Two: http://stackoverflow.com/a/6410474/460557 Three: http://stackoverflow.com/a/17073196/460557 Four: http://stackoverflow.com/a/30776186/460557 There are many more. Just do a search. – Jorge Campos Feb 28 '16 at 21:39
  • Affan, can you mark this as answered and/or a duplicate if you are happy with Jorge's solution. – cameront Feb 28 '16 at 22:26
  • @JorgeCampos as you suggested, I am trying to achieve it using this link [link](http://stackoverflow.com/questions/6410134/how-to-select-sql-results-grouped-by-weeks/6410474#6410474) . Here is my query Select CP.ProductName,MonthNumber,sum(Cp.ProductQuantity ) as QTY from( SELECT CP.ProductName, DATEDIFF(month, '2015-01-01','2015-12-31') AS MonthNumber, Cp.ProductQuantity FROM VendorTrading VT inner join CustomerProducts CP on VT.Id = Cp.VendorTradingId WHERE (VT.Tradedate between isnull(NULL,VT.Tradedate) and isnull(NULL,VT.Tradedate)) ) GROUP BY CP.ProductName, MonthNumber – Affan Shahab Feb 29 '16 at 19:02
  • But it is giving error on GROUP BY clause Incorrect syntax near the keyword 'GROUP'. – Affan Shahab Feb 29 '16 at 19:03
  • 1
    At this query your problem is that you are using a field from a table of a subquery. You can't do that. you have to name your subquery something as `from (select CP.ProductName ......) as someName ` then group by with this new name (also in select) like `group by someName.ProductName .....` – Jorge Campos Feb 29 '16 at 19:07
  • Ok the error is gone, And I have modified as you suggested. Here is how it look like now: Select CP.ProductName,MonthNumber,sum(Cp.ProductQuantity ) as QTY from( SELECT CP.ProductName, DATEDIFF(month, '2015-01-01','2015-12-31') AS MonthNumber, Cp.ProductQuantity FROM VendorTrading VT inner join CustomerProducts CP on VT.Id = Cp.VendorTradingId WHERE (VT.Tradedate between isnull(NULL,VT.Tradedate) and isnull(NULL,VT.Tradedate)) ) as SomeName GROUP BY SomeName.ProductName, SomeName.MonthNumber, SomeName.ProductQuantity But message shows on execution> Next Comment – Affan Shahab Feb 29 '16 at 19:13
  • The multi-part identifier "CP.ProductName" could not be bound. The multi-part identifier "Cp.ProductQuantity" could not be bound. – Affan Shahab Feb 29 '16 at 19:14
  • 1
    You didn't pay attention to the comment. **(also in select)** change your query to `select someName.ProductName, someName.MonthNumber, sum(someName.ProductQuantity) from (...it is fine here..) group by SomeName.ProductName, SomeName.MonthNumber` also note that `someName` is an alias and can be whatever you want. We usually use just a letter. Also you don't group by ProductQuantity because it is the field being summed. – Jorge Campos Feb 29 '16 at 19:25

0 Answers0