[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2014-03-07
5
Medium Priority
?
273 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 143

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 143

Expert Comment

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

Accepted Solution

by:
Scott Pletcher earned 2000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

649 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