SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 aggregate time
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

Novice2Sql
Starting Member

33 Posts

Posted - 10/29/2012 :  08:15:27  Show Profile  Reply with Quote
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
Go to Top of Page

Novice2Sql
Starting Member

33 Posts

Posted - 10/29/2012 :  08:19:45  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/29/2012 :  10:34:39  Show Profile  Reply with Quote
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/

Go to Top of Page

Novice2Sql
Starting Member

33 Posts

Posted - 10/31/2012 :  04:16:41  Show Profile  Reply with Quote
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
Go to Top of Page

Novice2Sql
Starting Member

33 Posts

Posted - 10/31/2012 :  06:39:06  Show Profile  Reply with Quote
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,
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/31/2012 :  06:58:07  Show Profile  Reply with Quote
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,
       STATUS
If 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
Go to Top of Page

Novice2Sql
Starting Member

33 Posts

Posted - 11/05/2012 :  06:41:37  Show Profile  Reply with Quote
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
Go to Top of Page

Novice2Sql
Starting Member

33 Posts

Posted - 11/05/2012 :  06:53:22  Show Profile  Reply with Quote
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);
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/05/2012 :  08:11:08  Show Profile  Reply with Quote
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


Go to Top of Page

Novice2Sql
Starting Member

33 Posts

Posted - 11/06/2012 :  05:57:13  Show Profile  Reply with Quote

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

Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.13 seconds. Powered By: Snitz Forums 2000