| Author |
 Topic  |
|
Novice2Sql
Starting Member
33 Posts |
 Posted - 10/29/2012 : 08:15:27
|
thanks but I still only want it to sum for status: B, J, K, 1 so would I do the following: ;With Temp
AS
(
SELECT t.Job_order,t.status,t.statusopened,DATEDIFF(SECOND,t.statusopened,t1.statusopened) AS timeinstatus
FROM #tmp t
where t.status in ('B', 'J','K', '1')
OUTER APPLY (SELECT TOP 1 statusopened
FROM #tmp
WHERE Job_order = t.Job_order
AND statusopened > t.statusopened
ORDER BY statusopened)t1
)
SELECT Job_order,Status,SUM(timeinstatus)
FROM Temp
GROUP BY Job_order,Status
|
 |
|
|
Novice2Sql
Starting Member
33 Posts |
Posted - 10/29/2012 : 08:19:45
|
Since I am only concern with B J K 1 status would I modify it to: ;With Temp
AS
(
SELECT t.Job_order,t.status,t.statusopened,DATEDIFF(SECOND,t.statusopened,t1.statusopened) AS timeinstatus
FROM #tmp t
where t.status in ('B', 'J','K', '1')
OUTER APPLY (SELECT TOP 1 statusopened
FROM #tmp
WHERE Job_order = t.Job_order
AND statusopened > t.statusopened
ORDER BY statusopened)t1
)
SELECT Job_order,Status,SUM(timeinstatus)
FROM Temp
GROUP BY Job_order,Status
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47147 Posts |
Posted - 10/29/2012 : 10:34:39
|
quote:
Originally posted by Novice2Sql
Since I am only concern with B J K 1 status would I modify it to: ;With Temp
AS
(
SELECT t.Job_order,t.status,t.statusopened,DATEDIFF(SECOND,t.statusopened,t1.statusopened) AS timeinstatus
FROM #tmp t
where t.status in ('B', 'J','K', '1')
OUTER APPLY (SELECT TOP 1 statusopened
FROM #tmp
WHERE Job_order = t.Job_order
AND status in ('B', 'J','K', '1')
AND statusopened > t.statusopened
ORDER BY statusopened)t1
)
SELECT Job_order,Status,SUM(timeinstatus)
FROM Temp
GROUP BY Job_order,Status
then i think you want this small tweak too!
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Novice2Sql
Starting Member
33 Posts |
Posted - 10/31/2012 : 04:16:41
|
Hello, I made the tweak and added one more criteria however it is givng me an error "Syntax error near Outer on line 8" not sure what it could be
;With Temp
AS
(
SELECT t.Job_order,t.status,t.statusopened,DATEDIFF(SECOND,t.statusopened,t1.statusopened) AS timeinstatus
FROM #tmp t
where t.status in ('B', 'J','K', '1')
AND (t.statusopened >= GETDATE() - 10)
OUTER APPLY (SELECT TOP 1 statusopened
FROM #tmp
WHERE Job_order = t.Job_order
AND status in ('B', 'J','K', '1')
AND statusopened > t.statusopened
AND (t.statusopened >= GETDATE() - 10)
ORDER BY statusopened)t1
)
SELECT Job_order,Status,SUM(timeinstatus)
FROM Temp
GROUP BY Job_order,Status |
|
|
|
Novice2Sql
Starting Member
33 Posts |
Posted - 10/31/2012 : 06:39:06
|
Can you do this without using a CTE? because I would need to format the data as the previous display I stated in the beginning
Thanks, |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/31/2012 : 06:58:07
|
OUTER APPLY is sort of a like a LEFT JOIN, so that section needs to go in the FROM clause - you have it in the WHERE clause and that is what the syntax error is about.;WITH Temp
AS
(
SELECT t.Job_order,
t.status,
t.statusopened,
DATEDIFF(SECOND, t.statusopened, t1.statusopened) AS timeinstatus
FROM #tmp t
OUTER APPLY (
SELECT TOP 1 statusopened
FROM #tmp
WHERE Job_order = t.Job_order
AND STATUS IN ('B', 'J', 'K', '1')
AND statusopened > t.statusopened
AND (t.statusopened >= GETDATE() - 10)
ORDER BY
statusopened
)t1
WHERE t.status IN ('B', 'J', 'K', '1')
AND (t.statusopened >= GETDATE() - 10)
)
SELECT Job_order,
STATUS,
SUM(timeinstatus)
FROM Temp
GROUP BY
Job_order,
STATUSIf you want to avoid the CTE (although I didn't quite follow the reason for it), you can make it into a subquery like shown below:SELECT Job_order,
STATUS,
SUM(timeinstatus)
FROM
(
SELECT t.Job_order,
t.status,
t.statusopened,
DATEDIFF(SECOND, t.statusopened, t1.statusopened) AS timeinstatus
FROM #tmp t
OUTER APPLY (
SELECT TOP 1 statusopened
FROM #tmp
WHERE Job_order = t.Job_order
AND STATUS IN ('B', 'J', 'K', '1')
AND statusopened > t.statusopened
AND (t.statusopened >= GETDATE() - 10)
ORDER BY
statusopened
)t1
WHERE t.status IN ('B', 'J', 'K', '1')
AND (t.statusopened >= GETDATE() - 10)
)s
GROUP BY
Job_order,
STATUS
|
|
|
|
Novice2Sql
Starting Member
33 Posts |
Posted - 11/05/2012 : 06:41:37
|
Hi
I used the last query that was provided; however the calculation is not correct and it missed one of the criteria. I don't quite understand the CTE syntax to be able to incorporate the case function so I can display the data the way I want; this is why I asked for a way not to use the CTE. But the problem still is its not calculating the status time correctly.
Thanks again for helping
|
|
|
|
Novice2Sql
Starting Member
33 Posts |
Posted - 11/05/2012 : 06:53:22
|
when i ran the query it missed the "J" status calculation for this job_order. The calculation should have came back from the time it was open in "J" until the time the status became "1": I have provided the data for your testing
INSERT
INTO #tmp(Job_order, status,statusopened, facility, Joborder_ID )
VALUES (
'0DA49A201260','A','6/11/2012 10:17:13 AM',11,1530018380
'0DA49A201260','C','6/11/2012 10:18:21 AM',11,1530018380
'0DA49A201260','B','6/13/2012 10:52:27 AM',11,1530018380
'0DA49A201260','1','6/13/2012 12:42:55 PM',11,1530018380
'0DA49A201260','J','7/11/2012 2:55:48 PM',11,1530018380
'0DA49A201260','1','7/17/2012 7:26:50 AM',11,1530018380
'0DA49A201260','C','10/29/2012 10:12:06 AM',11,1530018380
'0DA49A201260','B','10/30/2012 7:18:39 AM',11,1530018380
'0DA49A201260','1','10/30/2012 11:02:45 AM',11,1530018380
'0DA49A201260','B','11/4/2012 10:04:21 AM',11,1530018380
'0DA49A201260','1','11/4/2012 10:29:25 AM',11,1530018380
'0DA49A201260','C','11/4/2012 10:29:29 AM',11,1530018380
'0DA49A201260','B','11/4/2012 10:50:32 AM',11,1530018380
'0DA49A201260','E','11/4/2012 11:07:04 AM',11,1530018380
'0DA49A201260','F','11/4/2012 3:13:47 PM',11,1530018380); |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/05/2012 : 08:11:08
|
I can't recall all the logic that you were trying to implement. Just by running the query using the input data you posted, it seemed like the J is being excluded because of the condition "t.statusopened >= GETDATE() - 10)". If removing that condition does not give you the expected results, or if that is not the right thing to do, can you post the results that you should be getting?SELECT Job_order,
STATUS,
SUM(timeinstatus)
FROM
(
SELECT t.Job_order,
t.status,
t.statusopened,
DATEDIFF(SECOND, t.statusopened, t1.statusopened) AS timeinstatus
FROM #tmp t
OUTER APPLY (
SELECT TOP 1 statusopened
FROM #tmp
WHERE Job_order = t.Job_order
AND STATUS IN ('B', 'J', 'K', '1')
AND statusopened > t.statusopened
--AND (t.statusopened >= GETDATE() - 10)
ORDER BY
statusopened
)t1
WHERE t.status IN ('B', 'J', 'K', '1')
--AND (t.statusopened >= GETDATE() - 10)
)s
GROUP BY
Job_order,
STATUS
|
|
|
|
Novice2Sql
Starting Member
33 Posts |
Posted - 11/06/2012 : 05:57:13
|
Hi, I have a status table that I need the total time a record has been in status B, J, K, and 1. The record may go through many other status and back to the same status again. I basically need the sum of the total time it has been in the B, J, K and 1. The all the other status can be summed together. The "StatusOpened" field is populated when the status is open which also be the endtime for the previous status. So the calculation would be from the StatustOpened until it changes to the next status. Below is an example of how i want to display the data:
JOB_ID TIME_IN_STATUS "B"STATUS "J"STATUS "K"STATUS "1"STATUS NOT 1,J,K,B STATUS
MOW5887 20 MINS 190 MINS 50 MINS 10 MINS 220MINS
|
|
|
|
Topic |
|
|
|
aggregate time
