Day and night period - 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 2005 Forums

Transact-SQL (2005)

Day and night period

Author

Topic

paulafernandes
Starting Member

10 Posts

Posted - 2008-02-15 : 09:23:29

Hello everyone

I have a problem and I'm breaking my head for 2 days thinking how to solve this!

I have a SQL Server database with a table 3 fields, a id, a begin and a end date.
The id is a foreign key to the jobs table and the dates indicate the date and time that a certain job started and ended.

I need to do a report to count to each id, how many hours where made in the daily period (07:00 to 21:00) and on the night period (21:00 to 07:00).

If I have something like this:
BeginDate = 15-02-2008 08:00:00
EndDate = 16-02-2008 08:00:00

I need the result:
DayTime = 13
NightTime = 10
DayTime = 1

Total:
DayTime = 14
NightTime = 10

I really dont't see how I can do this, so if anyone can help me, I would be very apreciated!

Thank you
Paula

talleyrand
Starting Member

35 Posts

Posted - 2008-02-15 : 09:42:24

I think a general plan of attack is to group on the start day (truncate time to midnight) and then use a custom function to determine the amount of hours spent in day and probably a seperate but complimentary function for hours at night. I'll think about the actual grits of implementation during my meetings.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-15 : 09:50:27

Try this out. this should give you a start off function. I havent tested this.

CREATE FUNCTION CalcTime
(@FromDate datetime,
@Todate datetime
)
RETURNS @RESULTS Table
(
DayTime decimal(3,2),
NightTime decimal(3,2)
)
AS
BEGIN
DECLARE @RemTime decimal(3,2),@dayDiff decimal(3,2),@lastdiff decimal(3,2)


SELECT @RemTime= DATEDIFF(hh,@FromDate,DATEADD(d,1,DATEADD(d,DATEDIFF(d,0,@FromDate),0)))

INSERT INTO @RESULTS
SELECT @RemTime -3,3

SELECT @dayDiff=DATEDIFF(d,DATEADD(d,1,DATEADD(d,DATEDIFF(d,0,@FromDate),0)),DATEADD(d,DATEDIFF(d,0,@Todate),0))

WHILE @daydiff >0
BEGIN
INSERT INTO @RESULTS
SELECT 13,10

SELECT @daydiff=@daydiff-1
END

SELECT @lastdiff=DATEDIFF(hh,DATEADD(d,DATEDIFF(d,0,@Todate),0).@todate)

INSERT INTO @RESULTS
SELECT 7,@lastdiff-7
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-15 : 10:37:23

[code]-- Prepare sample data
DECLARE @Sample TABLE (ID INT, BeginDate DATETIME, EndDate DATETIME)

SET DATEFORMAT DMY

INSERT @Sample
SELECT 1, '15-02-2008 08:00:00', '16-02-2008 08:00:00'

-- Show the expected output
SELECT SUM(CASE WHEN v.Number >= 420 AND v.Number < 1260 THEN 1 ELSE 0 END) / 60.0 AS DayTime,
SUM(CASE WHEN v.Number >= 420 AND v.Number < 1260 THEN 0 ELSE 1 END) / 60.0 AS NightTime
FROM (
SELECT ID,
DATEDIFF(MINUTE, '00:00:00', BeginDate) % 1440 AS theBeginMinute,
DATEDIFF(MINUTE, '00:00:00', EndDate) % 1440 AS theEndMinute
FROM @Sample
WHERE DAY(BeginDate) = DAY(EndDate)

UNION ALL

SELECT ID,
DATEDIFF(MINUTE, '00:00:00', BeginDate) % 1440,
1440
FROM @Sample
WHERE DAY(BeginDate) < DAY(EndDate)

UNION ALL

SELECT ID,
0,
DATEDIFF(MINUTE, '00:00:00', EndDate) % 1440
FROM @Sample
WHERE DAY(BeginDate) < DAY(EndDate)
) AS d
INNER JOIN master..spt_values AS v ON v.Type = 'p'
WHERE v.Number >= d.theBeginMinute
AND v.Number < d.theEndMinute[/code]

E 12°55'05.25"
N 56°04'39.16"

paulafernandes
Starting Member

10 Posts

Posted - 2008-02-18 : 11:38:31

Hello!

Thank you everyone for all your answers. I'm sorry I didn't reply sooner, but only now I had time to pick up with this.

