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
Butterfly2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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...
0
Butterfly2Author Commented:
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.
0
Butterfly2Author Commented:
Did you open the attachment?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Yes
But it was not clear...
0
Scott PletcherSenior DBACommented:
SELECT nurs_sta, COUNT(DISTINCT episode_no) AS [Total Episodes],
    FLOOR(ROUND(SUM(DATEDIFF(MINUTE, xfer_eff_dtime, end_dtime)) / 60.0, 0)) AS Total_Hours
FROM dbo.tablename
WHERE
    ...
GROUP BY nurs_sta
ORDER BY nurs_sta


For example, with the provided test data:
SELECT nurs_sta, COUNT(DISTINCT episode_no) AS [Total Episodes],
    FLOOR(ROUND(SUM(DATEDIFF(MINUTE, xfer_eff_dtime, end_dtime)) / 60.0, 0)) AS Total_Hours
FROM (
SELECT 528322159 AS episode_no,      'IA' AS vst_type_cd, 'S' AS pt_type, '3NTW' AS nurs_sta,
    '2014-01-13 11:07:00.000' AS xfer_eff_dtime, '2014-01-16 17:43:00.000' AS end_dtime UNION ALL
SELECT 528322159,      'IA','S','3NTW','2014-01-13 15:23:00.000','      2014-01-16 17:43:00.000' UNION ALL
SELECT 528322159,      'IA','S','3NTW','2014-01-13 15:35:00.000','      2014-01-16 17:43:00.000' UNION ALL
SELECT 528322159,      'IA','P','3NTE','2014-01-13 17:46:00.000','      2014-01-16 17:43:00.000' UNION ALL
SELECT 528472129,      'IA','S','3NTW','2014-01-07 09:46:00.000','      2014-01-09 18:41:00.000' UNION ALL
SELECT 528472129,      'IA','S','3NTW','2014-01-07 11:58:00.000','      2014-01-09 18:41:00.000' UNION ALL
SELECT 528472129,      'IA','S','3NTW','2014-01-07 12:33:00.000','      2014-01-09 18:41:00.000' UNION ALL
SELECT 528472129,      'IA','P','3NTE','2014-01-07 14:28:00.000','      2014-01-09 18:41:00.000'
) AS test_data
GROUP BY nurs_sta
ORDER BY nurs_sta
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.