Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have a data table like below
i want to check out the number of timesslots placed between StartTimeSlot and EndTimeSlot
for instance,
starttimeslot is 5, end timeslot is 8, so there 3 timeslot which is 5-6, 6-7, 7-8
| Date | EndTime | Member_id | SLOTHOUR | StartDate | StartTime | EndTimeSlot | StartTimeSlot | TimeSlot |
| 10/1/2013 | 10/1/2013 1:00 | 12830055 | 1 | 10/1/2013 0:00 | 10/1/2013 0:00 | 1:00 | 0:00 | 0:00 |
| 10/1/2013 | 10/1/2013 1:00 | 20943457 | 1 | 10/1/2013 0:00 | 10/1/2013 0:00 | 1:00 | 0:00 | 0:00 |
| 10/1/2013 | 10/1/2013 2:00 | 18047025 | 2 | 10/1/2013 0:00 | 10/1/2013 0:00 | 2:00 | 0:00 | 0:00 |
| 10/1/2013 | 10/1/2013 2:00 | 20943457 | 2 | 10/1/2013 0:00 | 10/1/2013 0:00 | 2:00 | 0:00 | 0:00 |
| 10/1/2013 | 10/1/2013 2:00 | 21530607 | 2 | 10/1/2013 0:00 | 10/1/2013 0:00 | 2:00 | 0:00 | 0:00 |
| 10/1/2013 | 10/1/2013 3:00 | 10057676 | 3 | 10/1/2013 0:00 | 10/1/2013 0:00 | 3:00 | 0:00 | 0:00 |
| 10/1/2013 | 10/1/2013 3:00 | 12830055 | 1 | 10/1/2013 0:00 | 10/1/2013 2:00 | 3:00 | 2:00 | 2:00 |
| 10/1/2013 | 10/1/2013 3:00 | 19228915 | 3 | 10/1/2013 0:00 | 10/1/2013 0:00 | 3:00 | 0:00 | 0:00 |
| 10/1/2013 | 10/1/2013 3:00 | 20454367 | 2 | 10/1/2013 0:00 | 10/1/2013 1:00 | 3:00 | 1:00 | 1:00 |
| 10/1/2013 | 10/1/2013 4:00 | 17103625 | 3 | 10/1/2013 0:00 | 10/1/2013 1:00 | 4:00 | 1:00 | 1:00 |
| 10/1/2013 | 10/1/2013 4:00 | 19228915 | 3 | 10/1/2013 0:00 | 10/1/2013 1:00 | 4:00 | 1:00 | 1:00 |
| 10/1/2013 | 10/1/2013 4:00 | 21567586 | 3 | 10/1/2013 0:00 | 10/1/2013 1:00 | 4:00 | 1:00 | 1:00 |
| 10/1/2013 | 10/1/2013 5:00 | 10057676 | 5 | 10/1/2013 0:00 | 10/1/2013 0:00 | 5:00 | 0:00 | 0:00 |
| 10/1/2013 | 10/1/2013 5:00 | 12830055 | 1 | 10/1/2013 0:00 | 10/1/2013 4:00 | 5:00 | 4:00 | 4:00 |
| 10/1/2013 | 10/1/2013 5:00 | 13296697 | 4 | 10/1/2013 0:00 | 10/1/2013 1:00 | 5:00 | 1:00 | 1:00 |
| 10/1/2013 | 10/1/2013 5:00 | 13296697 | 5 | 10/1/2013 0:00 | 10/1/2013 0:00 | 5:00 | 0:00 | 0:00 |
| 10/1/2013 | 10/1/2013 5:00 | 13769081 | 3 | 10/1/2013 0:00 | 10/1/2013 2:00 | 5:00 | 2:00 | 2:00 |
| 10/1/2013 | 10/1/2013 5:00 | 21530607 | 2 | 10/1/2013 0:00 | 10/1/2013 3:00 | 5:00 | 3:00 | 3:00 |
| 10/1/2013 | 10/1/2013 5:00 | 22571297 | 5 | 10/1/2013 0:00 | 10/1/2013 0:00 | 5:00 | 0:00 | 0:00 |
| 10/1/2013 | 10/1/2013 6:00 | 11404167 | 2 | 10/1/2013 0:00 | 10/1/2013 4:00 | 6:00 | 4:00 | 4:00 |
| 10/1/2013 | 10/1/2013 6:00 | 20454367 | 1 | 10/1/2013 0:00 | 10/1/2013 5:00 | 6:00 | 5:00 | 5:00 |
| 10/1/2013 | 10/1/2013 7:00 | 12830055 | 1 | 10/1/2013 0:00 | 10/1/2013 6:00 | 7:00 | 6:00 | 6:00 |
| 10/1/2013 | 10/1/2013 7:00 | 20943457 | 2 | 10/1/2013 0:00 | 10/1/2013 5:00 | 7:00 | 5:00 | 5:00 |
the chart i want to make out is like
| TimeSlot | timeslot count |
| total | 73 |
| 0:00 | 6 |
| 1:00 | 5 |
| 2:00 | 3 |
| 3:00 | 2 |
| 4:00 | 3 |
| 5:00 | 4 |
| 6:00 | 4 |
| 7:00 | 6 |
| 8:00 | 5 |
| 9:00 | 6 |
| 10:00 | 4 |
| 11:00 | 2 |
| 12:00 | 3 |
| 13:00 | 2 |
| 14:00 | 3 |
| 15:00 | 2 |
| 18:00 | 3 |
| 19:00 | 2 |
| 20:00 | 1 |
| 21:00 | 4 |
| 22:00 | 2 |
| 23:00 | 1 |
how to write the expression script?
| Date | Member_id | StartDate | StartTime | StartTimeSlot | EndTimeSlot | TimeSlot |
| 10/1/2013 | 12830055 | 10/1/2013 0:00 | 10/1/2013 0:00 | 0:00 | 1:00 | 0:00 |
| 10/1/2013 | 20943457 | 10/1/2013 0:00 | 10/1/2013 0:00 | 0:00 | 1:00 | 0:00 |
| 10/1/2013 | 18047025 | 10/1/2013 0:00 | 10/1/2013 0:00 | 0:00 | 2:00 | 0:00 |
| 10/1/2013 | 20943457 | 10/1/2013 0:00 | 10/1/2013 0:00 | 0:00 | 2:00 | 0:00 |
| 10/1/2013 | 21530607 | 10/1/2013 0:00 | 10/1/2013 0:00 | 0:00 | 2:00 | 0:00 |
| 10/1/2013 | 10057676 | 10/1/2013 0:00 | 10/1/2013 0:00 | 0:00 | 3:00 | 0:00 |
| 10/1/2013 | 12830055 | 10/1/2013 0:00 | 10/1/2013 2:00 | 2:00 | 3:00 | 2:00 |
| 10/1/2013 | 19228915 | 10/1/2013 0:00 | 10/1/2013 0:00 | 0:00 | 3:00 | 0:00 |
| 10/1/2013 | 20454367 | 10/1/2013 0:00 | 10/1/2013 1:00 | 1:00 | 3:00 | 1:00 |
| 10/1/2013 | 17103625 | 10/1/2013 0:00 | 10/1/2013 1:00 | 1:00 | 4:00 | 1:00 |
| 10/1/2013 | 19228915 | 10/1/2013 0:00 | 10/1/2013 1:00 | 1:00 | 4:00 | 1:00 |
| 10/1/2013 | 21567586 | 10/1/2013 0:00 | 10/1/2013 1:00 | 1:00 | 4:00 | 1:00 |
| 10/1/2013 | 10057676 | 10/1/2013 0:00 | 10/1/2013 0:00 | 0:00 | 5:00 | 0:00 |
| 10/1/2013 | 12830055 | 10/1/2013 0:00 | 10/1/2013 4:00 | 4:00 | 5:00 | 4:00 |
| 10/1/2013 | 13296697 | 10/1/2013 0:00 | 10/1/2013 1:00 | 1:00 | 5:00 | 1:00 |
| 10/1/2013 | 13296697 | 10/1/2013 0:00 | 10/1/2013 0:00 | 0:00 | 5:00 | 0:00 |
| 10/1/2013 | 13769081 | 10/1/2013 0:00 | 10/1/2013 2:00 | 2:00 | 5:00 | 2:00 |
| 10/1/2013 | 21530607 | 10/1/2013 0:00 | 10/1/2013 3:00 | 3:00 | 5:00 | 3:00 |
| 10/1/2013 | 22571297 | 10/1/2013 0:00 | 10/1/2013 0:00 | 0:00 | 5:00 | 0:00 |
| 10/1/2013 | 11404167 | 10/1/2013 0:00 | 10/1/2013 4:00 | 4:00 | 6:00 | 4:00 |
| 10/1/2013 | 20454367 | 10/1/2013 0:00 | 10/1/2013 5:00 | 5:00 | 6:00 | 5:00 |
| 10/1/2013 | 12830055 | 10/1/2013 0:00 | 10/1/2013 6:00 | 6:00 | 7:00 | 6:00 |
| 10/1/2013 | 20943457 | 10/1/2013 0:00 | 10/1/2013 5:00 | 5:00 | 7:00 | 5:00 |
| 10/1/2013 | 1319988 | 10/1/2013 0:00 | 10/1/2013 5:00 | 5:00 | 8:00 | 5:00 |
| 10/1/2013 | 11404167 | 10/1/2013 0:00 | 10/1/2013 7:00 | 7:00 | 8:00 | 7:00 |
| 10/1/2013 | 18047025 | 10/1/2013 0:00 | 10/1/2013 5:00 | 5:00 | 8:00 | 5:00 |
| 10/1/2013 | 21530607 | 10/1/2013 0:00 | 10/1/2013 7:00 | 7:00 | 8:00 | 7:00 |
| 10/1/2013 | 21530607 | 10/1/2013 0:00 | 10/1/2013 6:00 | 6:00 | 8:00 | 6:00 |
| 10/1/2013 | 1383563 | 10/1/2013 0:00 | 10/1/2013 8:00 | 8:00 | 9:00 | 8:00 |
See attached qvw.