@visakh16:
I'm not familiar with function in SQL, but for what I understood, It's creating a temporary table where the results are inserted, right? (If not I'm sorry!!!) I'm not getting this working...
When I try some dates, the values return are not what expected.

@Peso:
I'm not sure what is "v.Number", so I replaced with the begin and end date, but the values returned doesn't make sense. For example to BeginDate = '18-02-2008 08:00' and EndDate = '19-02-2008 08:00' I get DayTime = 0.000000 and NightTime = 52.316666.

I'm sorry, but I have to ask for you more help because this was asked to me and I realize that is way above my knowledge of SQL, but I have to do it...

Thank you again
Paula

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-18 : 11:46:18

Why did you edit my code so that it will not work?
v.Number is just a number ranging from 0 to 1439 denoting all possible minutes during a day.
With your new sample data ('18-02-2008 08:00' and '19-02-2008 08:00'), I get DayTime = 14 and NightTime = 10

This is the part you need to execute

SELECT		SUM(CASE WHEN v.Number >= 420 AND v.Number < 1260 THEN 1 ELSE 0 END) / 60.0 AS DayTime,
		SUM(CASE WHEN v.Number >= 420 AND v.Number < 1260 THEN 0 ELSE 1 END) / 60.0 AS NightTime
FROM		(
			SELECT	ID,
				DATEDIFF(MINUTE, '00:00:00', BeginDate) % 1440 AS theBeginMinute,
				DATEDIFF(MINUTE, '00:00:00', EndDate) % 1440 AS theEndMinute
			FROM	YourTableNameHere
			WHERE	DAY(BeginDate) = DAY(EndDate)

			UNION ALL

			SELECT	ID,
				DATEDIFF(MINUTE, '00:00:00', BeginDate) % 1440,
				1440
			FROM	YourTableNameHere
			WHERE	DAY(BeginDate) < DAY(EndDate)

			UNION ALL

			SELECT	ID,
				0,
				DATEDIFF(MINUTE, '00:00:00', EndDate) % 1440
			FROM	YourTableNameHere
			WHERE	DAY(BeginDate) < DAY(EndDate)
		) AS d
INNER JOIN	master..spt_values AS v ON v.Type = 'p'
WHERE		v.Number >= d.theBeginMinute
		AND v.Number < d.theEndMinute

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-18 : 11:50:15

What is the result you are getting? its actually a table valued function that returns the tbale of results given two dates:-

CREATE FUNCTION CalcTime
(@FromDate datetime,
@Todate datetime
)
RETURNS @RESULTS Table
(
DayTime decimal(3,2),
NightTime decimal(3,2)
)
AS
BEGIN
DECLARE @RemTime decimal(3,2),@dayDiff decimal(3,2),@lastdiff decimal(3,2)


SELECT @RemTime= DATEDIFF(hh,@FromDate,DATEADD(d,1,DATEADD(d,DATEDIFF(d,0,@FromDate),0)))

INSERT INTO @RESULTS
SELECT @RemTime -3,3

SELECT @dayDiff=DATEDIFF(d,DATEADD(d,1,DATEADD(d,DATEDIFF(d,0,@FromDate),0)),DATEADD(d,DATEDIFF(d,0,@Todate),0))

WHILE @daydiff >0
BEGIN
INSERT INTO @RESULTS
SELECT 13,10

SELECT @daydiff=@daydiff-1
END

SELECT @lastdiff=DATEDIFF(hh,DATEADD(d,DATEDIFF(d,0,@Todate),0).@todate)

INSERT INTO @RESULTS
SELECT 7,@lastdiff-7
RETURN
END
GO

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-18 : 12:29:38

Peso:

I noticed that you werent taking into account the number of days between the beginning day and the ending day in your calculation. I altered your solution to work correctly with a multi-day data set:

-- Prepare sample data
DECLARE @Sample TABLE
    (
      ID INT,
      BeginDate DATETIME,
      EndDate DATETIME
    )

SET DATEFORMAT DMY

INSERT  @Sample
        SELECT  1, '15-02-2008 08:00:00', '16-02-2008 08:00:00' UNION
        SELECT  2, '15-02-2008 08:00:00', '17-02-2008 08:00:00'

-- Show the expected output
SELECT  d.ID,
        ( SUM(CASE WHEN v.Number >= 420
                        AND v.Number < 1260 THEN 1
                   ELSE 0
              END) / 60.0 ) + ( ( DATEDIFF(DAY, s.BeginDate, s.EndDate) - 1 )
                                * 14 ) AS DayTime,
        ( SUM(CASE WHEN v.Number >= 420
                        AND v.Number < 1260 THEN 0
                   ELSE 1
              END) / 60.0 ) + ( ( DATEDIFF(DAY, s.BeginDate, s.EndDate) - 1 )
                                * 10 ) AS NightTime
FROM    ( SELECT    ID,
                    DATEDIFF(MINUTE, '00:00:00', BeginDate) % 1440 AS theBeginMinute,
                    DATEDIFF(MINUTE, '00:00:00', EndDate) % 1440 AS theEndMinute
          FROM      @Sample
          WHERE     DAY(BeginDate) = DAY(EndDate)
          UNION ALL
          SELECT    ID,
                    DATEDIFF(MINUTE, '00:00:00', BeginDate) % 1440,
                    1440
          FROM      @Sample
          WHERE     DAY(BeginDate) < DAY(EndDate)
          UNION ALL
          SELECT    ID,
                    0,
                    DATEDIFF(MINUTE, '00:00:00', EndDate) % 1440
          FROM      @Sample
          WHERE     DAY(BeginDate) < DAY(EndDate)
        ) AS d
        INNER JOIN @Sample s ON d.ID = s.ID
        INNER JOIN master..spt_values AS v ON v.Type = 'p'
WHERE   v.Number >= d.theBeginMinute
        AND v.Number < d.theEndMinute
GROUP BY d.ID,
        s.BeginDate,
        s.EndDate

paulafernandes
Starting Member

10 Posts

Posted - 2008-02-18 : 13:17:27

Hello Peso

First I have to admit that I didn't knew what was master..spt_values... Sorry. I went looking and now (I think) I understand.
Actually, since I didn't knew that table, I was replacing all the mentions to it to tables of my database. Of course It didn't worked.

Now I know how to calculate the values to two given dates. But how I do it to all my table containing the dates?

I really hope I'm not asking a very stupid thing, but if I am feel free to call me dumb. It just that I feel like I'm walking on a new way, and I feel completly in the dark...

Thank you
Paula

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-18 : 14:37:21

Just use Peso's suggestion posted 02/18/2008 : 11:46:18. That will run on your entire table.

paulafernandes
Starting Member

10 Posts

Posted - 2008-02-18 : 17:19:21

Hello Peso and jdaman

I've already tried Peso's solution but I got only result: DayTime = 12423.816666 and NightTime = 1804.333333

But analyzing jdaman solution's I've found the solution by mixing the two sugestions and It's now working!

For the record:

SELECT	d.Id_JobSignalling,	SUM(CASE WHEN v.Number >= 420 AND v.Number < 1260 THEN 1 ELSE 0 END) / 60.0 AS DayTime,
		SUM(CASE WHEN v.Number >= 420 AND v.Number < 1260 THEN 0 ELSE 1 END) / 60.0 AS NightTime
FROM		(
			SELECT	Id_JobSignalling,
				DATEDIFF(MINUTE, '00:00:00', BeginDate) % 1440 AS theBeginMinute,
				DATEDIFF(MINUTE, '00:00:00', EndDate) % 1440 AS theEndMinute
			FROM	[Op.JobSignalling]
			WHERE	DAY(BeginDate) = DAY(EndDate)

			UNION ALL

			SELECT	Id_JobSignalling,
				DATEDIFF(MINUTE, '00:00:00', BeginDate) % 1440,
				1440
			FROM	[Op.JobSignalling]
			WHERE	DAY(BeginDate) < DAY(EndDate)

			UNION ALL

			SELECT	Id_JobSignalling,
				0,
				DATEDIFF(MINUTE, '00:00:00', EndDate) % 1440
			FROM	[Op.JobSignalling]
			WHERE	DAY(BeginDate) < DAY(EndDate)
		) AS d
        INNER JOIN [Op.JobSignalling] s ON d.Id_JobSignalling = s.Id_JobSignalling
INNER JOIN	master..spt_values AS v ON v.Type = 'p'
WHERE		v.Number >= d.theBeginMinute
		AND v.Number < d.theEndMinute
GROUP BY d.Id_JobSignalling,
        s.BeginDate,
        s.EndDate

Thank you both!
Thank's also to visakh16, I'm sorry I didn't studied properly your sugestion.

Paula

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources