USELESS BANTER PART AKA INTRODUCTION
If you are like me, very often the following happens to you: On a fine Friday evening like today, at about 4:45 PM, you are sitting in your cubicle farm, sipping your coffee, surfing the web, minding your own business and bothering no one. Then the pointy-haired guy or girl walks into your office and says something like this:
“I need the breakdown of sales by day from our Translyvania office. And, also get me how many orders there were each day”.
Then he/she/it walks back to the office with no regard to the fact that you were about to hit the road. To add insult to injury, without even turning back, he/she/it says:
“If you can’t get it to me by today 5:00 PM, Monday 8:00 AM would be fine”
THE SHALLOW PART AKA THE QUERY
Avid readers of my shallow blog would remember this particularly shallow article https://myshallowsqlblog.wordpress.com/beginning-of-anything/
The brilliant technique I invented there (I wish!!) allows you find the beginning of a day (or anything else for that matter). What we will do here then is to group by the beginning of day. Think about it. You have orders all through the day, and if we could have one common thing for all the orders on a given day, we could group by that common thing. The common thing we are going to use is the beginning of the day.
I know, that was a lot of talk and no action. So let me create some sample data so I can really show you in code
CREATE TABLE #Orders(OrderAmount DECIMAL(19,2), OrderDateStamp DATETIME); GO INSERT INTO #Orders (OrderAmount, OrderDatestamp) SELECT 100.00,'20130725 13:00:00' UNION ALL SELECT 50.00,'20130725 13:15:00' UNION ALL SELECT 150.00,'20130725 14:25:00' UNION ALL SELECT 100.00,'20130725 14:30:00' UNION ALL SELECT 150.00,'20130726 9:00:00' UNION ALL SELECT 25.00,'20130726 9:10:00' UNION ALL SELECT 15.00,'20130726 9:14:00'; GO
Now, we simply group by the beginning of the day. If you recall from that brilliant blog, to find the beginning of the day, you do something like this:
So our query would then be
-- Orders by Day SELECT DATEADD(dd,DATEDIFF(dd,0,OrderDatestamp),0) AS OrderDate, SUM(OrderAmount) AS OrderAmount, COUNT(*) AS OrderCount FROM #Orders GROUP BY DATEADD(dd,DATEDIFF(dd,0,OrderDatestamp),0);
That is to say, simply group by the beginning of the day, and include that in the select list also. The result for the sample data would be this:
OrderDate OrderAmount OrderCount 2013-07-25 00:00:00.000 400.00 4 2013-07-26 00:00:00.000 175.00 2
That is all there is to it!!
Now that you know how to group by the day, can you figure out what you would do if the point haired thing wanted you to get the data by the hour? Of course you do!! You would simply use EXACTLY the same query, except, you would group it by the hour like this:
-- Orders by the hour SELECT DATEADD(hh,DATEDIFF(hh,0,OrderDatestamp),0) AS OrderHour, SUM(OrderAmount) AS OrderAmount, COUNT(*) AS OrderCount FROM #Orders GROUP BY DATEADD(hh,DATEDIFF(hh,0,OrderDatestamp),0); -- Result -- OrderHour OrderAmount OrderCount -- 2013-07-25 13:00:00.000 150.00 2 -- 2013-07-25 14:00:00.000 250.00 2 -- 2013-07-26 09:00:00.000 175.00 2
What is even better, now you know how to group it by anything! By the day, by the hour, by the year, by the quarter, or even by the minute or by the second!!
Or, do you? What if the pointy haired gal wanted you to find the order amount and number of orders in 15 minute increments!?! I am not saying she will, but what if she did?
Honestly? I don’t know!! 🙂 Can YOU figure it out? Or, let us take the easy way out. We will do it next time. Remember our motto: Always strive to be shallow. And, never do it today if it can be done tomorrow.
So until next time, may the force be with you!!