Hourly Buckets of Minutes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Hourly Buckets of Minutes

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2013-07-16 : 11:01:25

I have a set of data that contains the Hours worked for an employee. I show InHour, InMinute, OutHour, OutMinute all Int datatypes.
For example:
InHour = 8
InMinute = 15
OutHour = 13
OutMinute = 30
This would indicate that the employee worked from 8:15 - 1:30. I need these results to show the number of minutes they worked in each hour such as this:
08 - 45
09 - 60
10 - 60
11 - 60
12 - 60
01 - 30

I cannot figure out how to 1) bucket up the hours just between when they worked instead of all 24 hours in a day and 2) how to subtract for just that hour such as 60 - InHour for the first hour worked, 60 for the inbetween hours and 0 + OutMinute for the last hour worked.

Suggestions, Ideas, leads?

Thanks tons,
JAdauto

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-16 : 11:37:52

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
Person VARCHAR(10) NOT NULL,
InHour TINYINT NOT NULL,
InMinute TINYINT NOT NULL,
OutHour TINYINT NOT NULL,
OutMinute TINYINT NOT NULL
);

INSERT @Sample
(
Person,
InHour,
InMinute,
OutHour,
OutMinute
)
VALUES ('Demo', 8, 15, 13, 30),
('SwePeso', 8, 15, 8, 30);

-- SwePeso
SELECT s.Person,
v.Number AS theHour,
CASE
WHEN s.InHour = s.OutHour THEN s.OutMinute - s.InMinute
WHEN s.InHour = v.Number THEN 60 - s.InMinute
WHEN s.OutHour = v.Number THEN s.OutMinute
ELSE 60
END AS theMinutes
FROM @Sample AS s
INNER JOIN master.dbo.spt_values AS v ON v.Type = 'P'
AND v.Number BETWEEN s.InHour AND s.OutHour
ORDER BY s.Person,
v.Number;[/code]

N 56°04'39.26"
E 12°55'05.63"

JAdauto
Posting Yak Master

160 Posts

Posted - 2013-07-16 : 12:25:48

SwePeso, you are a-m-a-z-i-n-g!! That is EXACTLY what I needed. Thank you so much for being smarter then me and helping those who need it!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources