Partition Time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I Have a table which contains the follownig data:

ID Time Of Scan Doors Open Time Since Opened

122Bd 12:00:12 12:00:00 00:00:12
135AA 11:15:02 11:00:00 00:15:02
147DD 09:15:12 09:00:00 00:15:12
122bd 12:10:00 12:00:00 00:10:00

What I am trying to do is get the following output,

ID 0-15Min 16-30Mins
122bd 2 0
135AA 0 1
147DD 0 1

I was hoping to use a partition to get the results but I am not getting
anywhere, any ideas?
 
I couldn't get the syntax of Partition working the way I wanted, but if you
only have a few time ranges you could just to this

SELECT MyTable.ID, Sum(IIf(DatePart("n",[TimeSinceOpened])<15,1,0)) AS [0 -
15 mins], Sum(IIf(DatePart("n",[TimeSinceOpened])>=15 And
DatePart("n",[TimeSinceOpened])<30,1,0)) AS [15 - 30 mins]
FROM MyTable
GROUP BY MyTable.ID;
 
Try (****not tested****)

SELECT [ID], Count([ID]) AS NoOfOccurrences,
DateDiff("s", [Doors Open], [Time Of Scan]) \ 900 + 1 AS [15 Mins Intervals]
FROM [YourTable]
GROUP BY [ID], DateDiff("s", [Doors Open], [Time Of Scan]) \ 900 + 1

The above Query then can be used as the DataSource for a Cross-Tab Query
which should give you the cross-tab format as required.
 
Back
Top