CODEWITHSUNDEEP
sqljoinsubtraction
Eraniichan
Eraniichan

Reputation: 121

Getting the difference between two Select queries

So I'm stuck with this sql query,

select YEAR(docdate),
    sum(case when MONTH(DocDate) = 1 then DeliveredQty else 0 end) as January,
    sum(case when MONTH(DocDate) = 2 then DeliveredQty else 0 end) as February,
    sum(case when MONTH(DocDate) = 3 then DeliveredQty else 0 end) as March,
    sum(case when MONTH(DocDate) = 4 then DeliveredQty else 0 end) as April,
    sum(case when MONTH(DocDate) = 5 then DeliveredQty else 0 end) as May,
    sum(case when MONTH(DocDate) = 6 then DeliveredQty else 0 end) as June,
    sum(case when MONTH(DocDate) = 7 then DeliveredQty else 0 end) as July,
    sum(case when MONTH(DocDate) = 8 then DeliveredQty else 0 end) as August,
    sum(case when MONTH(DocDate) = 9 then DeliveredQty else 0 end) as September,
    sum(case when MONTH(DocDate) = 10 then DeliveredQty else 0 end) as October,
    sum(case when MONTH(DocDate) = 11 then DeliveredQty else 0 end) as November,
    sum(case when MONTH(DocDate) = 12 then DeliveredQty else 0 end) as December
    from trading.DeliveryReceipt A
    inner join trading.DeliveryReceiptDetail B
    on A.DocNumber = B.DocNumber
    group by year(docdate)

select YEAR(docdate),
    sum(case when MONTH(DocDate) = 1 then ReturnedQty else 0 end) as January,
    sum(case when MONTH(DocDate) = 2 then ReturnedQty else 0 end) as February,
    sum(case when MONTH(DocDate) = 3 then ReturnedQty else 0 end) as March,
    sum(case when MONTH(DocDate) = 4 then ReturnedQty else 0 end) as April,
    sum(case when MONTH(DocDate) = 5 then ReturnedQty else 0 end) as May,
    sum(case when MONTH(DocDate) = 6 then ReturnedQty else 0 end) as June,
    sum(case when MONTH(DocDate) = 7 then ReturnedQty else 0 end) as July,
    sum(case when MONTH(DocDate) = 8 then ReturnedQty else 0 end) as August,
    sum(case when MONTH(DocDate) = 9 then ReturnedQty else 0 end) as September,
    sum(case when MONTH(DocDate) = 10 then ReturnedQty else 0 end) as October,
    sum(case when MONTH(DocDate) = 11 then ReturnedQty else 0 end) as November,
    sum(case when MONTH(DocDate) = 12 then ReturnedQty else 0 end) as December
    from trading.SalesReturn C
    inner join trading.SalesReturnDetail D
    on C.DocNumber = D.DocNumber
    group by year(docdate)

It will show something like this...

https://i.sstatic.net/Rvdg7.png

What I want is to subtract the second table from the first table. I don't know how to do it. I've tried several things but still no luck.

Upvotes: 0

Views: 498

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Just use a join and subtract the values:

select d.yr,
       (d.January - r.January) as January,
       . . .
       (d.December - r.December) as December
from (select YEAR(docdate) as yr,
             sum(case when MONTH(DocDate) = 1 then DeliveredQty else 0 end) as January,
             sum(case when MONTH(DocDate) = 2 then DeliveredQty else 0 end) as February,
             sum(case when MONTH(DocDate) = 3 then DeliveredQty else 0 end) as March,
             sum(case when MONTH(DocDate) = 4 then DeliveredQty else 0 end) as April,
             sum(case when MONTH(DocDate) = 5 then DeliveredQty else 0 end) as May,
             sum(case when MONTH(DocDate) = 6 then DeliveredQty else 0 end) as June,
             sum(case when MONTH(DocDate) = 7 then DeliveredQty else 0 end) as July,
             sum(case when MONTH(DocDate) = 8 then DeliveredQty else 0 end) as August,
             sum(case when MONTH(DocDate) = 9 then DeliveredQty else 0 end) as September,
             sum(case when MONTH(DocDate) = 10 then DeliveredQty else 0 end) as October,
             sum(case when MONTH(DocDate) = 11 then DeliveredQty else 0 end) as November,
             sum(case when MONTH(DocDate) = 12 then DeliveredQty else 0 end) as December
      from trading.DeliveryReceipt A inner join
           trading.DeliveryReceiptDetail B
           on A.DocNumber = B.DocNumber
      group by year(docdate)
     ) d join
     (select YEAR(docdate) as yr,
             sum(case when MONTH(DocDate) = 1 then DeliveredQty else 0 end) as January,
             sum(case when MONTH(DocDate) = 2 then DeliveredQty else 0 end) as February,
             sum(case when MONTH(DocDate) = 3 then DeliveredQty else 0 end) as March,
             sum(case when MONTH(DocDate) = 4 then DeliveredQty else 0 end) as April,
             sum(case when MONTH(DocDate) = 5 then DeliveredQty else 0 end) as May,
             sum(case when MONTH(DocDate) = 6 then DeliveredQty else 0 end) as June,
             sum(case when MONTH(DocDate) = 7 then DeliveredQty else 0 end) as July,
             sum(case when MONTH(DocDate) = 8 then DeliveredQty else 0 end) as August,
             sum(case when MONTH(DocDate) = 9 then DeliveredQty else 0 end) as September,
             sum(case when MONTH(DocDate) = 10 then DeliveredQty else 0 end) as October,
             sum(case when MONTH(DocDate) = 11 then DeliveredQty else 0 end) as November,
             sum(case when MONTH(DocDate) = 12 then DeliveredQty else 0 end) as December
      from trading.SalesReturn C inner join
           trading.SalesReturnDetail D
           on C.DocNumber = D.DocNumber
      group by year(docdate)
     ) r
     on r.year = d.year;

Upvotes: 1

Related Questions