Hi!,
I need to examine 30 million records (one month worth) of daily ticket validations (Unix datetime) and divy them up into 24 one-hour periods for 211 stations.
First I created a view which selects the month I’m looking for (and the equipment type) then creates a Windows Datetime value for each Unix datetime.
SELECT TOP (100) PERCENT StationName AS Station, MainTable.UnixDateTime AS ValTime,
DATEADD(s, MainTable.UnixDateTime, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS WinTime
FROM MainTable
INNER JOIN StationName ON MainTable.StationID = StationName.StationID
WHERE (StationName.ValidStationCode = 32767) [use only valid stations]
AND (MainTable. UnixDateTime >= 1264996800)
AND (MainTable. UnixDateTime < 1267416000)
AND (MainTable.EquipmentCode IN (33, 36)) [examine only this equipment]
ORDER BY Station
I then run the main procedure which uses a select statement on this view for each of the one-hour periods. That’s 24 select statements for each of 211 stations.
Example 1)
Update table Set [0102]= (select count(ValTime)
from view
where Station = @thisStation and DatePart (Hour, WinTime)>= 1 and DatePart (Hour, WinTime)< 2)
from view
where table.Station = @thisStation
Example 2)
Update table Set [0203]= (select count(ValTime)
from view
where Station = @thisStation and DatePart (Hour, WinTime)>= 2 and DatePart (Hour, WinTime)< 3)
from view
where table.Station = @thisStation
etc.
The procedure works - Yay! It takes 11 hours - Boo :(
Output e.g.:
0001 0102 0203 0304...2324
Station1 27 34 567 231... 123
Station2 245 57 23 198... 21
etc.
The output table has 25 columns.
There is a faster, better way of doing this but I don't know what it is. (Buy a supercomputer?) I've thought about Rank and partition but can't visualize how that would work. Any gurus out there?