SQL Server Performance Forum – Threads Archive

Query For 2 Seperate Month Results

I am building a report that is currently running correctly for cuurent month results with the following query. SELECT DISTINCT
Customer.Street1 AS ‘Prop#’,
Customer.Name AS ‘Prop_Name’,
COUNT(master.number) AS ‘#_Of_Accts’,
SUM(master.original1) AS ‘$_Listed’,
SUM(payhistory.paid1) AS ‘$_Collected’
FROM Customer
LEFT OUTER JOIN payhistory ON payhistory.customer=Customer.customer
INNER JOIN master ON master.number=payhistory.number
WHERE (Customer.customer in (‘@allowedcustomers’))
AND (DATEDIFF(MM, master.received, GETDATE()) = 0)
GROUP BY Customer.Street1, Customer.Name What I need to do is duplicate columns ‘#_Of_Accts’, ‘$_Listed’, and ‘$_Collected’ for the previous month: (DATEDIFF(MM, master.received, GETDATE()) = 1) Any help would be greatly appreciated.
Mark Stopkey

---

You should be able to use a CASE expression like
< pseudocode >
SUM(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 0 THEN master.original1 ELSE 0 END) AS $_listed_this_month,
SUM(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 1 THEN master.original1 ELSE 0 END) AS $_listed_previous_month
</pseudocode>
You would of courde need to adjust your WHERE clause. Another way would be to use a derived table in which you retrieve the data for the previous month and JOIN this derived table to the one above. —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de

---

Thanks for the help! I am trying it out now.

---

The results of SUM master.original1 works great but COUNT master.number gets me the same results for both dates. Here is the query. SELECT DISTINCT
Customer.Street1 AS ‘Prop#’,
Customer.Name AS ‘Prop_Name’,
COUNT(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 5 THEN master.number ELSE 0 END) AS ‘#_Accts_This_Month’,
SUM(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 0 THEN master.original1 ELSE 0 END) AS ‘$_Listed_This_Month’,
COUNT(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 1 THEN master.number ELSE 0 END) AS ‘#_Accts_Prior_Month’,
SUM(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 1 THEN master.original1 ELSE 0 END) AS ‘$_Listed_Prior_Month’,
SUM(payhistory.paid1) AS ‘$_Collected’
FROM Customer
LEFT OUTER JOIN payhistory ON payhistory.customer=Customer.customer
INNER JOIN master ON master.number=payhistory.number
WHERE (Customer.customer in (‘@allowedcustomers’))
GROUP BY Customer.Street1, Customer.Name

---

OOPS. In the reply, I pasted in test numbers by mistake. Here is the actual query. SELECT DISTINCT
Customer.Street1 AS ‘Prop#’,
Customer.Name AS ‘Prop_Name’,
COUNT(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 0 THEN master.number ELSE 0 END) AS ‘#_Accts_This_Month’,
SUM(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 0 THEN master.original1 ELSE 0 END) AS ‘$_Listed_This_Month’,
COUNT(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 1 THEN master.number ELSE 0 END) AS ‘#_Accts_Prior_Month’,
SUM(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 1 THEN master.original1 ELSE 0 END) AS ‘$_Listed_Prior_Month’,
SUM(payhistory.paid1) AS ‘$_Collected’
FROM Customer
LEFT OUTER JOIN payhistory ON payhistory.customer=Customer.customer
INNER JOIN master ON master.number=payhistory.number
WHERE customer.customer IN (@allowedcustomers)
GROUP BY Customer.Street1, Customer.Name

---

]]>