Sql-server – Breakout Query Results By Year And Month

datesql serversql-server-2008-r2t-sql

I have two tables that house data and I need to perform calculations on both sets of data, so I thought a Union All query would be best. Well that gets me the result set that I am after, but now i need to take this a step further and provide a breakout by year/month for the data. So the equation I am after is (vramt-saleamt)/vramt and this is my DDL which provides the data and a date, but how would I split the data out to be a breakdown by month/year

Desired Output

Jan 16 Feb 16 Mar 16
XXXX   XXX    XXX

And this is sample DDL

    Declare @Store1 Table (cardname varchar(200), saleamt float, saledate date)
Declare @TF Table (cardname varchar(200), vramt float ,saledate date)

Insert Into @TF (cardname, vramt, saledate) Values
('Red', 200.00, '20160101'), ('Pink', 300.00, '20160201'), ('Orange', 400.00, '20160301')

Insert Into @Store1 (cardname, saleamt, saledate) Values
('Red', 400.00, '20160201'), ('Pink', 800.00, '20160204'), ('Orange', 900.00, '20160301')

SELECT
T1.cardname,
SUM(ISNULL(T1.vramt,0)) AS vr,
0 As sa,
saledate As saledate
FROM @TF AS T1
GROUP BY T1.cardname, T1.saledate
UNION ALL
Select
t2.cardname,
0 As vr,
SUM(ISNULL(t2.saleamt,0)) As sa,
saledate as saledate
FROM @Store1 t2
Group By t2.cardname, t2.saledate

Best Answer

This is very klugey and I've quite a few gymnastics here give your initial code, but the final data set still needs to be ported using a PIVOT if you want ONLY one row. I would suggest you look at the PIVOT operator to understand to use this. 

    Declare @Store1 Table (cardname varchar(200), saleamt float, saledate date)
Declare @TF Table (cardname varchar(200), vramt float ,saledate date)

Insert Into @TF (cardname, vramt, saledate) Values
('Red', 200.00, '20160101'), ('Pink', 300.00, '20160201'), ('Orange', 400.00, '20160301')

Insert Into @Store1 (cardname, saleamt, saledate) Values
('Red', 400.00, '20160201'), ('Pink', 800.00, '20160204'), ('Orange', 900.00, '20160301')


;with datavr as (
SELECT
T1.cardname,
SUM(ISNULL(T1.vramt,0)) AS vr,
0 As sa,
--saledate As saledate,
rtrim(cast(saledate as varchar(7))) as monyr
FROM @TF AS T1
GROUP BY T1.cardname, T1.saledate
),
datasa as (
Select
t2.cardname,
0 As vr,
SUM(ISNULL(t2.saleamt,0)) As sa,
--saledate as saledate,
rtrim(cast(saledate as varchar(7))) as monyr
FROM @Store1 t2
Group By t2.cardname, t2.saledate
)

select 
    isnull(v.cardname, s.cardname) as cardname,
    isnull(v.monyr, s.monyr) as monyr,
    sum(v.vr) as vrsum,
    sum(s.sa) as sasum,
    (sum(isnull(v.vr,0)) - sum(isnull(s.sa,0)) / sum(v.vr)) as margin
from datavr v
full outer join datasa s
    on v.cardname = s.cardname
    and v.monyr = s.monyr
group by 
    isnull(v.cardname, s.cardname),
    isnull(v.monyr, s.monyr)

More kluge, but if you want to use the unpretty PIVOT to really get that one row:

;with datavr as (
SELECT
T1.cardname,
SUM(ISNULL(T1.vramt,0)) AS vr,
0 As sa,
--saledate As saledate,
cast(datename(month,saledate)as varchar(3)) + '-' + right(year(saledate),2) as monyr
FROM @TF AS T1
GROUP BY T1.cardname, T1.saledate
),
datasa as (
Select
t2.cardname,
0 As vr,
SUM(ISNULL(t2.saleamt,0)) As sa,
--saledate as saledate,
cast(datename(month,saledate)as varchar(3)) + '-' + right(year(saledate),2) as monyr
FROM @Store1 t2
Group By t2.cardname, t2.saledate
)
, penult as (
select 
    --isnull(v.cardname, s.cardname) as cardname,
    isnull(v.monyr, s.monyr) as monyr,
    --sum(v.vr) as vrsum,
    --sum(s.sa) as sasum,
(sum(isnull(v.vr,0)) - sum(isnull(s.sa,0))) / sum(isnull(v.vr,0)) as margin
from datavr v
full outer join datasa s
    on v.cardname = s.cardname
    and v.monyr = s.monyr
group by 
    isnull(v.cardname, s.cardname),
    isnull(v.monyr, s.monyr)
)
select p.*
from penult d
pivot (
    max(margin)
    for monyr in ([Jan-16], [Feb-16], [Mar-16])
) p
Related Question