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 StartTime01 01 8/10/2013 9:00:00 PM 8/11/2013 9:00:00 PMIf that happens I want this to happen:RowNumber AssetID StopTime StartTime01 01 8/10/2013 9:00:00 PM 8/11/2013 11:59:59 PM02 01 8/11/2013 12:00:00 AM 8/11/2013 9:00:00 PMThis 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
Here is an approximate example of how you can write a query to do what you are trying to do. If you have a calendar table or numbers table in your database, you wouldn't need the calendar cte that I am using.Also, I don't really like the idea of using 11:59:59 to mark the end of the day. In many cases a datetime value might have milli second (or microsecond or nanosecond portions if you use a higher precision data type). While one second may not matter in many applications, it does matter in others. Also, even if you don't care about a second, this can bring about logical errors.There is an elegant solution to that problem, but I will leave that out for now, for fear of boring you to death on things you probably don't care about.In any case, here is the query: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 startimeFROM calendar c CROSS JOIN #tmp tWHERE t.starttime > Dt AND t.stoptime < DATEADD(dd,1,dt)ORDER BY rownumber, assetid, stoptime DROP TABLE #tmp; |