Author |
Topic |
mjimenezh
Yak Posting Veteran
81 Posts |
Posted - 2014-03-26 : 16:36:17
|
Hi, I have a sql 2008/r2 view that shows me always (vertically) the last 12 months of sales including the actual month,I need to sum the sold quantity of every item taking care of the customer,the year and the month and showing all this on columns mode(201403),(201402)...,by exemple : must shows per customer/item/and date :debcode|debtor |year |month|itemcode|description |sum(sold) per month| 100 debtor1 2014 03 item1 item 1 demo 25 100 debtor1 2014 03 item2 item 2 demo 10 100 debotr1 2014 02 item1 item 3 demo 5 100 debotr1 2013 02 item2 item 1 demo 15Here is my code, i hope you can help me, pleaseSELECT TOP (100) PERCENT Year, Month, debcode, Debtor, Currency, SalesRep, ItemCode, ItemDescription, SUM(Quantity) AS Sold, InvoiceDateFROM dbo.View_VK_ORDERS_SUMARYWHERE (Sale_Price <> 0.01) AND (InvoiceDate >= DATEADD(MM, - 12, GETDATE()))GROUP BY ItemCode, ItemDescription, Month, Year, debcode, Debtor, Currency, SalesRep, InvoiceDateORDER BY Year, Month, debcode, ItemCode |
|
mjimenezh
Yak Posting Veteran
81 Posts |
Posted - 2014-03-27 : 16:00:50
|
Sorry, this is my code :SELECT [DebCode],[Debtor],ItemCode, ItemDescription,[Year],[1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12]FROM (SELECT DebCode,Debtor,ItemCode,ItemDescription,Year,Month,Sold FROM View_VK_ORDERS_SUMARY_F) AS PivotDataPIVOT (SUM(Sold) FOR Month IN ([1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12])) AS PIVOTING |
|
|
mjimenezh
Yak Posting Veteran
81 Posts |
Posted - 2014-03-27 : 20:34:41
|
Someboody to help me? I need columns with dynamic headers [201403],[201402],[201401],[201312]...my code gives me one fully year from the current month so the next month all the header must be change.My actual data looks like this :debcode|debtor|itemcode|year|01|02|03|04|05|.......|12| 100 deb100 item1 2013 1 0 3 5 2 0 100 deb100 item1 2014 0 5 5 101 deb101 item1 2013 3 3 9 0 0 2 101 deb101 item2 2014 2 2 6 must looks like this :debcode|debtor|itemcode|2013-01|2013-02|.......|2014-03| 100 deb100 item1 101 deb101 item2 Thanks for your help. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
mjimenezh
Yak Posting Veteran
81 Posts |
Posted - 2014-03-28 : 10:42:12
|
Thanks madhivanan but it's too advanced for me, I'm trying to learn sql and specially I'm trying to understand Pivot, this is turning me crazy.Thanks again body.. |
|
|
|
|
|