calculate 2 fields to 24 hours and check overlappi

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)

calculate 2 fields to 24 hours and check overlappi

Author

Topic

michal
Starting Member

4 Posts

Posted - 2008-12-21 : 08:24:46

i have 2 fields - "date_from "
and "date_to"

12/30/1996 4:00:00 PM 12/30/1997 6:59:59 PM
12/30/1899 5:00:00 AM 12/30/1899 11:59:59 AM
12/30/1899 10:00:00 PM 12/30/1899 11:59:59 PM
12/30/1899 1:00:00 AM 12/30/1899 3:59:59 AM
12/30/1899 12:00:00 AM 12/30/1899 12:59:59 AM
12/30/1899 4:00:00 AM 12/30/1899 4:59:59 AM
12/30/1899 7:00:00 PM 12/30/1899 9:59:59 PM
12/30/1899 12:00:00 PM 12/30/1899 3:59:59 PM

i need to use only the time part ( not the date) and find out - if all this records are
calculated (the sum of the date difference) to 24 hours total ,
and i need also to check if there is no ambiguity /overlapping
between the records.
if it is 24 hours and no overlapping - then return true , otherwise - false.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-12-22 : 01:18:54

try this

DECLARE		@Table TABLE(FromDate DATETIME, ToDate DATETIME)
DECLARE		@Return BIT

SELECT		@Return = 0

INSERT INTO @Table
		SELECT '12/30/1996 4:00:00 PM', '12/30/1997 6:59:59 PM'
UNION ALL	SELECT '12/30/1899 5:00:00 AM', '12/30/1899 11:59:59 AM'
UNION ALL	SELECT '12/30/1899 10:00:00 PM', '12/30/1899 11:59:59 PM'
UNION ALL	SELECT '12/30/1899 1:00:00 AM', '12/30/1899 3:59:59 AM'
UNION ALL	SELECT '12/30/1899 12:00:00 AM', '12/30/1899 12:59:59 AM'
UNION ALL	SELECT '12/30/1899 4:00:00 AM', '12/30/1899 4:59:59 AM'
UNION ALL	SELECT '12/30/1899 7:00:00 PM', '12/30/1899 9:59:59 PM'
UNION ALL	SELECT '12/30/1899 12:00:00 PM', '12/30/1899 3:59:59 PM' 

SELECT		DISTINCT @Return = 1
FROM		(
					SELECT	SUM(DATEDIFF(HOUR, DATEADD(DAY, -DATEDIFF(DAY, 0, FromDate), FromDate), DATEADD(DAY, -DATEDIFF(DAY, 0, ToDate), DATEADD(SECOND, 1, ToDate)))) OVER () AS 'HrsSum'
							, ROW_NUMBER () OVER (PARTITION BY FromDate, ToDate ORDER BY FromDate, ToDate) AS 'Seq'
					FROM		@Table
			) A
WHERE		Seq = 1
		AND HrsSum = 24
			
SELECT		@Return

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 04:06:03

What is Books Online?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 04:16:01

[code]DECLARE @Sample TABLE
(
fromDate DATETIME,
toDate DATETIME
)

INSERT @Sample
SELECT '12/30/1996 4:00:00 PM', '12/30/1997 6:59:59 PM' UNION ALL
SELECT '12/30/1899 5:00:00 AM', '12/30/1899 11:59:59 AM' UNION ALL
SELECT '12/30/1899 10:00:00 PM', '12/30/1899 11:59:59 PM' UNION ALL
SELECT '12/30/1899 1:00:00 AM', '12/30/1899 3:59:59 AM' UNION ALL
SELECT '12/30/1899 12:00:00 AM', '12/30/1899 12:59:59 AM' UNION ALL
SELECT '12/30/1899 4:00:00 AM', '12/30/1899 4:59:59 AM' UNION ALL
SELECT '12/30/1899 7:00:00 PM', '12/30/1899 9:59:59 PM' UNION ALL
SELECT '12/30/1899 12:00:00 PM', '12/30/1899 3:59:59 PM'

DECLARE @a INT,
@b DATETIME

SELECT @a = SUM(DATEDIFF(SECOND, DATEADD(DAY, DATEDIFF(DAY, s1.fromDate, 0), s1.fromDate), DATEADD(DAY, DATEDIFF(DAY, s1.toDate, 0), s1.toDate)) + 1),
@b = MAX(s2.fromDate)
FROM @Sample AS s1
LEFT JOIN @Sample AS s2 ON DATEADD(DAY, DATEDIFF(DAY, s2.fromDate, 0), s2.fromDate) <= DATEADD(DAY, DATEDIFF(DAY, s1.toDate, 0), s1.toDate)
AND DATEADD(DAY, DATEDIFF(DAY, s2.toDate, 0), s2.toDate) >= DATEADD(DAY, DATEDIFF(DAY, s1.fromDate, 0), s1.fromDate)
AND s1.fromDate <> s2.fromDate

IF @a = 86400 AND @b IS NULL
PRINT 'True'
ELSE
PRINT 'False'[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 04:24:24

PeterNeo, try your suggection with this sample data only.

SELECT	'12/30/1899 12:00:00 AM', '12/30/1899 12:49:59 AM' UNION ALL
SELECT	'12/30/1899 12:55:00 AM', '12/30/1899 11:59:59 PM'

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources