CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Fontanka16
Fontanka16

Reputation: 1323

Get history of record states sql server 2008

Say I had a Bus Garage application that contained a datatable that represented wether buses where either in the garage, out of the garage or in the shop for maintenance. It looks like this:

+-----------------------+-----+-----+
|Date                   |BusId|State|
+-----------------------+-----+-----+
|2013-09-12 15:02:41,844|1    |IN   |
+-----------------------+-----+-----+
|2013-09-12 15:02:41,844|2    |IN   |
+-----------------------+-----+-----+
|2013-09-12 15:02:41,844|3    |OUT  |
+-----------------------+-----+-----+
|2013-09-12 15:02:41,844|4    |OUT  |
+-----------------------+-----+-----+
|2013-09-12 15:02:41,844|5    |OUT  |
+-----------------------+-----+-----+
|2013-09-13 15:02:41,844|1    |OUT  |
+-----------------------+-----+-----+
|2013-09-14 15:02:41,844|1    |IN   |
+-----------------------+-----+-----+
|2013-09-15 15:02:41,844|1    |OUT  |
+-----------------------+-----+-----+
|2013-09-15 15:02:41,844|2    |OUT  |
+-----------------------+-----+-----+

Now i want to make a nice day-by-day (or hour by hour etc) dataset giving me an overview of how many buses where in the garage an how many that where out of it. 

+-------------------+-----+------------+
|Date               |State|Count(buses)|
+-------------------+-----+------------+
|2013-09-12 16:00:00|IN   |2           |
+-------------------+-----+------------+
|2013-09-12 16:00:00|OUT  |3           |
+-------------------+-----+------------+ 
|2013-09-13 16:00:00|IN   |1           |
+-------------------+-----+------------+
|2013-09-13 16:00:00|OUT  |4           |
+-------------------+-----+------------+
|2013-09-14 16:00:00|IN   |2           |
+-------------------+-----+------------+
|2013-09-14 16:00:00|OUT  |3           |
+-------------------+-----+------------+
|2013-09-15 16:00:00|IN   |0           |
+-------------------+-----+------------+
|2013-09-15 16:00:00|OUT  |5           |
+-------------------+-----+------------+

How (not necessary explained in code) would i go about to do this just using TSQL? I have one reqirement, and that is that i can not use variable declarations in my statement since i will have this as a View.

I asked a very similar question, but i felt that that one got too verbouse and not as general as this one.

Upvotes: 0

Views: 85

Answers (2)

yogesh
yogesh

Reputation: 23

try this....

DECLARE @businfo AS TABLE([date] datetime,busid int,[state] varchar(5))

INSERT INTO @businfo VALUES('2013-09-12 15:02:41',1,'IN')
INSERT INTO @businfo VALUES('2013-09-12 15:02:41',2,'IN')
INSERT INTO @businfo VALUES('2013-09-12 15:02:41',3,'OUT')
INSERT INTO @businfo VALUES('2013-09-12 15:02:41',4,'OUT')
INSERT INTO @businfo VALUES('2013-09-12 15:02:41',5,'OUT')
INSERT INTO @businfo VALUES('2013-09-13 15:02:41',1,'OUT')
INSERT INTO @businfo VALUES('2013-09-14 15:02:41',1,'IN')
INSERT INTO @businfo VALUES('2013-09-15 15:02:41',1,'OUT')
INSERT INTO @businfo VALUES('2013-09-15 15:02:41',2,'OUT')


select [date],[state],COUNT(busid) as [count(buses)] from @businfo
group by [date],[state]
order by [date]

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460148

Do you really want multiple records per day/hour just to display the different states? I would make them columns. You can use a CTE and the OVER clause to count per day/hour group:

WITH CTE AS 
(
    SELECT [Date] = DATEADD(day, DATEDIFF(day, 0, [Date]),0), 
           [BusId], [State],
           [IN]  = SUM(CASE WHEN State='IN'  THEN 1 END) OVER (PARTITION BY DATEADD(day, DATEDIFF(day, 0, [Date]),0)),
           [Out] = SUM(CASE WHEN State='Out' THEN 1 END) OVER (PARTITION BY DATEADD(day, DATEDIFF(day, 0, [Date]),0)),
           [DayNum] = ROW_NUMBER() OVER (PARTITION BY DATEADD(day, DATEDIFF(day, 0, [Date]),0)
                                         ORDER BY [Date], [BusID], [State])
    FROM dbo.Garage g    
)
SELECT [Date], [BusId], [State], [IN], [OUT]
FROM CTE 
WHERE [DayNum] = 1

Demo

Result:

DATE                               BUSID   STATE   IN   OUT
September, 12 2013 00:00:00+0000    1       IN     2    3
September, 13 2013 00:00:00+0000    1       OUT  (null) 1
September, 14 2013 00:00:00+0000    1       IN     1    (null)
September, 15 2013 00:00:00+0000    1       OUT  (null) 2

This works even in SQL-Server 2005. If you want to group by hour instead of day you have to change DATEADD(day, DATEDIFF(day, 0, [Date]),0) to DATEADD(hour, DATEDIFF(hour, 0, [Date]),0) everywhere.

Upvotes: 2

Related Questions