Solved

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

Posted on 2014-03-07
5
262 Views
Last Modified: 2014-05-29
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
0
Comment
Question by:Butterfly2
  • 2
  • 2
5 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39912349
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
 

Author Comment

by:Butterfly2
ID: 39912434
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
 

Author Comment

by:Butterfly2
ID: 39913657
Did you open the attachment?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39913788
Yes
But it was not clear...
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40054597
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now