split numbers into months - 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)

split numbers into months

Author

Topic

BigBolts
Starting Member

5 Posts

Posted - 2012-06-14 : 13:08:18

Hi All,

I have a customer who tracks the number of hours lost due to incidents. They record the Date, the return date and the total hours lost. E.g. Date 01/01/2012, return 02/04/2012, total hours lost 500.

id, date, return, hourslost
1, 01/01/2012, 02/04/2012, 500
2, 05/01/2012, 06/03/2012, 200

I need a way to split the total hours over the number of months between the dates. In the example for id 1, it would be split over 4 months. The day is not important, they consider no difference between the 1st or 31st of a month.

However, the maximum hours available for each month is 148. So, in the example, the hours would be split into:
Jan 148
Feb 148
Mar 148
Apr 56

This is to provide a cumulative hours lost graph, that gives a true representation of the hours lost over the whole time period. I have no way to change the database.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-14 : 13:22:18

If the total was 200 would it be
Jan 148
Feb 52
Mar 0
Apr 0

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-14 : 14:03:23

what type of calendar are you using?
This is January 1, 2012 01/01/2012
Is this 02/04/2012 April 2, 2012? I assume it is since you said "In the example for id 1, it would be split over 4 months"

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-14 : 15:39:23

what if hours lost was 650 in first case? will you cap it to 148 for all months?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-06-15 : 00:41:31

[code]DECLARE @Sample TABLE
(
ID INT PRIMARY KEY,
FromDate DATE NOT NULL,
ToDate DATE NULL,
HoursLost INT NULL
)

INSERT @Sample
VALUES (1, '20120101', '20120402', 500),
(2, '20120105', '20120306', 200)

-- SwePeso
SELECT s.ID,
DATEADD(MONTH, v.Number + DATEDIFF(MONTH, '19000101', s.FromDate), '19000101') AS theMonth,
CASE DATEDIFF(MONTH, s.FromDate, s.ToDate)
WHEN v.Number THEN s.HoursLost % 148
ELSE 148
END AS HoursLost
FROM @Sample AS s
INNER JOIN master.dbo.spt_values AS v ON v.Type = 'P'
AND v.Number BETWEEN 0 AND DATEDIFF(MONTH, s.FromDate, s.ToDate)
ORDER BY s.ID,
v.Number[/code]

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

BigBolts
Starting Member

5 Posts

Posted - 2012-06-15 : 04:43:42

nigelrivett - Yes it would be 148, 52, 0 (only 3 months)

yosiasz - it's UK format, day first, so you assumed correctly.

visakh16 - the number would never go higher than what was available e.g. if there were 650 hours lost, it would be over a longer period.

swepeso - that's awseome!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources