1

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
1

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
  • Yes that is only returning 4 rows like I am after. Will need to examine the pivot operator as you suggest to only have 1 row returned. – IcyPopTarts Jul 28 '17 at 18:34
  • updated my answer to show you how unpretty it is getting to that one row – codedawg82 Jul 28 '17 at 18:42
  • this works if dates are all in one year, but if they span between multiple years it no longer works since it only pulls the 1st two characters of the year. – IcyPopTarts Jul 28 '17 at 18:54
  • typo -- we should be using the right instead of left function and changing the names in the pivot. I've updated accordingly. – codedawg82 Jul 28 '17 at 18:57
  • I think something is off with the calculations. For example, when I run your query above, I get a result set of 200, 297.33, and 397.75 - I would expect to have 1, -3, -1.25 – IcyPopTarts Jul 28 '17 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.