Aggregate Functions MIN and MAX

I was tasked to develop a performance report using SQL Server Reporting Services 2005. One of the requirements for this report was to get the MAX and MIN date values and then perform a DATEDIFF to get the difference of days between the two values. One of the hurdles I faced was the fact that table contained multiple row for a single woid so to shed some clarity I put together some sample data to illustrate my scenario. There is probably multiple ways to accomplish the same end results, but this was my route.

Create a temp table

For this example we will be using only three fields. I added the ID field for my own selfish reasons… actually come to think of it… I really won’t need it. Any who here is the skinny.

CREATE TABLE #tmpTbl
(
	ID INT IDENTITY(1,1)
	,WOID INT
	,CreateDate datetime
)

Insert records

This is pretty straightforward as it is a simple insert statement that will insert 100 rows.

INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (1,'2008-12-11 01:33:55.997')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (1,'2008-12-11 01:33:56.010')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (2,'2008-12-11 06:02:56.983')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (2,'2008-12-11 06:04:12.640')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (2,'2008-12-11 06:04:17.907')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (3,'2008-12-11 07:49:53.810')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (3,'2008-12-11 07:49:53.810')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (3,'2008-12-11 07:55:40.567')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (4,'2008-12-11 08:00:50.477')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (4,'2008-12-11 08:00:50.477')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (4,'2008-12-11 08:03:27.480')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (5,'2008-12-11 08:12:07.927')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (5,'2008-12-11 08:12:07.927')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (5,'2008-12-11 08:18:42.327')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (6,'2008-12-11 08:59:49.217')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (6,'2008-12-11 09:07:24.443')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (6,'2008-12-11 09:09:30.883')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (6,'2008-12-11 09:29:05.030')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (6,'2008-12-11 09:29:23.610')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (7,'2008-12-11 09:55:24.327')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (7,'2008-12-11 09:55:24.340')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (8,'2008-12-11 09:58:10.970')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (8,'2008-12-11 09:58:10.970')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (8,'2008-12-11 09:58:23.170')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (9,'2008-12-11 10:00:32.207')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (9,'2008-12-11 10:00:32.207')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (10,'2008-12-11 10:26:03.173')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (10,'2008-12-11 10:28:43.940')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (10,'2008-12-11 12:27:09.417')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (10,'2008-12-11 12:28:07.600')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (11,'2008-12-11 12:48:33.023')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (11,'2008-12-11 12:48:33.023')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (12,'2008-12-11 13:26:49.440')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (12,'2008-12-11 13:26:49.470')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (13,'2008-12-11 13:28:56.300')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (13,'2008-12-11 13:28:56.300')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (14,'2008-12-11 13:31:01.223')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (14,'2008-12-11 13:31:01.223')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (15,'2008-12-11 14:05:41.173')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (15,'2008-12-11 14:05:41.207')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-11 15:09:38.990')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-11 15:09:38.990')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (5,'2008-12-11 15:15:44.560')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (15,'2008-12-11 15:18:54.733')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-11 15:25:39.223')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (13,'2008-12-11 15:27:13.160')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (12,'2008-12-11 15:27:58.490')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (11,'2008-12-11 15:29:50.600')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (11,'2008-12-11 15:29:53.663')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-11 15:31:30.210')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-11 17:09:22.057')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (11,'2008-12-11 17:09:54.043')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (6,'2008-12-11 17:11:10.353')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (17,'2008-12-12 07:06:27.550')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (17,'2008-12-12 07:06:27.550')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (3,'2008-12-12 07:07:23.393')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (3,'2008-12-12 07:10:41.897')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (10,'2008-12-12 07:18:08.377')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (13,'2008-12-12 07:20:14.627')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (7,'2008-12-12 07:46:29.563')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (9,'2008-12-12 08:08:37.073')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-12 08:14:06.267')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-12 08:30:03.270')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-12 08:30:25.350')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-12 08:34:11.557')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-12 08:42:04.197')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (18,'2008-12-12 08:43:36.873')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (19,'2008-12-12 08:48:38.390')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (19,'2008-12-12 08:48:38.390')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-12 08:56:07.633')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (18,'2008-12-12 08:56:39.760')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (19,'2008-12-12 08:57:03.603')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (19,'2008-12-12 08:57:26.807')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (20,'2008-12-12 09:00:13.077')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (20,'2008-12-12 09:00:13.077')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (20,'2008-12-12 09:00:38.153')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (20,'2008-12-12 09:00:46.437')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (21,'2008-12-12 09:13:14.637')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (21,'2008-12-12 09:20:57.927')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (21,'2008-12-12 09:22:33.040')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (21,'2008-12-12 09:23:29.383')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (18,'2008-12-12 09:28:24.827')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (16,'2008-12-12 09:34:04.410')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (9,'2008-12-12 09:50:24.820')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (9,'2008-12-12 09:54:03.217')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (10,'2008-12-12 09:54:29.327')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (10,'2008-12-12 09:54:35.937')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (22,'2008-12-12 10:25:15.753')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (22,'2008-12-12 10:25:15.753')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (23,'2008-12-12 10:30:18.803')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (23,'2008-12-12 10:30:18.803')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (9,'2008-12-12 10:33:03.073')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (9,'2008-12-12 10:37:01.220')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (9,'2008-12-12 10:37:15.580')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (11,'2008-12-12 10:38:25.737')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (8,'2008-12-12 10:40:53.490')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (8,'2008-12-12 10:47:17.497')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (8,'2008-12-12 10:47:46.090')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (8,'2008-12-12 10:48:23.310')
INSERT INTO #tmpTbl (WOID,CreateDate) VALUES (18,'2008-12-12 11:00:24.733')

