Friday, January 22, 2016

Set Based Solutions

In the Structured Query Language (SQL) database world in which I live, the first thing to learn is thinking of information as existing in sets. We learned about sets in third grade, never thinking that we would use that knowledge for anything useful.

Someone needed to have a SQL statement that would always return only activity from the previous day, without having to tell it what day it is now, and have it work independent of human interaction. OK, we can do that.

So, the set we want is records that have a date that is yesterday. We also want to fabricate counts of stuff that happened yesterday in four (or n) domains. This works:

SELECT     TOP (100) PERCENT Client_Name, Abstractor, Abstractor_Name, EntryDate, COUNT(CASE WHEN Payment = 1 THEN 1 ELSE NULL END) AS [Payment Type 1],
                      COUNT(CASE WHEN Payment = 2 THEN 1 ELSE NULL END) AS [Payment Type 2], COUNT(CASE WHEN Payment = 3 THEN 1 ELSE NULL END) AS [Payment Type 3],
                      COUNT(CASE WHEN Payment = 4 THEN 1 ELSE NULL END) AS [Payment Type 4], DATEADD(dd, - 1, GETDATE()) AS EntryDate1
FROM         dbo.vwClientsPaymentsAbstractors
WHERE     (EntryDate >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0)) AND (EntryDate < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
GROUP BY Client_Name, Abstractor, Abstractor_Name, EntryDate
ORDER BY Client_Name, EntryDate

We count the payment types using "COUNT(CASE WHEN...THEN...ELSE...END" statements to coerce the different types into their sums.

Then this date manipulation:

WHERE     (EntryDate >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0)) AND (EntryDate < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))

gets us only yesterday's records, without knowing what day today is. It works every day.

I love working with SQL.

No comments:

Post a Comment