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)
 Calculting time a document is with third party

Author  Topic 

NickC
Yak Posting Veteran

68 Posts

Posted - 2012-08-10 : 06:36:18
Sorry I keep posting! New Role and clearly struggling!!!

I'm using SQL 2000 DB but can use 2008 by taking the data from 2000 into 2008 prior to running a query and think what I need might be easier.

I have an audit table that needs to be analysed to calculate the time that a document is with us, and a time it is with a third party.

The audit table is poor, it has no ID, timestamps which can be duplicates and status messages (the duplicate occur when a status automaticaly changes due to another one).

These statuses are text based i.e.

(Workgroup\NCampbell) Changed Owner: xyz
(Workgroup\SQLTeam) Change Status: Help

They have over 180 Status messages too!

I did try settign a Y next to each status which was a third party and then doing a datetime to calculate the minutes between statuses as a field then summing the Y's to give a total however it now seems thats some status messages can be ours and their depending on status messages prior so now I ahve a new idea but no idea how to implement

I want to calculate a start time for status lets call the status (gone to third party) and and end time (Received back by query)

then calculate all these instances for each document type

so if theres 3 times it starts and 3 times it stops then do a daet datediff 3 times eahc start and stop then keep sum as a total

this is the table for a single document:
This is a temp table I created to remove the fluff to status and add a unique id (Using Identity (INT 1,1) which sin't ideal) and document_type

With this example (a single document_Id)

I want it to start when it says 'Changed Owner to : Uw Support' and stop when it says 'Changed Status to : Query - Refer to UW'

it can go back and fourth more than once tho so need to keep the total.

I got about as far as this

DECLARE @Status			VARCHAR(MAX)
DECLARE @StartTime DateTime
DECLARE @StopTime DateTime
DECLARE @DocumentID INT
DECLARE @RunningTotal INT
DECLARE @LineCount INT

SET @LineCount = 0

WHILE Status <> 'Changed Status to : COMPLETE' OR 'Changed Status to : COMPLETED'
'


When I eralised they want holidays nott o eb counted as well as this period, we do have a table called tbl_calender which has every date in it though! and a IsHOliday box with a 1 or a 0 in it so I could use that but no idea how id even approach any of this.

ANY HELP WOULD BE AWESOME, if not I understand its not a quick 2 second query :(

NickC
Yak Posting Veteran

68 Posts

Posted - 2012-08-10 : 06:37:02
I've never used variables either so have no idea, im more of your case subquery at best kinda guy :(
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-10 : 07:00:12
This takes more time than I have available, but my thought process would be as follows:

1. Identify all the rows that will participate in the calculations (i.e., rows that have the "Changed Owner/Changed Status" as you indicated) and may be even insert them into a new table.

2. If the identity column is not already ordered by time, create another column to order it by ID. You could use the time stamp column to do this, but depending on the amount of data to be processed adding a new column may be more efficient.

3. Use the "Islands and Gaps" method that Itzik Ben-Gan has described in this article. It is part of a book, but for some reason, that one chapter in the book is available for free: http://www.manning.com/nielsen/SampleChapter5.pdf

If you hit a wall, post the query along with DDLs for tables that someone can copy and paste into SSMS window and I am sure people on the forum would be able to help.
Go to Top of Page

NickC
Yak Posting Veteran

68 Posts

Posted - 2012-08-10 : 09:46:02
Thank Sunita - Still Struggling

I've created a function fo the holiday thing now so not an issue but struggling on the rest a little, ive created a test situation using DDL
[Code]
DROP TABLE #Temp

CREATE TABLE #Temp(
[doc_id] [int] NULL,
[timestamp] [datetime] NULL,
[notes] [varchar](1024) NULL
)

GO

INSERT INTO #Temp (doc_id, [timestamp], notes)
values (1, '2012-01-01', 'Started')
INSERT INTO #Temp (doc_id, [timestamp], notes)
values (1, '2012-02-01', 'abc')
INSERT INTO #Temp (doc_id, [timestamp], notes)
values (1, '2012-06-01', 'Query')
INSERT INTO #Temp (doc_id, [timestamp], notes)
values (1, '2012-07-01', 'xyz')
INSERT INTO #Temp (doc_id, [timestamp], notes)
values (1, '2012-08-01', 'Started')
INSERT INTO #Temp (doc_id, [timestamp], notes)
values (1, '2012-09-01', 'qwerty')
INSERT INTO #Temp (doc_id, [timestamp], notes)
values (1, '2012-10-01', 'Completed')
INSERT INTO #Temp (doc_id, [timestamp], notes)
values (2, '2012-01-01', 'Started')

SELECT * FROM #Temp[/Code]
Basically what I want to achieve is a fourth column called "third party time" and calculte the time that it is not in query this is indicated by having 'started' be the start and query be a 'stop time'

so in this example we'd calculate minute difference from row 1 to row 3, then stop then start again on row 6 (as its called started) then stop on completed, then repeat this process for every doc id

im sooo bad at explaining things, :/
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-10 : 10:01:03
See the query below. The where clause in the outer query determines the starting points and the where clauses in the inner query determine the ending period. I may not have those where clauses exactly right, but if you change them appropriately, this should work.
SELECT
a.*,
c.ThirdPartyTime
FROM
#Temp a
CROSS APPLY
(
SELECT TOP(1) DATEDIFF(dd,a.timestamp,b.Timestamp) AS ThirdPartyTime
FROM #Temp b
WHERE
b.[timestamp] > a.[timestamp]
AND b.Notes = 'Completed'
AND b.Notes IN ('Query','Completed')
ORDER BY
b.[timestamp]
) c
WHERE
a.Notes IN ('Started')
Go to Top of Page
   

- Advertisement -