Let’s select the raw data

As you can see most records have more than one CreateDate associated to a woid.

SELECT * FROM #tmpTbl ORDER BY WOID

Let’s get some counts

So let’s select the data to get date counts and then group by woid to identify how many dates per woid. The results imply that there is no count that is below 2.

SELECT WOID, COUNT(CreateDate) 'Total' 
FROM #tmpTbl
GROUP BY WOID
ORDER BY Total

Getting MIN and MAX values

I figured we better run through this or touch base on this briefly. The first query will return the minimum date value per record set, meaning only only record per set will be returned. The same goes for the second query following; however, it returns the maximum date value per record set. For more information about aggregate functions please visit: MSDN: AGGREGATE FUNCTIONS

SELECT	WOID
		,MIN(CreateDate) 'MinDate'
FROM #tmpTbl
GROUP BY WOID

SELECT	WOID
		,MAX(CreateDate) 'MaxDate'
FROM #tmpTbl
GROUP BY WOID

Returning both aggregates

In the following query I am returning both the MIN and MAX date value for a given woid. I join a second instance of the #tmpTbl to itself on woid and then group by the woid to be in accordance with the rules of aggregation.

SELECT	t1.WOID
		,MIN(t1.CreateDate) 'MinDate'
		,MAX(t2.CreateDate) 'MaxDate'
FROM #tmpTbl t1
JOIN #tmpTbl t2 ON t1.WOID = t2.WOID
GROUP BY t1.WOID

Verify

Just to make sure the values returned are correct let’s select a record to verify against to make sure the results are as expected. It’s always a good idea to verify by the way!

SELECT	WOID
		,CreateDate
FROM #tmpTbl
WHERE (WOID = 2)
ORDER BY CreateDate

Going further

Now that we have our date aggregates in place it would be a good idea to get the difference in days between the two values by using DATEDIFF. In the following query I am using the D interval which represents Days. For more information about DateDiff and it’s intervals please visit: MSDN: DATEDIFF

SELECT	t1.WOID
		,MIN(t1.CreateDate) 'MinDate'
		,MAX(t2.CreateDate) 'MaxDate'
		,DATEDIFF(d,MIN(t1.CreateDate),MAX(t2.CreateDate)) 'Days'
FROM #tmpTbl t1
JOIN #tmpTbl t2 ON t1.WOID = t2.WOID
GROUP BY t1.WOID
DROP TABLE #tmpTbl

This works well in this case, but I am dealing with a few more requirements which adds a little more complexity to the equation. I have to filter by additional statuses which are returning more then one instance of the record per woid due to a one-to-many relationship. Then I need to join in two additional tables which also pose a one-to-many situation. This is affecting the MIN and MAX aggregate dates severely. As soon as I douse the fire I will add to this post with further details.

Amended

Turns out my query was correct in design, but the data had some anomalies that caused records to display multiple times due to a couple of things. Some of the records did not have a completed status which were filtered out in my WHERE clause. Then some of the records had duplicate CreateDates with a different associated GrpID but with the same StatID which is an oddity. This caused some of the records to display twice. I’ll need to figure out a way to select only one. I am using a SELECT TOP 1 which passes the CreateDate but since the dataset that I am passing it has duplicate records I still get the duplicates returned. S I might need to pass the data to a temp table then do a filter and then join back to the main query via UNION ALL.

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