Avatar of coldchillin
coldchillin
 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.
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
coldchillin

8/22/2022 - Mon
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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
coldchillin

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
coldchillin

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