Loop & PIVOT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Loop & PIVOT

Author

Topic

stevieb
Starting Member

13 Posts

Posted - 2014-05-20 : 00:28:36

I am trying to repeat some analysis that I would typically undertake using Excel (Pivot Tables). But the dataset is to large for Excel now.
An Extract of the Data is below.

probeID dbdate dbTime
AA 4657 2014-03-20 17:26:10
AA 2968 2014-03-20 17:26:54
AD 2340 2014-03-20 17:25:35
AA 4415 2014-03-20 23:30:48

I would like to generate the number of Unique 'ProbeID' for each hour of the day for all of the days of the calendar month

So the output would look like

dbDate..... Hr 00:00 01:00 02:00 etc etc to 23:00
01/04/2014 #Value
02/04/2014 #Value

Where the Value is generated from the below

SELECT COUNT(DISTINCT probeid) AS TAXI_IDs FROM Taxi_Apr14_All
WHERE
dbdate = '2014-04-16' AND
dbTime < '18:00:00' AND
dbTime >= '17:00:00'

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-05-20 : 02:48:22

DECLARE @Taxi_Apr14_All TABLE(probeID varchar(50), dbdate DATE, dbTime TIME)
insert @Taxi_Apr14_All
SELECT 'AA 4657', '2014-03-20', '17:26:10' union all
SELECT 'AA 2968', '2014-03-20', '17:26:54' union all
SELECT 'AD 2340', '2014-03-20', '17:25:35' union all
SELECT 'AD 2340', '2014-03-21', '17:25:35' union all
SELECT 'AA 4415', '2014-03-20', '23:30:48'

--Query
SELECT *
FROM (SELECT ProbeID, dbdate, convert( varchar(2),dbTime, 24)+':00' AS dbTime FROM @Taxi_Apr14_All) P
PIVOT (COUNT(ProbeID) FOR dbTime IN ([00:00], [01:00], [02:00], [03:00], [04:00], [05:00], [06:00], [07:00], [08:00], [09:00], [10:00], [11:00], [12:00], [13:00], [14:00], [15:00], [16:00], [17:00], [18:00], [19:00], [20:00], [21:00], [22:00], [23:00]))Pvt

--
Chandu

stevieb
Starting Member

13 Posts

Posted - 2014-05-20 : 03:33:18

Bandi,

Thanks, The scripts works great but.. I need to count the number of unique (DISTINCT) Values in each time period.

If I run the following
SELECT COUNT(DISTINCT probeid) AS TAXI_IDs FROM Taxi_Apr14_All

It shows that I have 7630 unique values.

When I run the script you have suggested for some of the time segments I get 9000 observations. Which is correct for the number of observations in the time. But I am looking for the unique values.

stevieb
Starting Member

13 Posts

Posted - 2014-05-20 : 03:42:45

DECLARE @Taxi_Apr14_All TABLE(probeID varchar(50), dbdate DATE, dbTime TIME)
insert @Taxi_Apr14_All
SELECT 'AA 4657', '2014-03-20', '17:26:10' union all
SELECT 'AA 2968', '2014-03-20', '17:26:54' union all
SELECT 'AD 2340', '2014-03-20', '17:25:35' union all
SELECT 'AD 2340', '2014-03-20', '17:15:35' union all
SELECT 'AD 2340', '2014-03-20', '17:25:35' union all
SELECT 'AD 2340', '2014-03-20', '17:35:35' union all
SELECT 'AD 2340', '2014-03-20', '17:45:35' union all
SELECT 'AD 2340', '2014-03-20', '17:55:35' union all
SELECT 'AA 4415', '2014-03-20', '23:30:48'

--Query
SELECT *
FROM (SELECT ProbeID, dbdate, convert( varchar(2),dbTime, 24)+':00' AS dbTime FROM @Taxi_Apr14_All) P
PIVOT (COUNT(ProbeID) FOR dbTime IN ([00:00], [01:00], [02:00], [03:00], [04:00], [05:00], [06:00], [07:00], [08:00], [09:00], [10:00], [11:00], [12:00], [13:00], [14:00], [15:00], [16:00], [17:00], [18:00], [19:00], [20:00], [21:00], [22:00], [23:00]))Pvt

The result for 17:00 should be 3. but in the above dataset and script it appears as 8

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-05-20 : 06:12:59

--Query
SELECT *
FROM (SELECT distinct ProbeID, dbdate, convert( varchar(2),dbTime, 24)+':00' AS dbTime FROM @Taxi_Apr14_All) P
PIVOT (COUNT( ProbeID) FOR dbTime IN ([00:00], [01:00], [02:00], [03:00], [04:00], [05:00], [06:00], [07:00], [08:00], [09:00], [10:00], [11:00], [12:00], [13:00], [14:00], [15:00], [16:00], [17:00], [18:00], [19:00], [20:00], [21:00], [22:00], [23:00]))Pvt

--
Chandu

stevieb
Starting Member

13 Posts

Posted - 2014-05-20 : 07:32:04

Thank you

I added an extra 'as Probe ID' and was getting a syntax error

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-05-20 : 07:36:08

its Okay..

Revert us back if u face any issues :)

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources