Can this be done using CTE | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Can this be done using CTE

Hi All, (sorry for the long explanation, but I want to make it clear to you) Maybe I’m trying to accomplish something that cannot be done but I thought why not share my thoughts on this one with you guys. In my table Invoice I have the following two rows:
——————————————
DateInvoice AmountInvoice OrderPlaced
01-01-2007 300000 yes
01-03-2007 100000 No
—————————————— What I would like to accomplish is the following overview: ————————————————————————-
Year AmountInvoice NumberOfInvoices AmountInvoice NumberOfInvoices
2007 400000 2 100000 1
————————————————————————- In this row I want to see a split of total orderamount and the second amount is the amount where the orderPlaced is Yes. My thoughts were to use a CTE for this one.I came up with this: —
WITH OrderPlacedYesCTE (DateInvoice, AmountInvoice, OrderCount)
AS
(
SELECT DATEPART(YY, DateInvoice), SUM(AmountInvoice), COUNT(*)
FROM Invoice
WHERE OrderPlaced=1
GROUP BY DATEPART(YY, DateInvoice)
)
—
This shows the records grouped and summed perfectly For the second part I used: —
SELECT DATEPART(YY,in.DateInvoice), SUM(in.AmountInvoice), COUNT(*) AS ‘NumberOfInvoices’, op.AmountInvoice, op.Ordercount
FROM Invoice AS In
INNER JOIN OrderPlacedYesCTE AS op ON DATEPART(yy, in.DateInvoice)=DATEPART(yy, op.DateInvoice)
GROUP BY DATEPART(yy,in.DateInvoice), in.AmountInvoice, op.AmountInvoice, op.Ordercount
— This returns back no rows. Is what I want possible this way? DateInvoice=smalldatetime, AmountInvoice=money, OrderPlaced=bit Maurice
Why would you need a CTE? You already have the GROUP BY pretty much worked out, other than that use something like: SUM(OrderAmount), SUM(CASE WHEN OrderPlaced = ‘Yes’ THEN OrderAmount ELSE 0 END)
Adriaan, Thanks for the reply. I thought I had two pieces of information coming from the same table. That’s why I thought I had to combine the two. I tried your suggestion and it worked like a charme. Another lesson learned. Well spent some time on the CTE technique don’t consider it a waste of time but we must move on. Thanks again. Maurice
]]>