Group them by the day!

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:

DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

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!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s