What I need in a result set is first I need to group together the Customer table by CustomerDate and CustomerCode. Then I need to get the grouped CustomerID's and create two sum records summing the amount per currency across the CustomerID's for each grouping detail records. So in the above scenario my return results would be:
What is the best way to handle this with the grouping.
Microsoft SQL ServerSQL
Last Comment
sbornstein2
8/22/2022 - Mon
Scott Pletcher
SELECT C.CustomerDate, C.CustomerCode, CD.Currency_Type, SUM(CD.Amount) AS AmountSum
FROM CustomerDetail CD
INNER JOIN Customer C ON C.CustomerID = CD.CustomerID
GROUP BY C.CustomerDate, C.CustomerCode, CD.Currency_Type
ORDER BY C.CustomerDate, C.CustomerCode, CD.Currency_Type
sbornstein2
ASKER
Thanks Scott can I add one more wrinkle to the fold lets say I have the same scenario with one added field. Once I net the two currencies I need to take the positive currency back to the Customer table and use that Identifier on both records. So in the below case I have a positive CAD 275 I want to apply the 5678 as identifier to both records.
What I need in a result set is first I need to group together the Customer table by CustomerDate and CustomerCode. Then I need to get the grouped CustomerID's and create two sum records summing the amount per currency across the CustomerID's for each grouping detail records. So in the above scenario my return results would be:
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.
;With cte as
(
Select a.CustomerDate, a.CustomerCode, b.CurrencyType, Sum(b.Amount) AmountSum, --max(a.Identifier) Identifier,
max(a.Identifier) over (partition by a.CustomerDate, a.CustomerCode) Identifier,
row_number() over (partition by a.CustomerDate, a.CustomerCode order by a.CustomerID) idx
from @Customer a inner join @CustomerDetail b
on a.CustomerID = b.CustomerID
Group By a.CustomerDate, a.CustomerCode, b.CurrencyType, a.Identifier, a.CustomerID
)
Select a.CustomerDate, a.CustomerCode, a.CurrencyType, Sum(a.AmountSum) AmountSum, a.Identifier
from cte a
group by a.CustomerDate, a.CustomerCode, a.CurrencyType, a.Identifier, a.idx
order by a.CustomerDate, a.CustomerCode, a.idx
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
sbornstein2
ASKER
Oh wait sorry it is right I think
sbornstein2
ASKER
How is that example you have taking into account the positive Amount?
FROM CustomerDetail CD
INNER JOIN Customer C ON C.CustomerID = CD.CustomerID
GROUP BY C.CustomerDate, C.CustomerCode, CD.Currency_Type
ORDER BY C.CustomerDate, C.CustomerCode, CD.Currency_Type