CODEWITHSUNDEEP
sqlsql-server-2008
user2979612
user2979612

Reputation: 177

Getting all distinct values and sorting them by hour

I have the following table:

ID    TimeStamp    CarNumber
1    2018\12\03 14:05:32    433
2    2018\12\03 14:13:52    420
3    2018\12\03 14:55:14    433
4    2018\12\03 15:12:03    420
5    2018\12\03 16:15:55    570

My desired output is a list of each car number in each hour of the day:

Hour       CarNumbers
0          0
...
14         433, 420
15         420
16         570
...

I'm using SQL Server 2008 so I can't use string_agg(). Is there a way to do this?

Upvotes: 2

Views: 78

Answers (2)

LukStorms
LukStorms

Reputation: 29677

To get counts for all the hours, you'll need to left join to a list with all the hours.
And probably combined with the date of the timestamps if they span more than 1 date.

To substitute the missing STRING_AGG you could use the FOR XML trick.

Example snippet: 

-- Using a table variable for demonstration
declare @Table table (id int primary key identity(1,1), [Timestamp] datetime, CarNumber int);

-- Sample data
insert into @Table ([Timestamp], CarNumber) values
('2018-12-03 14:05:32', 433),
('2018-12-03 14:13:52', 420),
('2018-12-03 14:55:14', 433),
('2018-12-03 15:12:03', 420),
('2018-12-03 16:15:55', 570),
('2018-12-09 14:00:00', 999);

-- Query
WITH RCTE_HOURS AS
(
  select 0 as [Hour] 
  union all
  select [Hour] + 1
  from RCTE_HOURS
  where [Hour] < 23
)
SELECT h.[Hour],
 COALESCE(STUFF((
   SELECT ', ' + CONVERT(VARCHAR(10), CarNumber) 
   FROM @Table t2 
   WHERE CAST(t2.[Timestamp] AS DATE) = d.[Date]
     AND DATEPART(HOUR, t2.[Timestamp]) = h.[Hour]
     AND CarNumber IS NOT NULL
   GROUP BY CarNumber
   ORDER BY MIN(t2.[Timestamp])
   FOR XML PATH ('')
   ), 1, 2, ''),'0') AS CarNumbers
FROM 
(
  SELECT DISTINCT CAST([Timestamp] AS DATE) AS [Date]
  FROM @Table
  WHERE CAST([Timestamp] AS DATE) = CAST('2018-12-03' AS DATE)
) AS d
CROSS JOIN RCTE_HOURS h
ORDER BY d.[Date], h.[Hour];

Returns:

Hour CarNumbers
---- ---------- 
0    0
1    0
2    0
3    0
4    0
5    0
6    0
7    0
8    0
9    0
10   0
11   0
12   0
13   0
14   433, 420
15   420
16   570
17   0
18   0
19   0
20   0
21   0
22   0
23   0

Upvotes: 1

Nikhil
Nikhil

Reputation: 3950

SELECT   DATEPART(HOUR, Timestamp), STUFF(
             (SELECT ',' + carnumber 
              FROM tablename t1
              FOR XML PATH (''))
             , 1, 1, '') carnumbers 
from tablename 
group by  DATEPART(HOUR, Timestamp) asc ;

Upvotes: 0

Related Questions