quote:

---

Originally posted by Kacki

Hi all,

Hopefully I will be able to explain this in a way you can understand. I am using MSSQL 2005. I have a table with the columns StopTime and StartTime. I run a query that shows the time different between StopTime and StartTime grouped by day. If the StartTime is the next day, it throws off my query. I can run a SSIS package or trigger, what ever works, I just don't know how to do it. Lets say the table looks like this (notice StartTime is the next day):

RowNumber AssetID StopTime StartTime
01 01 8/10/2013 9:00:00 PM 8/11/2013 9:00:00 PM

If that happens I want this to happen:

RowNumber AssetID StopTime StartTime
01 01 8/10/2013 9:00:00 PM 8/11/2013 11:59:59 PM
02 01 8/11/2013 12:00:00 AM 8/11/2013 9:00:00 PM

This way, all my datediff calculations are grouped by the right day.

Also, could it work to span multilpe days, with a new row for every day between StopTime and StartTime?

Thank you in advance,
Rog

---

CREATE TABLE #tmp ( rownumber INT, assetid INT, stoptime DATETIME, starttime DATETIME)
INSERT INTO #tmp VALUES (1,1,'8/10/2013 9:00:00 PM','8/11/2013 9:00:00 PM')
INSERT INTO #tmp VALUES (1,2,'8/6/2013 5:00:00 AM','8/13/2013 3:00:00 PM')

;WITH calendar AS
(
	SELECT CAST('20130805' AS DATETIME) AS Dt
	UNION ALL SELECT DATEADD(dd, 1,Dt) FROM calendar WHERE Dt < '20130814'
)
SELECT rownumber,assetid,
	CASE 
		WHEN t.stoptime < Dt AND t.starttime > Dt THEN Dt
		WHEN t.stoptime <= DATEADD(dd,1,Dt) THEN stoptime
	END AS stoptime,
	CASE 
		WHEN t.starttime > DATEADD(dd,1,Dt) AND t.stoptime < DATEADD(dd,1,Dt) THEN DATEADD(mi,-1,DATEADD(dd,1,Dt))
		WHEN t.starttime >= Dt THEN starttime
	END AS startime
FROM
	calendar c
	CROSS JOIN #tmp t
WHERE
	t.starttime > Dt AND t.stoptime < DATEADD(dd,1,dt)
ORDER BY rownumber, assetid, stoptime
	
	
DROP TABLE #tmp;

CREATE TABLE dbo.Calendar
(	Dt DATETIME NOT NULL PRIMARY KEY CLUSTERED)

;WITH cte(Dt) AS
(
	SELECT CAST('20080101' AS DATETIME)
	UNION ALL
	SELECT DATEADD(dd,1,Dt) FROM cte
	WHERE Dt < '20151231'
)
INSERT INTO dbo.Calendar SELECT Dt FROM cte
OPTION (MAXRECURSION 0)