Solved

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

Posted on 2014-03-07
5
259 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]
Comment Utility
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
Comment Utility
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
Comment Utility
Did you open the attachment?
0
 
LVL 142

Expert Comment

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

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 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

14 Experts available now in Live!

Get 1:1 Help Now