asked on

SQL Question Two Left Joins, Aggregates

I have a transaction table with CustomerId, ItemId, DollarAmt, QTY, and TransDate

I need to join to Customer table, Item table. Here's my problem:

I need to get This Year and Last Year data in the same record.

SELECT CustomerId, ItemId, SUM(ThisYear.DollarAmt), SUM(ThisYear.QTY), SUM(LastYear.DollarAmt), SUM(LastYear.DollarAmt)
FROM Customer c
LEFT JOIN (
SELECT DollarAmt,QTY,CustomerId
FROM Sales s
JOIN Customer c ON c.CustomerId = s.SalesId
WHERE Date = {date logic}
) AS ThisYear ON ThisYear.CustomerId = c.CustomerId
LEFT JOIN (
SELECT DollarAmt,QTY,CustomerId
FROM Sales s
JOIN Customer c ON c.CustomerId = s.SalesId
WHERE Date = {date logic}
) AS LastYear ON LastYear .CustomerId = c.CustomerId
GROUP BY CustomerId, ItemId

Open in new window

This should group all the sales data by Customer, however, I don't believe it's correctly placing the same items this year and last year item.

DrewKjell

SELECT CustomerId, ItemId, SUM(ThisYear.DollarAmt), SUM(ThisYear.QTY), SUM(LastYear.DollarAmt), SUM(LastYear.DollarAmt)
FROM Customer c
LEFT OUTER JOIN Sales ThisYear ON (c.CustomerId = ThisYear.SalesId AND Date = {date logic})
LEFT OUTER JOIN Sales LastYear ON (c.CustomerId = LastYear.SalesId AND Date = {date logic})
GROUP BY c.CustomerId, c.ItemId

Open in new window

HainKurt

what about this, all in one query...

if this is ok, we can join this with customer table to get customer info...

select CustomerId, ItemId, 
			 SUM(case year(TransDate) when year(getDate())-1 then DollarAmt else 0 end) TotalDollarAmtLastYear,
			 SUM(case year(TransDate) when year(getDate()) then DollarAmt else 0 end) TotalDollarAmtThisYear,
			 SUM(case year(TransDate) when year(getDate())-1 then Qty else 0 end) TotalQtyLastYear,
			 SUM(case year(TransDate) when year(getDate()) then Qty else 0 end) TotalQtyThisYear
  from Sales
 where year(TransDate)>=year(getdate())-1
 group by CustomerId, ItemId

Open in new window

Lowfatspread

like this

?

;with cte as 
  (select customerid,case year(trandate)
                          when year(getdate()) then 1 
                          else 2
                          end as yr
         ,qty,dollaramt,itemid
     from sales 
    where trandate >=convert(char(4),year(getdate())-1)+'0101'
 )
select c.customerid
    ,s.itemid,s.thisdollar,s.thisqty,s.lastdollar,s.lastqty
from customer as c
left outer join 
   (select a.customerid,a.itemid,a.[1] as ThisDollar
                    ,b.[1] as ThisQty
                    ,a.[2] as LastDollar,b.[2] as LastQty
     from (Select customerid,itemid
                  ,[1],[2]
             from CTE 
             pivot (sum(qty) for yr in ([1],[2])) as pvt
          ) as B
     inner join 
          (Select customerid,itemid
                  ,[1],[2]
             from CTE 
             pivot (sum(dollaramt) for yr in ([1],[2])) as pvt
          ) as A
   ) as S
on c.customerid=s.customerid
order by 1

Open in new window

ASKER CERTIFIED SOLUTION

coldchillin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

coldchillin

ASKER

The other solutions provided may have also worked, but I went a different way and seemed to address the issue at hand.