Solved

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

Posted on 2014-03-07
5
266 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 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 69

Accepted Solution

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL, How to do aggregation on extracting field and range band field? 2 30
TSQL convert date to string 4 34
SQL, add where clause 5 23
sql server query 18 37
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

856 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