asked on

SQL- Summary of time in hours, calculating datediff from the same field

Good Morning Experts,

I need some assistance I need to calculate time(in hours), which I would normally use date diff, however the time reocords or all in the same field(except for the last transfer)

Please see attachment:
I have 2 different episodes that were at 2 nurse stations, basically I need a query that shows

the sum of total episodes and total hours spent at each location for example

Nurs_Sta Total Episodes Total Hours

3NTW 2 7
3NTE 2 140
results-sample.xlsx

Guy Hengel [angelIII / a3]

can you please clarify how the number 2 for total episodes is to be determined?
and the "total_hours" value from from which calculation, actually?
it's not really clear to me looking at that data...

Butterfly2

ASKER

the number of episodes total # of episodes it 2 because u have 2 distinct episoded numbers.
528322159
528472129

as for total hours it was just a guess but you would have to add up the hours the nurse station regardless of the episode, for exmaple you would us the xfer_eff_dtime field:

for nurs_sta 3NTW and Episode 528322159
you would have to get the hours from the following times(all from the xfer_eff_dtime field)
2014-01-13 11:07:00.000 to
2014-01-13 15:23:00.000 to
2014-01-13 15:35:00.000

then for nurs_sta 3NTE, since its the last place where that episode was located you take the xfer_eff_dtime from the end_dtime to get the hours

so for 3NTE it would be
2014-01-13 17:46:00.000 to
2014-01-16 17:43:00.000

and you would do the same for the other episode and the hours for each nurs_sta.

hopefully I cleared it up a little bit.

I hope I was able to clarify for you.

Butterfly2

ASKER

Did you open the attachment?

Guy Hengel [angelIII / a3]

Yes
But it was not clear...

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial