August 18, 2014 at 4:13 am
I have a financial department requirement, where they require various data grouped by Year/Month.
I have done this by using PIVOT - but the goal posts have now changed and they only want to see the previous 3 months along with lasts years data as a comparison.
I have the following script ;
========================
SELECT
TrnYear,
[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12]
FROM
(
SELECT
ROW_NUMBER() OVER ( ORDER BY TrnYear DESC, right('00'+ rtrim(TrnMonth), 2) DESC ) as Row,
TrnYear,
(right('00'+ rtrim(TrnMonth), 2) ) as TrnMonth,
Count( Invoice ) as InvoiceCount
FROM ApInvoicePay
Where
TrnYear >= Year( GetDate()) -2
Group by TrnYear, TrnMonth
) p
PIVOT
(
Sum(InvoiceCount)
FOR TrnMonth IN
( [01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])
) AS Pvt
where Row in ( 1,2,3,12,13,14,15 )
================================
from the above I get the the following output ;
================================
TrnYear010203040506070809101112
2013NULLNULLNULLNULLNULLNULLNULLNULL652NULLNULLNULL
2013NULLNULLNULLNULLNULLNULLNULL556NULLNULLNULLNULL
2013NULLNULLNULLNULLNULLNULL679NULLNULLNULLNULLNULL
2013NULLNULLNULLNULLNULL485NULLNULLNULLNULLNULLNULL
2014NULLNULLNULLNULLNULLNULLNULL331NULLNULLNULLNULL
2014NULLNULLNULLNULLNULLNULL488NULLNULLNULLNULLNULL
2014NULLNULLNULLNULLNULL560NULLNULLNULLNULLNULLNULL
================================
what I would like to see is the TrnYear only ONCE ..... am I missing one simple piece of grouping code ?
Thanks
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
August 19, 2014 at 5:57 pm
Hi,
Since I don't have sample data to work with I took the AdventurWorks database as my foundation. I am not totally clear how your data looks like but below is what I was able to devise.. maybe it can help you with your code .
select * from
(
select YEAR(ORderDATE) as Year_, MONTH(OrderDate) AS Month_pivot,SalesOrderID
FROM Sales.SalesOrderHeader
) as t
pivot
(
Count(salesOrderID) for month_pivot in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])
) as p
----------------------------------------------------
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply