Count occurrences by time

davidmatthewjames · April 24, 2024, 8:54am

Good morning, I have a table that has the time a call was made, and the reference number associated with it. I would like to give each call an occurrence based on the reference number in time order.

For example

Desired output

I hope some can help.

Kind regards

David

robert_volk · April 24, 2024, 10:39am

SELECT CallDateTime, ID
,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CallDateTime) Occurance
FROM myTable
ORDER BY ID, CallDateTime

davidmatthewjames · April 24, 2024, 11:09am

Perfect thank you. I will have to learn the Row number over partition stuff.

Thanks for your help, much appreciated.