Convert Start and End Dates to Time records

Question

Convert Start and End Dates to Time records

paul.desmond

SSC Rookie

Points: 25
More actions
October 4, 2008 at 10:04 am

#70829

I have a SQL table from a holiday approval system that needs to be reconciled to a time recording system. The holiday approval system gives a start date and an end date for the approval and duration in days and hours. I want to covert it so it shows how much holiday was taken on each day. For example I would like to convert a users records from this:
Start Date End Date Duration Hours
01/04/2008 04/04/2008 4 28
to this:
Date Hours
01/04/2008 7
02/04/2008 7
03/04/2008 7
04/04/2008 7
Any ideas?

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 1

Sure... but let me ask a question... what does "Duration" contain? It can't be the number of months because there's actually only 3 months between the two dates given.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dugi SSCoach Points: 17998 More actions · Answer 2

hmm are you sure that every day has 7 hour or it is different with any other day!?

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Dugi SSCoach Points: 17998 More actions · Answer 3

Jeff Moden (10/4/2008)
Sure... but let me ask a question... what does "Duration" contain? It can't be the number of months because there's actually only 3 months between the two dates given.

As I can see here the date format is DD/MM/YYYY OR simple DMY

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 4

Paul, is that correct? The date format is dd/mm/yyyy?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

paul.desmond SSC Rookie Points: 25 More actions · Answer 5

Yes sorry date format is dd/mm/yyyy. The duration is number of days. The hours are that person's std hours per day * number of days. In this instance this person works 7 hours a day, part-time staff would have a different result for hours but same for days.

Regards

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 6

No problem... it's easy either way... just needed to know which way. 🙂

See if this doesn't fit the bill... It does require a Tally table which you can get at the following URL... it's a very useful tool!

http://www.sqlservercentral.com/articles/TSQL/62867/

Here's a set based solution that uses a cross join with the Tally table to spawn the missing dates... one of many ways that a Tally table can be used to replace a loop...

--===== Set the correct date format

SET DATEFORMAT DMY

--===== Build the test table and populate it with data.

-- Note that this is NOT part of the solution

DECLARE @YourTable TABLE (StartDate DATETIME, EndDate DATETIME, Duration INT, Hours INT)

INSERT INTO @YourTable

(StartDate, EndDate, Duration, Hours)

SELECT '01/04/2008', '04/04/2008', 4, 28 UNION ALL --Your orginal example

SELECT '01/05/2008', '04/06/2008', 35, 320 --A large example just to show crossing month boundaries

--===== Spawn the necessary number of sequential dates and calculate

-- the number of hours for each day evenly distributed

SELECT DATEADD(dd,t.N,y.StartDate-1) AS Date,

CAST((y.Hours+0.0)/y.Duration AS DECIMAL(9,2)) AS Hours

FROM @YourTable y

CROSS JOIN dbo.Tally t

WHERE t.N <= y.Duration

ORDER BY y.StartDate

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

paul.desmond SSC Rookie Points: 25 More actions · Answer 7

Thanks for this. Have only just got around to applying this and works a treat. You are right really useful. Thanks again.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 8

No problem... thanks for taking the time to feedback, Paul. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)