Date Calculation not working - 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)

Date Calculation not working

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-09-04 : 23:07:17

In my trigger below my variables @Park1, @park2 and @Park3 are all coming out as zeros. Can you help me to see why. there are definitely values in the table for these columns.

--------------------------------------------

USE [Almouth]
GO
/****** Object: Trigger [dbo].[Fault_Parked_UPDATE] Script Date: 09/05/2010 03:53:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: <04/08/10>
-- Description: <Trigger to update the Duration field in calllog with the duration of a fault>
-- =============================================
Alter TRIGGER [dbo].[Fault_Parked_UPDATE] ON [dbo].[Detail]
FOR INSERT, UPDATE
AS
Declare @CalliD Varchar(8)
Declare @Park1Start datetime
Declare @Park1End datetime
Declare @Park2Start datetime
Declare @Park2End datetime
Declare @Park3Start datetime
Declare @Park3End datetime
Declare @Park1 INT
Declare @Park2 INT
Declare @Park3 INT
Declare @TotalParkedTime INT
Declare @FaultStart datetime
Declare @FaultEnd datetime
Declare @FaultTotal INT
Declare @FaultMinusParked INT

Select @callid=Callid FROM inserted WHERE
isdate(FaultStart)=1
and isdate(FaultEnd)=1

IF @Callid IS NOT NULL
Begin

Select @Park1Start = CASE WHEN ISDATE(@Park1Start)= 1 THEN Cast([ParkedDate1]+' '+[ParkedTime1] as datetime) ELSE CAST('1900-01-01 00:00:00' as DateTime) END
from detail
Where callid = @Callid

Select @Park1End = CASE WHEN ISDATE(@Park1End)= 1 THEN Cast([UnParkedDate1]+' '+[UnParkedTime1] as datetime) ELSE CAST('1900-01-01 00:00:00' as DateTime) END
from detail
Where callid = @Callid

Select @Park2Start = CASE WHEN ISDATE(@Park2Start)= 1 THEN Cast([ParkedDate2]+' '+[ParkedTime2] as datetime) ELSE CAST('1900-01-01 00:00:00' as DateTime) END
from detail
Where callid = @Callid

Select @Park2End = CASE WHEN ISDATE(@Park2End)= 1 THEN Cast([UnParkedDate2]+' '+[UnParkedTime2] as datetime) ELSE CAST('1900-01-01 00:00:00' as DateTime) END
from detail
Where callid = @Callid

Select @Park3Start = CASE WHEN ISDATE(@Park3Start)= 1 THEN Cast([ParkedDate3]+' '+[ParkedTime3] as datetime) ELSE CAST('1900-01-01 00:00:00' as DateTime) END
from detail
Where callid = @Callid

Select @Park3End = CASE WHEN ISDATE(@Park3End)= 1 THEN Cast([UnParkedDate3]+' '+[UnParkedTime3] as datetime) ELSE CAST('1900-01-01 00:00:00' as DateTime) END
from detail
Where callid = @Callid

Select @Park1 = datediff(ss,@Park1Start ,@Park1End)/60

Select @Park2 = datediff(ss,@Park2Start ,@Park2End)/60

Select @Park3 = datediff(ss,@Park3Start ,@Park3End)/60

Select @TotalParkedTime = @Park1 + @Park2 + @Park3

Select @FaultStart = Cast([FaultStart]+' '+[FaultStartTime] as datetime)
from detail
Where callid = @Callid

Select @FaultEnd = Cast([FaultEnd]+' '+[FaultEndTime] as datetime)
from detail
Where callid = @Callid

Select @FaultTotal = datediff(ss,@FaultStart ,@FaultEnd)/60

Select @FaultMinusParked = @FaultTotal - @TotalParkedTime

UPDATE CallLog
set
Park1 = @Park1,
Park2 = @Park2,
Park3 = @Park3,
TotalParkedTime = @TotalParkedTime,
FaultMinusParked = @FaultMinusParked,
FaultTotal = @FaultTotal

Where calllog.CallID = @CallID

END

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-09-04 : 23:39:51

Fixed it by changing the code slightly as below

----------------------

USE [Almouth2]
GO
/****** Object: Trigger [dbo].[Fault_Parked_UPDATE] Script Date: 09/05/2010 04:38:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: <04/08/10>
-- Description: <Trigger to update the Duration field in calllog with the duration of a fault>
-- =============================================
CREATE TRIGGER [dbo].[Fault_Parked_UPDATE] ON [dbo].[Detail]
FOR INSERT, UPDATE
AS
Declare @CalliD Varchar(8)
Declare @Park1Start datetime
Declare @Park1End datetime
Declare @Park2Start datetime
Declare @Park2End datetime
Declare @Park3Start datetime
Declare @Park3End datetime
Declare @Park1 INT
Declare @Park2 INT
Declare @Park3 INT
Declare @TotalParkedTime INT
Declare @FaultStart datetime
Declare @FaultEnd datetime
Declare @FaultTotal INT
Declare @FaultMinusParked INT

Select @callid=Callid FROM inserted WHERE
isdate(FaultStart)=1
and isdate(FaultEnd)=1

IF @Callid IS NOT NULL
Begin

Select @Park1Start = Cast([ParkedDate1]+' '+[ParkedTime1] as datetime)
from detail
Where callid = @Callid

Select @Park1End = Cast([UnParkedDate1]+' '+[UnParkedTime1] as datetime)
from detail
Where callid = @Callid

Select @Park2Start = Cast([ParkedDate2]+' '+[ParkedTime2] as datetime)
from detail
Where callid = @Callid

Select @Park2End = Cast([UnParkedDate2]+' '+[UnParkedTime2] as datetime)
from detail
Where callid = @Callid

Select @Park3Start = Cast([ParkedDate3]+' '+[ParkedTime3] as datetime)
from detail
Where callid = @Callid

Select @Park3End = Cast([UnParkedDate3]+' '+[UnParkedTime3] as datetime)
from detail
Where callid = @Callid

Select @Park1 = CASE WHEN @Park1End > @Park1Start AND isdate(ParkedDate1)=1 THEN datediff(ss,@Park1Start ,@Park1End)/60 ELSE 0 END
from detail
Where callid = @Callid

Select @Park2 = CASE WHEN @Park2End > @Park2Start AND isdate(ParkedDate2)=1 THEN datediff(ss,@Park2Start ,@Park2End)/60 ELSE 0 END
from detail
Where callid = @Callid

Select @Park3 = CASE WHEN @Park3End > @Park3Start AND isdate(ParkedDate3)=1 THEN datediff(ss,@Park3Start ,@Park3End)/60 ELSE 0 END
from detail
Where callid = @Callid

Select @TotalParkedTime = @Park1 + @Park2 + @Park3

Select @FaultStart = Cast([FaultStart]+' '+[FaultStartTime] as datetime)
from detail
Where callid = @Callid

Select @FaultEnd = Cast([FaultEnd]+' '+[FaultEndTime] as datetime)
from detail
Where callid = @Callid

Select @FaultTotal = datediff(ss,@FaultStart ,@FaultEnd)/60

Select @FaultMinusParked = @FaultTotal - @TotalParkedTime

UPDATE CallLog
set
Park1 = @Park1,
Park2 = @Park2,
Park3 = @Park3,
TotalParkedTime = @TotalParkedTime,
FaultMinusParked = @FaultMinusParked,
FaultTotal = @FaultTotal

Where calllog.CallID = @CallID

END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-05 : 02:43:57

Hrm... What if you UPDATE or INSERT more than one record at a time?
Then your code above will not work...

Here is a rewrite that will always work, no matter how many records are update or inserted at any single time.

CREATE TRIGGER	dbo.Fault_Parked_UPDATE
ON		dbo.Detail
FOR		INSERT,
		UPDATE
AS

;WITH cteValid(CallID, Park1, Park2, Park3, FaultTotal)
AS (
	SELECT	CallID,
		CASE
			WHEN Park1End > Park1Start AND ISDATE(ParkedDate1) = 1 THEN DATEDIFF(SECOND, Park1Start, Park1End) / 60
			ELSE 0
		END AS Park1,
		CASE
			WHEN Park2End > Park2Start AND ISDATE(ParkedDate2) = 1 THEN DATEDIFF(SECOND, Park2Start, Park2End) / 60
			ELSE 0
		END AS Park2,
		CASE
			WHEN Park3End > Park3Start AND ISDATE(ParkedDate3) = 1 THEN DATEDIFF(SECONDS, Park3Start, Park3End) / 60
			ELSE 0
		END AS Park3,
		DATEDIFF(SECOND, FaultStart, FaultEnd) / 60 AS FaultTotal
	FROM	(
			SELECT	CallID,
				ParkedDate1,
				CAST(ParkedDate1 + ' ' + ParkedTime1 AS DATETIME) AS Park1Start,
				CAST(UnParkedDate1 + ' ' + UnParkedTime1 AS DATETIME) AS Park1End,
				ParkedDate2,
				CAST(ParkedDate2 + ' ' + ParkedTime2 AS DATETIME) AS Park2Start,
				CAST(UnParkedDate2 + ' ' + UnParkedTime2 AS DATETIME) AS Park2End,
				ParkedDate3,
				CAST(ParkedDate3 + ' ' + ParkedTime3 AS DATETIME) AS Park3Start,
				CAST(UnParkedDate3 + ' ' + UnParkedTime3 AS DATETIME) AS Park3End,
				CAST(FaultStart + ' ' + FaultStartTime AS DATETIME) AS FaultStart,
				CAST(FaultEnd + ' ' + FaultEndTime AS DATETIME) AS FaultEnd
			FROM	inserted
			WHERE	ISDATE(FaultStart) = 1
				AND ISDATE(FaultEnd) = 1
		) AS d
)
UPDATE		cl
SET		cl.Park1 = v.Park1,
		cl.Park2 = v.Park2,
		cl.Park3 = v.Park3,
		cl.TotalParkedTime = v.Park1 + v.Park2 + v.Park3,
		cl.FaultMinusParked = v.FaultTotal - v.Park1 - v.Park2 - v.Park3,
		cl.FaultTotal = v.FaultTotal
FROM		dbo.CallLog AS cl
INNER JOIN	cteValid AS v ON v.CallID = cl.CallID

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

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-09-05 : 07:38:51

Thanks a lot for that.
Does that mean it will update all relevant rows everytime?

Would that slow down the query over time?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-05 : 10:00:13

No. If there is only one record in most updates and inserts, the code above works on the single record.
But, if there sometimes are multiple records affected in the update or insert, the code above works with that too.

If CallID is the primary key, which I suspect, the final update in the trigger is very fast.

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

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-09-05 : 11:56:21

Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources