i need to create a cross tab and in my column heading the date should be split per hour e.g 01:00 to 02:00  02:00 to 03:00 and so forth

 

here's the data from my datasource:

Code          StartDate                              EndDate                         Total

SPML001  2005-03-10 15:30:00.000  2005-03-10 18:30:00.000   16153
SPML001  2005-03-10 18:30:00.000  2005-03-10 19:30:00.000   0
SPML001  2005-03-10 19:30:00.000  2005-03-10 23:00:00.000   18847

 

for each line of StartDate to endDate it should split into hours and calculate the total per hour:

e.g the duration in hours for 2005-03-10 15:30:00.000 to 2005-03-10 18:30:00.000   is 3hrs and should display

 

15:30 to 14:30 14:30 to 15:30 15:30 to 16:30....... 23:30 to 00:00