SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Hourly Buckets of Minutes
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JAdauto
Posting Yak Master

USA
159 Posts

Posted - 07/16/2013 :  11:01:25  Show Profile  Reply with Quote
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

Sweden
30282 Posts

Posted - 07/16/2013 :  11:37:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- 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;


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

Edited by - SwePeso on 07/16/2013 11:41:29
Go to Top of Page

JAdauto
Posting Yak Master

USA
159 Posts

Posted - 07/16/2013 :  12:25:48  Show Profile  Reply with Quote
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!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000