Automatic get DateDiff in another column.

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 2008 Forums

Transact-SQL (2008)

Automatic get DateDiff in another column.

Author

Topic

MrCapuchino
Starting Member

9 Posts

Posted - 2010-07-12 : 17:42:00

Hello

I want to create a trigger or if someone has a better idea to do the following:

There are 2 columns for date in a table and 1 column called "difference" where the difference in days from the two dates will be added, from a webpage I made, the user inputs 1 date which is saved to the database.

Then later from another form the user inputs the other date.

How can I make that after that last date input it automatically calculates the difference and save it in the other column called "difference" in the same table.

Thanks in advance

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-07-12 : 17:51:46

Why save the difference between the dates when you can calculate it whenever you need it?

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-12 : 17:52:20

You can do that with a calculated column, but why bother calculating this ahead of time? Why not instead calculate it with DATEDIFF once you need to query it?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-12 : 17:59:04

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

MrCapuchino
Starting Member

9 Posts

Posted - 2010-07-12 : 18:32:06

Hi, this is because the process works like this:

An order comes in from the provider telling that the item will arrive at a certain date.
A person captures the order info including this arriving date.
When the item arrives this same person captures this new real arriving date. (The important thing is that the previous arriving date might be wrong and the item came later or before)
I want that when the person captures this new real date, the calculation applies automatically because in my webpage I have a Gridview that captures this values when clicked on a button. The table is more complicated than these values it includes many columns and other calculations, thats why I cannot query it.

I created a TRIGGER that for me seems right, but even if SQL Server does not mark any errors, when I update the realdate field nothing happens.
Here is the code:

CREATE TRIGGER dbo.DIFERENCIA
ON dbo.MaterialColocado
FOR UPDATE
AS
BEGIN
DECLARE @CUENTA AS INT
SET @CUENTA = 1
DECLARE @FECHAP AS DATE
DECLARE @FECHAC AS DATE
DECLARE @LIMITE AS INT
DECLARE @DIFERENCIA AS INT
DECLARE @ID AS INT
WHILE (@CUENTA <= @LIMITE)
SET @LIMITE = (SELECT COUNT(*) FROM MaterialColocado)
WHILE (@CUENTA <= @LIMITE)
BEGIN WITH TODOS AS (
SELECT id, fechapromesa, fechacumplida, ROW_NUMBER() OVER (ORDER BY ID) AS LINEA FROM MaterialColocado)
SELECT TOP 1 @FECHAP = (fechapromesa), @FECHAC = (fechacumplida), @ID = id FROM MaterialColocado WHERE LINEA = @CUENTA
SET @DIFERENCIA = DATEDIFF(day, @FECHAP, @FECHAC)
UPDATE MaterialColocado SET diferenciafechas = @DIFERENCIA WHERE ID = @ID
SET @CUENTA = @CUENTA + 1
END
END

any ideas? Thx

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-12 : 18:54:15

I would create two fields. OriginalDeliveryDate and another one called ActualDeliveryDate. There is a lot of benefit for this. OTD is one. Also when redoing your contracts with delivering company and/org if you use multiple companies you can use that as leverage for better pricing for delivery. You can tell the folks "listen you are delivering this many days late on this % of items. whereas comapny C is doing it much better. We need a new price" So user app for original price writes to OriginalDeliveryDate and final date will write to ActualDeliveryDate. If dates chaneg a lot you can create a totally different table that keep track of these dates every time delivery company changes dates. Put another field called username and you have auditing. Some companies like that because some users just fill in dates just for the sake of and then come back and clean it. Company might not want that kind of sloppy work.

imho

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

MrCapuchino
Starting Member

9 Posts

Posted - 2010-07-12 : 19:04:00

Hi
I have two fields OriginalDate and RealDate. But My Trigger is not working properly. Can you help me?

MrCapuchino
Starting Member

9 Posts

Posted - 2010-07-12 : 19:23:12

I modify my trigger and now it seems like it is trying to work but it throws me this error

Here is my Trigger code:
CREATE TRIGGER dbo.DIFERENCIA
ON dbo.MaterialColocado
AFTER UPDATE
AS
BEGIN
DECLARE @CUENTA AS INT
SET @CUENTA = 1
DECLARE @FECHAP AS DATE
DECLARE @FECHAC AS DATE
DECLARE @LIMITE AS INT
DECLARE @ID AS INT
SET @LIMITE = (SELECT COUNT(*) FROM MaterialColocado)
SELECT id, fechapromesa, fechacumplida, ROW_NUMBER() OVER (ORDER BY ID) AS LINEA FROM MaterialColocado
WHILE (@CUENTA <= @LIMITE)
SELECT @ID = id, @FECHAP = (fechapromesa), @FECHAC = (fechacumplida) FROM MaterialColocado WHERE LINEA = @CUENTA
UPDATE MaterialColocado SET diferenciafechas = DATEDIFF(day, @FECHAP, @FECHAC) WHERE ID = @ID
SET @CUENTA = @CUENTA + 1
END

Msg 245, Level 16, State 1, Procedure DIFERENCIA, Line 20
Conversion failed when converting the varchar value 'PARKER' to data type int.

('PARKER' is a value I use in one of my columns, but I never declared or used something that has to do with that column in my trigger)

Any ideas?

Thanks

MrCapuchino
Starting Member

9 Posts

Posted - 2010-07-12 : 21:22:23

Hey, I already solved my problem, what I did is that instead of a trigger I put my other column as "computed column" so its value is based on the two other columns.

ADD difference as DATEDIFF(day, date1, date2);

Maybe you already knew this but it might be good info to other people.

Thanks for your replies.

Good Bye

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources