SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Insert new row if end date is the next day
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kacki
Starting Member

2 Posts

Posted - 08/19/2013 :  11:46:59  Show Profile  Reply with Quote
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



James K
Flowing Fount of Yak Knowledge

3578 Posts

Posted - 08/19/2013 :  12:23:07  Show Profile  Reply with Quote
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





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 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;
Go to Top of Page

Kacki
Starting Member

2 Posts

Posted - 08/19/2013 :  13:57:02  Show Profile  Reply with Quote
James,

Thanks for the reply. I will try to reverse engineer this a bit to as I would like to understand how it works, and how I can use it as a daily job or trigger that will run against this table. In your opinion, would creating and using a calander table be a better idea?

Thanks again.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3578 Posts

Posted - 08/19/2013 :  18:14:35  Show Profile  Reply with Quote
Yes, you should create a calendar table - that would be more efficient. You can create one like this, just replace the two dates in there with the max and min dates that you possibly could have in your business
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)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000