March 27, 2012 at 10:48 am
I have an application that handles a work queue. As a case is passed from status to the next throughout the process, I am tracking datetime 's for each status. For example
New
Assigned
In Process
In Suspense
Need Review
Complete
Finalized
Moving a case through the entire process could take months. I have a SP that adds a new record to the StatusChanges table if it doesn't exist or calculates the DATEDIFF between the StartDateTime and the EndDateTime as follows:
DECLARE @NewValue VARCHAR(50)
SET @NewValue = 'In Process' --this wil be one of the statuses listed above, i.e. New Record, Assigned, In Process, etc.
DECLARE @EndDateTime DATETIME
SET @EndDateTime = '2012-03-27 12:14:16.123'
IF NOT EXISTS (
SELECT ID
FROM #SC
WHERE ID = 1924)
BEGIN INSERT INTO #SC (ID, StatusName, StartDateTime)
VALUES (1924, @NewValue, GETDATE()) END
ELSE BEGIN
UPDATE #SC
SET @EndDateTime = EndDateTime = GETDATE(),
TotalTime = @EndDateTime - StartDateTime
WHERE ID = 1924
INSERT INTO #SC (ID, StatusName, StartDateTime)
VALUES (1924, @NewValue, GETDATE())
END
SELECT * FROM #SC
The problem is that I get records like this with the TotalTime prepended with 1900-01-01:
IF OBJECT_ID('TemDB..#SC') IS NOT NULL
DROP TABLE #SC
CREATE TABLE #SC (
[StatusChangeID] [int] IDENTITY(1,1) NOT NULL,
[ID] [int] NOT NULL,
[StatusName] [varchar](50) NOT NULL,
[StartDateTime] [datetime] NULL,
[EndDateTime] [datetime] NULL,
[TotalTime] [datetime] NULL
) ON [PRIMARY]
SET IDENTITY_INSERT #SC ON
INSERT INTO #SC (StatusChangeID, ID, StatusName, StartDateTime, EndDateTime, TotalTime)
SELECT 13029, 1924, 'Assigned', '2012-03-05 00:00:00', '2012-03-05 14:49:17.627', '1900-01-01 14:49:17.627'
SET IDENTITY_INSERT #SC OFF
SELECT * FROM #SC
Is there a way to get elapsed total time in a DATETIME-ish format that does not default to 1900-01-01? I've tried doing a DATEDIFF and subtracting out the 1900-01-01, but I get an arithmetic overflow error.
Any thoughts?
[font="Courier New"]_______________________________
Stephen Walsh[/font]
March 27, 2012 at 10:56 am
Please take a look at the following article and give us extra information.. I see that your requirement may be trivial, but with extra information, we can always give tested answer.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Also, please mask/remove any Dtabase names in your post. (I see that u have posted your database name)
March 27, 2012 at 11:18 am
The date 1900-01-01 is the zero date. If you were to subtract '2012-03-05 00:00:00.000' from '2012-03-05 00:00:00.000' you will get '1900-01-01 00:00:00.000'.
I think the best thing to ask right now is what are you looking at storing as elapsed time? You could use DATEDIFF to determine the number of seconds, or minutes, or some other set of time units between two times. It really depends on what you are reporting.
March 27, 2012 at 11:23 am
I'm wanting the option to report all of it if I wanted to. Normal reporting would probably be days, but as with all reporting, I'll need averages and it would be nice to see Average Turn Time = "2 Months and 3 days" or something similar.
[font="Courier New"]_______________________________
Stephen Walsh[/font]
March 27, 2012 at 11:33 am
What is the smallest unit of time you would report? Remember DATEDIFF(hours,'2012-03-05 01:00:00.000', '2012-03-05 01:59:00.000') would return 0 where DATEDIFF(hours,'2012-03-05 01:59:00.000', '2012-03-05 02:00:00.000') would return 1.
What you need to decide is how are you going to store the elapsed time between start and end date/time values. If the lowest reported value is hours, you would probably want to store the elpased time in minutes. If minutes, then seconds. You would then use this value in your computation to generate the value you actually report. How you do that, again depends on how you report it; is it just days, hours, minutes or is it months, days, hour, minutes.
March 27, 2012 at 11:55 am
That makes sense. That was the original thought, but I was trying to explore some other options.
Thanks for the help.
[font="Courier New"]_______________________________
Stephen Walsh[/font]
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply