June 24, 2008 at 11:14 pm
Hi,
I need help regarding calculation the time difference:
my data looks like this:
FormCode STATE TIME
============================================================
3245Submitted To X 2008-06-06 12:13:25.450
3245Submitted To X 2008-06-06 12:13:30.433
3245Submitted To X 2008-06-06 12:13:38.513
3245X Rejected 2008-06-06 12:33:43.003
3245X Rejected 2008-06-06 12:42:02.843
3245Submitted To X 2008-06-06 13:11:27.267
3245Submitted To X 2008-06-06 13:12:39.940
3245Submitted To X 2008-06-06 13:15:16.630
3245X Rejected 2008-06-06 14:45:44.190
3245X Rejected 2008-06-07 19:24:07.277
3245X Rejected 2008-06-07 19:34:00.193
3245X Rejected 2008-06-07 19:34:00.210
3245X Rejected 2008-06-07 19:34:25.977
3245X Rejected 2008-06-07 19:37:01.697
3245X Rejectedd 2008-06-07 19:37:01.760
3245Submitted To X 2008-06-07 19:37:37.743
3245Submitted To X 2008-06-07 19:38:26.120
3245Submitted To X 2008-06-07 19:38:39.590
3245X Rejected 2008-06-07 20:37:02.140
3245X Rejected 2008-06-09 10:28:30.100
3245X Rejected 2008-06-09 10:53:17.840
3245X Rejected 2008-06-09 10:54:23.137
3245X Rejected 2008-06-09 10:54:23.183
3245Submitted To X 2008-06-09 10:55:09.767
3245Submitted To X 2008-06-09 10:55:20.060
3245Submitted To X 2008-06-09 10:55:52.580
3245Submitted To X 2008-06-09 11:21:45.257
3245Submitted To X 2008-06-09 11:21:45.290
Now I want to calculate how much time the request (3245) is in "Submitted To X" state. and how much time the request is in "X Rejected" State.
The above sample is for only one request. third column represents the date and time. how to achieve this.
In our database data is very huge. If i write cursor it will take more time. Can anybody knows the solution please help me out.
Thanks in advance.
Regards
Venki
June 25, 2008 at 5:49 am
Dear Friend,
Could you explian the problem in detail?
I am not able to understand what time you want to calculate since same request is sumbitting multiple times..
June 25, 2008 at 6:31 am
Hi ,
In the above mentioned question.
Actual what i want is:
Request 3245 was " Submitted To X" at 2008-06-06 12:13:25.450 time.
How much time it is in "Submitted to X". and how much time it is "X Rejected" state.
Actually, what here happens is , first request will "submitted to X" after some time he will reject("X Rejected") and then again it will submit back to X. like that it will happens(like cyclic loop).
In that i need to calculate the time, how much time the request is in "Submit to X" and "X rejected" states respectively.
Please help me.
Thanks & Regards
Venki
June 25, 2008 at 6:57 am
But the data you mentioned clearly show that request is submitted three times without rejected...
3245Submitted To X2008-06-06 12:13:25.450
3245Submitted To X2008-06-06 12:13:30.433
3245Submitted To X2008-06-06 12:13:38.513
And then request is rejected..
3245X Rejected2008-06-06 12:33:43.003
3245X Rejected2008-06-06 12:42:02.843
So there is no sequence of Submission and Rejection.
Is it possible to submit the request again without being rejected? Please confirm..
June 25, 2008 at 8:10 am
I think you are looking for:
[font="Courier New"]SELECT FormCode, [State], Max([Time])-Min([Time]) AS TotalTime
FROM MyData
GROUP BY FormCode, [State][/font]
June 25, 2008 at 8:33 am
I take it that the process is in a certain state from the first record with that state until the first record with a different state or the end of the data. If that's the idea then maybe this is what you want
WITH curMax AS (
SELECT MAX([time]) AS MaxTime
FROM States
), curStates AS (
SELECT s1.FormCode,
s1.State AS State1,
s1.[time] as firsttime,
COALESCE(s2.State, 'EOD') AS State2,
MIN(COALESCE(s2.[time], curMax.MaxTime)) as secondtime
FROM states s1
LEFT OUTER JOIN states s2
ON s2.[time] > s1.[time]
AND s1.State <> s2.State
INNER JOIN curMax ON 1=1
GROUP BY s1.[time], s1.State, s1.FormCode, s2.State
), curTimes AS (
SELECT FormCode,
State1,
State2,
SecondTime,
MAX(DATEDIFF(ss, FirstTime, SecondTime)) AS StateTime
FROM curStates
GROUP BY FormCode, State1, State2, SecondTime
)
SELECT FormCode,
State1 AS [State],
SUM(StateTime) AS TotalTime
FROM curTimes
GROUP BY FormCode, State1
This gives you the results:
FormCode State TotalTime
3245 Submitted to X 12036
3245 X Rejected 244064
June 25, 2008 at 8:40 am
Here's one possible solution:
;with
CTE1 (Stat, Time) as -- Base Data
(select 'Submitted To X','2008-06-06 12:13:25.450' union all
select 'Submitted To X','2008-06-06 12:13:30.433' union all
select 'Submitted To X','2008-06-06 12:13:38.513' union all
select 'X Rejected','2008-06-06 12:33:43.003' union all
select 'X Rejected','2008-06-06 12:42:02.843' union all
select 'Submitted To X','2008-06-06 13:11:27.267' union all
select 'Submitted To X','2008-06-06 13:12:39.940' union all
select 'Submitted To X','2008-06-06 13:15:16.630' union all
select 'X Rejected','2008-06-06 14:45:44.190' union all
select 'X Rejected','2008-06-07 19:24:07.277' union all
select 'X Rejected','2008-06-07 19:34:00.193' union all
select 'X Rejected','2008-06-07 19:34:00.210' union all
select 'X Rejected','2008-06-07 19:34:25.977' union all
select 'X Rejected','2008-06-07 19:37:01.697' union all
select 'X Rejected','2008-06-07 19:37:01.760' union all
select 'Submitted To X','2008-06-07 19:37:37.743' union all
select 'Submitted To X','2008-06-07 19:38:26.120' union all
select 'Submitted To X','2008-06-07 19:38:39.590' union all
select 'X Rejected','2008-06-07 20:37:02.140' union all
select 'X Rejected','2008-06-09 10:28:30.100' union all
select 'X Rejected','2008-06-09 10:53:17.840' union all
select 'X Rejected','2008-06-09 10:54:23.137' union all
select 'X Rejected','2008-06-09 10:54:23.183' union all
select 'Submitted To X','2008-06-09 10:55:09.767' union all
select 'Submitted To X','2008-06-09 10:55:20.060' union all
select 'Submitted To X','2008-06-09 10:55:52.580' union all
select 'Submitted To X','2008-06-09 11:21:45.257' union all
select 'Submitted To X','2008-06-09 11:21:45.290'),
CTE2 (Stat, Time, Later) as -- Change of Status
(select c1.Stat, c1.Time, c2.Time
from CTE1 C1
inner join CTE1 C2
on c1.stat != c2.stat
and c1.time < c2.time),
CTE3 (Stat, Time, Changed) as -- First Status Change
(select Stat, time, min(Later)
from CTE2
group by Stat, time),
CTE4 (Stat, Time, Changed) as -- Eliminate Duplicate Statuses
(select Stat, min(Time), Changed
from CTE3
group by Stat, changed)
select *,
datediff(minute, Time, Changed) as Minutes -- Time in Status (Minutes)
from cte4
Results:
Stat Time Changed Minutes
Submitted To X 2008-06-06 12:13:25.450 2008-06-06 12:33:43.003 20
X Rejected 2008-06-06 12:33:43.003 2008-06-06 13:11:27.267 38
Submitted To X 2008-06-06 13:11:27.267 2008-06-06 14:45:44.190 94
X Rejected 2008-06-06 14:45:44.190 2008-06-07 19:37:37.743 1732
Submitted To X 2008-06-07 19:37:37.743 2008-06-07 20:37:02.140 60
X Rejected 2008-06-07 20:37:02.140 2008-06-09 10:55:09.767 2298
Instead of CTEs, you might be better of using temp tables, in which case, change each CTE in sequence into a temp table insert. If you do that, you can add indexes to the temp tables and might possibly get better performance. You'll have to test it to see which solution gets the best results.
On a large original table, CTEs and temp tables will both end up in tempdb, so make sure it has enough room to handle this without fragmenting like crazy. Too small a tempdb will definitely result in poor performance on this kind of thing.
You might also need to set up a calendar table to create rules for handling what happens when something is submitted late in the day and rejected early the next day, if that's applicable for what you're doing. (Not fair to blame someone for "taking a long time" if it was sent to him one minute before he leaves for the day and he handled it one minute after he gets back the following day, just because the number of minutes between those was huge. If that kind of thing is applicable here.)
Of course, in your final code, you'll need to group by your ID number. I left that out of this sample, because it was all the same in your example.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 25, 2008 at 11:48 am
How about :
DECLARE @t table (form_code int, state varchar(20), time_stamp datetime)
INSERT INTO @t (form_code, state, time_stamp)
SELECT 3245, 'Submitted To X','2008-06-06 12:13:25.450' UNION ALL
SELECT 3245, 'Submitted To X','2008-06-06 12:13:30.433' UNION ALL
SELECT 3245, 'Submitted To X','2008-06-06 12:13:38.513' UNION ALL
SELECT 3245, 'X Rejected','2008-06-06 12:33:43.003' UNION ALL
SELECT 3245, 'X Rejected','2008-06-06 12:42:02.843' UNION ALL
SELECT 3245, 'Submitted To X','2008-06-06 13:11:27.267' UNION ALL
SELECT 3245, 'Submitted To X','2008-06-06 13:12:39.940' UNION ALL
SELECT 3245, 'Submitted To X','2008-06-06 13:15:16.630' UNION ALL
SELECT 3245, 'X Rejected','2008-06-06 14:45:44.190' UNION ALL
SELECT 3245, 'X Rejected','2008-06-07 19:24:07.277' UNION ALL
SELECT 3245, 'X Rejected','2008-06-07 19:34:00.193' UNION ALL
SELECT 3245, 'X Rejected','2008-06-07 19:34:00.210' UNION ALL
SELECT 3245, 'X Rejected','2008-06-07 19:34:25.977' UNION ALL
SELECT 3245, 'X Rejected','2008-06-07 19:37:01.697' UNION ALL
SELECT 3245, 'X Rejected','2008-06-07 19:37:01.760' UNION ALL
SELECT 3245, 'Submitted To X','2008-06-07 19:37:37.743' UNION ALL
SELECT 3245, 'Submitted To X','2008-06-07 19:38:26.120' UNION ALL
SELECT 3245, 'Submitted To X','2008-06-07 19:38:39.590' UNION ALL
SELECT 3245, 'X Rejected','2008-06-07 20:37:02.140' UNION ALL
SELECT 3245, 'X Rejected','2008-06-09 10:28:30.100' UNION ALL
SELECT 3245, 'X Rejected','2008-06-09 10:53:17.840' UNION ALL
SELECT 3245, 'X Rejected','2008-06-09 10:54:23.137' UNION ALL
SELECT 3245, 'X Rejected','2008-06-09 10:54:23.183' UNION ALL
SELECT 3245, 'Submitted To X','2008-06-09 10:55:09.767' UNION ALL
SELECT 3245, 'Submitted To X','2008-06-09 10:55:20.060' UNION ALL
SELECT 3245, 'Submitted To X','2008-06-09 10:55:52.580' UNION ALL
SELECT 3245, 'Submitted To X','2008-06-09 11:21:45.257' UNION ALL
SELECT 3245, 'Submitted To X','2008-06-09 11:21:45.290'
---================
--- Actual query
---================
;WITH cte
AS
(SELECT form_code, state, time_stamp,
ROW_NUMBER() OVER (ORDER BY time_stamp) - RANK() OVER (PARTITION BY state ORDER BY time_stamp) diff
FROM @t
),
CteDiffs
AS(
SELECT form_code, state, diff as grp, DATEDIFF(ms, MIN(time_stamp),MAX(time_stamp)) time_spent
FROM cte
GROUP BY form_code, state, diff
)
SELECT form_code, SUM(CASE WHEN state = 'Submitted To X' THEN time_spent ELSE 0 END) AS Submitted_to_X_ms
, SUM(CASE WHEN STATE = 'X Rejected' THEN time_spent ELSE 0 END) AS X_Rejected_ms
FROM CTEDiffs
GROUP BY form_code
* Noel
June 26, 2008 at 12:11 am
Yes the request can submit more than one time. Here thing is while saving the record itself one entry will into the database.
how many times he saves the record means that many times it will come.
rejection also the same.
regards
Venki
June 26, 2008 at 2:15 am
Your answer is correct.
But Common Table Expressions (CTE) are not working in SQL server 2000. We are using SQL server 2000 database.
Please help me how to achieve the same in SQL server 2000.
Thanks & Regards
Venki
June 26, 2008 at 8:40 pm
So... why did you post in the 2005 forum or at least let us know in the beginning that you were using 2000?
About the only way to do this in 2000 is to either move the data into a temp table with a correct IDENTITY column or use something like in the following URL...
http://qa.sqlservercentral.com/articles/Advanced+Querying/61716/
--Jeff Moden
June 27, 2008 at 7:47 pm
Here's a simple solution that works on SS-2000. If this represents a large table, make sure the TIME column is indexed descending. Pull out the derived table and look at it by itself (uncomment the extra fields) and you will see what is happening.
declare @Table table(
FormCode int,
State varchar(16),
Time datetime
);
insert @Table (FormCode, State, Time)
select 3245, 'Submitted To X', '2008-06-06 12:13:25.450' union all
select 3245, 'Submitted To X', '2008-06-06 12:13:30.433' union all
select 3245, 'Submitted To X', '2008-06-06 12:13:38.513' union all
select 3245, 'X Rejected', '2008-06-06 12:33:43.003' union all
select 3245, 'X Rejected', '2008-06-06 12:42:02.843' union all
select 3245, 'Submitted To X', '2008-06-06 13:11:27.267' union all
select 3245, 'Submitted To X', '2008-06-06 13:12:39.940' union all
select 3245, 'Submitted To X', '2008-06-06 13:15:16.630' union all
select 3245, 'X Rejected', '2008-06-06 14:45:44.190' union all
select 3245, 'X Rejected', '2008-06-07 19:24:07.277' union all
select 3245, 'X Rejected', '2008-06-07 19:34:00.193' union all
select 3245, 'X Rejected', '2008-06-07 19:34:00.210' union all
select 3245, 'X Rejected', '2008-06-07 19:34:25.977' union all
select 3245, 'X Rejected', '2008-06-07 19:37:01.697' union all
select 3245, 'X Rejected', '2008-06-07 19:37:01.760' union all
select 3245, 'Submitted To X', '2008-06-07 19:37:37.743' union all
select 3245, 'Submitted To X', '2008-06-07 19:38:26.120' union all
select 3245, 'Submitted To X', '2008-06-07 19:38:39.590' union all
select 3245, 'X Rejected', '2008-06-07 20:37:02.140' union all
select 3245, 'X Rejected', '2008-06-09 10:28:30.100' union all
select 3245, 'X Rejected', '2008-06-09 10:53:17.840' union all
select 3245, 'X Rejected', '2008-06-09 10:54:23.137' union all
select 3245, 'X Rejected', '2008-06-09 10:54:23.183' union all
select 3245, 'Submitted To X', '2008-06-09 10:55:09.767' union all
select 3245, 'Submitted To X', '2008-06-09 10:55:20.060' union all
select 3245, 'Submitted To X', '2008-06-09 10:55:52.580' union all
select 3245, 'Submitted To X', '2008-06-09 11:21:45.257' union all
select 3245, 'Submitted To X', '2008-06-09 11:21:45.290';
select FormCode, State, Sum( TimeSpent ) / 1000 as SecondsSpent, Sum( TimeSpent ) / 60000 as MinutesSpent
from (
select t1.FormCode, t1.State, --t1.Time as Start, t2.Time as Finish,
DateDiff( ms, t1.Time, t2.Time ) as TimeSpent
from @Table t1
left join @Table t2
on t1.Time < t2.Time
and t2.Time = (
select Min( t3.Time )
from @Table t3
where t3.Time > t1.Time
)
) x
group by FormCode, State;
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
June 30, 2008 at 8:38 am
venki (6/26/2008)
Your answer is correct.But Common Table Expressions (CTE) are not working in SQL server 2000. We are using SQL server 2000 database.
Please help me how to achieve the same in SQL server 2000.
Thanks & Regards
Venki
Take the CTEs, translate them to temp tables. Insert sequentially, in the same pattern as the CTEs, and your final result will be the same as if it were CTEs. (Also, you posted this in an SQL 2005 forum. The SQL 2000 forum is just a tiny bit further down the page. No big deal, but I assumed from the forum that you had 2005.)
create table #CTE1 ( -- Base Data
Stat varchar(100),
Time datetime)
create table #CTE2 ( -- Change of Status
Stat varchar(100),
Time datetime,
Later datetime)
create table #CTE3 ( -- First Status Change
Stat varchar(100),
Time datetime,
Changed datetime)
create table #CTE4 ( -- Eliminate Duplicate Statuses
Stat varchar(100),
Time datetime,
Changed datetime)
insert into #CTE1 (Stat, Time)
select 'Submitted To X','2008-06-06 12:13:25.450' union all
select 'Submitted To X','2008-06-06 12:13:30.433' union all
select 'Submitted To X','2008-06-06 12:13:38.513' union all
select 'X Rejected','2008-06-06 12:33:43.003' union all
select 'X Rejected','2008-06-06 12:42:02.843' union all
select 'Submitted To X','2008-06-06 13:11:27.267' union all
select 'Submitted To X','2008-06-06 13:12:39.940' union all
select 'Submitted To X','2008-06-06 13:15:16.630' union all
select 'X Rejected','2008-06-06 14:45:44.190' union all
select 'X Rejected','2008-06-07 19:24:07.277' union all
select 'X Rejected','2008-06-07 19:34:00.193' union all
select 'X Rejected','2008-06-07 19:34:00.210' union all
select 'X Rejected','2008-06-07 19:34:25.977' union all
select 'X Rejected','2008-06-07 19:37:01.697' union all
select 'X Rejected','2008-06-07 19:37:01.760' union all
select 'Submitted To X','2008-06-07 19:37:37.743' union all
select 'Submitted To X','2008-06-07 19:38:26.120' union all
select 'Submitted To X','2008-06-07 19:38:39.590' union all
select 'X Rejected','2008-06-07 20:37:02.140' union all
select 'X Rejected','2008-06-09 10:28:30.100' union all
select 'X Rejected','2008-06-09 10:53:17.840' union all
select 'X Rejected','2008-06-09 10:54:23.137' union all
select 'X Rejected','2008-06-09 10:54:23.183' union all
select 'Submitted To X','2008-06-09 10:55:09.767' union all
select 'Submitted To X','2008-06-09 10:55:20.060' union all
select 'Submitted To X','2008-06-09 10:55:52.580' union all
select 'Submitted To X','2008-06-09 11:21:45.257' union all
select 'Submitted To X','2008-06-09 11:21:45.290'
insert into #CTE2 (Stat, Time, Later)
select c1.Stat, c1.Time, c2.Time
from #CTE1 C1
inner join #CTE1 C2
on c1.stat != c2.stat
and c1.time < c2.time
insert into #CTE3 (Stat, Time, Changed)
select Stat, time, min(Later)
from #CTE2
group by Stat, time
insert into #CTE4 (Stat, Time, Changed)
select Stat, min(Time), Changed
from #CTE3
group by Stat, changed
select *,
datediff(minute, Time, Changed) as Minutes -- Time in Status (Minutes)
from #cte4
Okay, I'd never actually name temp tables "#CTE1", but I'd also never name CTEs that way in production code. Go with whatever naming convention your standards call for. Also, the layout isn't my usual, because it's still laid out as if it were CTEs instead of my usual for tables. But the code works in 2005 or 2000. As mentioned in my first post, you can even add indexes to the temp tables if you so desire.
The first temp table, like the first CTE in the other post, should be left out. CTE2 should be populated from your real table. I just have it in there because I need it to run the rest of the code, since I don't have your real table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply