Friday, June 4, 2010

SQL Count by criteria.

This one had me scratching my head for a while. I had a tiered query grouped by a number of fields in the table structure. I wanted to get a count of all applicable items matching a set of given statuses.

For example: Toys
Type
Manufacturer # Sold # Returned

Cars
--Vrroom 14 0
--Bigga 16 2
Dolls
--Munchkin 22 4
--Slimer 7 1

etc.

Originally I had a query along the lines of:
SELECT i.InventoryType,
p.ProductName,
m.ManufacturerName,
CASE t.TransactionType
WHEN 1
THEN 'Sold'
WHEN 2
THEN 'Returned'
ELSE 'N/A'
END AS TransactionStatus,
COUNT(t.TransactionID) AS TransactionCount
FROM Inventory i
INNER JOIN Product p ON i.InventoryID = p.InventoryID
INNER JOIN Manufacturer m ON p.ManufacturerID = m.ManufacturerID
INNER JOIN Transaction t ON p.ProductID = t.ProductID
WHERE .... -- Conditions...

(Now this is only a rough approximation of the issue I was facing)
I then grouped within the report and used a conditional field on the transaction status to count applicable items. It sort of worked, but produced 2 lines for the items that had both sales and returns:

Cars
--Vrroom 14 0
--Bigga 16 0
-- 0 2
Dolls
--Munchkin 22 0
-- 0 4
--Slimer 7 0
-- 0 1

At first this looked like an issue within the SSRS report where it was grouping by Details. I removed that grouping which at first looked like it was working (1 row per product manufacturer) but that just "knocked off" one of the values. (I.e. for "Bigga" I'd end up with either 16/0 or 0/2, essentially depending on which status was found first.)

Round 2: I went back to the query determined to do the grouping properly in SQL.

...
m.ManufacturerName,
CASE t.TransactionType
WHEN 1
COUNT(t.TransactionID)
ELSE
0
END AS SoldCount,
CASE t.TransactionType
WHEN 2
COUNT(t.TransactionID)
ELSE
0
END AS ReturnedCount
...

This required t.TransactionType to be in the GroupBy clause which resulted in query results pretty much identical to what I was seeing in the report.

Round 3:
...
m.ManufacturerName,
SUM(CASE t.TransactionType
WHEN 1
THEN 1
ELSE 0
END) AS SoldCount,
SUM(CASE t.TransactionType
WHEN 2
THEN 1
ELSE 0
END) AS ReturnedCount
...

And Bingo!

Cars
--Vrroom 14 0
--Bigga 16 2

Dolls
--Munchkin 22 4
--Slimer 7 1

(Also, if a transaction had a quantity you wanted to count by then it'd be summing on t.Quantity rather than "1" (transaction count))

Thou shall not transmit localized DateTime values.

Date and time formats come in a variety of flavours, but one combination causes countless bugs, crashes, and potentially numerous lawsuits: dd/mm/yyyy vs. mm/dd/yyyy. If you ever come across "It worked yesterday, but it's crashing today" pretty much anywhere other than North America you've met this little gem.

The important thing to remember about DateTime values is that they are floating point numbers. 06/01/2010 is NOT a date, it is the localization of a date. The computer you give that localization to has to interpret what the actual date is. Is it January 6th, or June 1st? ANY, and EVERY time you need to transmit or transfer a DateTime from one source to another, you MUST take this into account. Generally this means every time the datetime transfers from one machine to another, such as to a Database server, a reporting server, or a web service. Today that may be two machines that have the same regional settings, but it just takes one server or client PC to be set to a different regional date format to totally hose your system. One thing you should NEVER see in code is a ".ToString()" (empty parameters) applied to a DateTime variable.

Now you don't need to go and start passing DateTime values around as floating point numbers to avoid this issue. (though that is one option.) There is one other practical option when transmitting DateTime values is to use ISO formatting. International Standard formatting for DateTime values is: yyyy-MM-dd HH:mm:ss. That is, 2010-01-06 19:04:30. In .Net this is commonly known as "Sortable". The main reason why this is advantageous is that regardless of their local date formats, any application worth its weight in salt *will* accept an ISO DateTime with its 24hr time format. ISO DateTimes also have the advantage that they are fully sortable so you can sort down to the month, day, hour, etc. or generate sequential numbers for things like filenames.

So there's no need to abandon the DateTime field type whether in .Net, SSRS, Crystal Reports, or SQL Server / Oracle or start farting around with regional settings; Just adopt ISO date and time formats when passing around dates.