aggregate time - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

aggregate time

Author

Topic

Novice2Sql
Starting Member

33 Posts

Posted - 2012-10-12 : 06:50:09

I have a status table that I would like to get the total time a particular record has been each of the status. The record can go through many statuses and then back to the same status again.

The "StatusOpened" field is populated when the status is open which also be the endtime for the previous status. I have attached the output of one the jobs...Any suggestions?

Record_No JOB_ID StatusOpened Old_Status New_Status DATE_OF_CHANGE
2072670 MOW5887 10/11/12 9:27:18 A 10/11/12 9:27:18
2072672 MOW5887 10/11/12 9:27:32 B 10/11/12 9:27:32
2072919 MOW5887 10/11/12 13:49:33 E 10/11/12 13:49:33
2072990 MOW5887 10/11/12 15:06:11 F 10/11/12 15:06:11
2072992 MOW5887 10/11/12 15:08:54 S 10/11/12 15:08:54
2072993 MOW5887 10/11/12 15:09:10 8 10/11/12 15:09:10
2072996 MOW5887 10/11/12 15:14:05 B 10/11/12 15:14:05
2072997 MOW5887 10/11/12 15:14:12 F 10/11/12 15:14:12
2072998 MOW5887 10/11/12 15:14:22 S 10/11/12 15:14:22
2072999 MOW5887 10/11/12 15:14:34 U 10/11/12 15:14:34

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-12 : 07:17:01

Can you just explore your question?
And also provide expected output........

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-12 : 12:54:09

can you align sample data properly. I cant make out what are values for two statuses. I see two status field headers but I can spot only single status value per row

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Novice2Sql
Starting Member

33 Posts

Posted - 2012-10-16 : 06:57:49

I would like for it to display as follow:

JOB_ID TIME IN A STATUS TIME IN "B" STATUS TIME IN "E" STATUS TIME IN "F"STATUS
MOW5887 20 MINS 190 MIN 40 MINS 60 MINS
MOW5953 40 MINS 10 MINS 50 MINS 180MINS

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-16 : 07:25:14

Do you see the problem that Visakh was pointing out? In the sample data that you posted, there is no value/column in the data for New_Status

Record_No	JOB_ID	StatusOpened	Old_Status	New_Status	DATE_OF_CHANGE
2072670		MOW5887	10/11/12 9:27:18	A	???	10/11/12 9:27:18
2072672		MOW5887	10/11/12 9:27:32	B	???	10/11/12 9:27:32
2072919		MOW5887	10/11/12 13:49:33	E	???	10/11/12 13:49:33
2072990		MOW5887	10/11/12 15:06:11	F	???	10/11/12 15:06:11
2072992		MOW5887	10/11/12 15:08:54	S	???	10/11/12 15:08:54
2072993		MOW5887	10/11/12 15:09:10	8	???	10/11/12 15:09:10
2072996		MOW5887	10/11/12 15:14:05	B	???	10/11/12 15:14:05
2072997		MOW5887	10/11/12 15:14:12	F	???	10/11/12 15:14:12
2072998		MOW5887	10/11/12 15:14:22	S	???	10/11/12 15:14:22
2072999		MOW5887	10/11/12 15:14:34	U	???	10/11/12 15:14:34

BTW, enclose your code and or tables in [code] and [/code] tags to preserve white spaces so it is more easily readable.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-16 : 09:08:18

quote:
Originally posted by Novice2Sql

I would like for it to display as follow:

JOB_ID TIME IN A STATUS TIME IN "B" STATUS TIME IN "E" STATUS TIME IN "F"STATUS
MOW5887 20 MINS 190 MIN 40 MINS 60 MINS
MOW5953 40 MINS 10 MINS 50 MINS 180MINS

atleast put a column separator for us to understand which column has which values!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Novice2Sql
Starting Member

33 Posts

Posted - 2012-10-17 : 03:07:45

BELOW IS THE DATA FROM THE TABLE AND THE COLUMNS NAME.."OLD STATUS" FIELD IS BLANK IN THE TABLE. What I would like to get is the amount of time/SUM the JOB(JOB_ID) HAS SPENT IN A STATUS.. AS YOU CAN SEE A JOB_ID CAN REVERT BACK TO THE SAME STATUS..


Record_No	JOB_ID	StatusOpened	Old_Status	New_Status	DATE_OF_CHANGE
2072670		MOW5887	10/11/12 9:27:18		A	      10/11/12 9:27:18
2072672		MOW5887	10/11/12 9:27:32		B	      10/11/12 9:27:32
2072919		MOW5887	10/11/12 13:49:33		E	      10/11/12 13:49:33
2072990		MOW5887	10/11/12 15:06:11		F	      10/11/12 15:06:11
2072992		MOW5887	10/11/12 15:08:54		S	      10/11/12 15:08:54
2072993		MOW5887	10/11/12 15:09:10		8	      10/11/12 15:09:10
2072996		MOW5887	10/11/12 15:14:05		B	      10/11/12 15:14:05
2072997		MOW5887	10/11/12 15:14:12		F	      10/11/12 15:14:12
2072998		MOW5887	10/11/12 15:14:22		S	      10/11/12 15:14:22
2072999		MOW5887	10/11/12 15:14:34		U	      10/11/12 15:14:34

The results I would IS A COLUMN THAT SHOWS HOW LONG IT'S BEEN IN EACH STATUS :

[code]
JOB_ID TIME_IN_STATUS "A" STATUS "B" STATUS "E" STATUS DATE_OF_CHANGE
MOW5887 30MINS 20 MINS

Novice2Sql
Starting Member

33 Posts

Posted - 2012-10-17 : 03:07:46


Record_No	JOB_ID	StatusOpened	Old_Status	New_Status	DATE_OF_CHANGE
2072670		MOW5887	10/11/12 9:27:18		A	      10/11/12 9:27:18
2072672		MOW5887	10/11/12 9:27:32		B	      10/11/12 9:27:32
2072919		MOW5887	10/11/12 13:49:33		E	      10/11/12 13:49:33
2072990		MOW5887	10/11/12 15:06:11		F	      10/11/12 15:06:11
2072992		MOW5887	10/11/12 15:08:54		S	      10/11/12 15:08:54
2072993		MOW5887	10/11/12 15:09:10		8	      10/11/12 15:09:10
2072996		MOW5887	10/11/12 15:14:05		B	      10/11/12 15:14:05
2072997		MOW5887	10/11/12 15:14:12		F	      10/11/12 15:14:12
2072998		MOW5887	10/11/12 15:14:22		S	      10/11/12 15:14:22
2072999		MOW5887	10/11/12 15:14:34		U	      10/11/12 15:14:34

The results I would IS A COLUMN THAT SHOWS HOW LONG IT'S BEEN IN EACH STATUS :

[code]
JOB_ID TIME_IN_STATUS "A" STATUS "B" STATUS "E" STATUS DATE_OF_CHANGE
MOW5887 30MINS 20 MINS

Novice2Sql
Starting Member

33 Posts

Posted - 2012-10-17 : 03:13:06

Sorry for the caps...I am having a hard time aligning my columns in this forum.. but the OLD Status is blank; the status listed should be under the New_Status fields


Record_No	JOB_ID	StatusOpened	Old_Status	New_Status	DATE_OF_CHANGE
2072670		MOW5887	10/11/12 9:27:18		          A	      10/11/12 9:27:18
2072672		MOW5887	10/11/12 9:27:32		          B	      10/11/12 9:27:32
2072919		MOW5887	10/11/12 13:49:33		          E	      10/11/12 13:49:33
2072990		MOW5887	10/11/12 15:06:11		          F	      10/11/12 15:06:11
2072992		MOW5887	10/11/12 15:08:54		          S	      10/11/12 15:08:54
2072993		MOW5887	10/11/12 15:09:10		          8	      10/11/12 15:09:10
2072996		MOW5887	10/11/12 15:14:05		          B	      10/11/12 15:14:05
2072997		MOW5887	10/11/12 15:14:12		          F	      10/11/12 15:14:12
2072998		MOW5887	10/11/12 15:14:22		          S	      10/11/12 15:14:22
2072999		MOW5887	10/11/12 15:14:34		          U	      10/11/12 15:14:34

Novice2Sql
Starting Member

33 Posts

Posted - 2012-10-17 : 03:26:12

THE results I want calculated are for these particular status: B, J, K, and 1; all the other status can be summed together in another column;The table_name is status_history
I would appreciate any help on this.


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

Novice2Sql
Starting Member

33 Posts

Posted - 2012-10-17 : 03:33:30

By the way this table has several jobs and some jobs may never be put into the certain status, but I would like to get the sum of time a job was placed in the B,J,K,1 status...I only listed the one job above data

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-17 : 07:05:32

You can calculate the duration like shown below:

SELECT
	JOB_ID,
	Status,
	SUM(DATEDIFF(minute,a.status_opened,a.date_of_change)) AS Duration
FROM
	YourTable
GROUP BY
	JOB_ID,
	Status

To get it in the format you described, you can use the PIVOT operator - it would be something like this:

;WITH cte AS
(		
	SELECT
		JOB_ID,
		Status,
		SUM(DATEDIFF(minute,a.status_opened,a.date_of_change)) AS Duration
	FROM
		YourTable
	GROUP BY
		JOB_ID,
		Status
)
SELECT
	*
FROM
	cte
PIVOT(MAX(Duration) FOR [Status] IN ([B],[J],[K],[1])) P

A better alternative to posting the data in tabular form is to post a script that will create a table and insert data into it. For example,

CREATE TABLE #tmp(col1 INT, col2 VARCHAR(32));
INSERT INTO #tmp VALUES
(1,'MyFirstData'),
(2,'SomeOther Data'),
(3,'Yet another set of data');

The advantage of doing this way is that someone who wants to respond to your question will be able to copy that and run it to create a test table, look at the data, write a script and test it. The script I posted for your problem is really untested code because I didn't have the test data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-17 : 23:00:23

quote:
Originally posted by Novice2Sql

By the way this table has several jobs and some jobs may never be put into the certain status, but I would like to get the sum of time a job was placed in the B,J,K,1 status...I only listed the one job above data

can you explain how you got those values

ie for ex 20 for B etc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Novice2Sql
Starting Member

33 Posts

Posted - 2012-10-22 : 04:12:37

quote:
Originally posted by visakh16

quote:
Originally posted by Novice2Sql

By the way this table has several jobs and some jobs may never be put into the certain status, but I would like to get the sum of time a job was placed in the B,J,K,1 status...I only listed the one job above data. The calculation would be the (date of change - status opened) per that status, sum it up and then that would give me the total amount of time a job was in that particular status. I can try to pull some more data for you to test...I am still new at this so your help is greatly appreciate.

can you explain how you got those values

ie for ex 20 for B etc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

The data I provided in the original question came from the table. As far as the results information it was only for how i want the data to display after all the calculations were done in the query. So those numbers would be incorrect that is showing. the calculation would be

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-22 : 12:27:10

hmm...can you give a realistic example then? even status values are not as per that in sample data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Novice2Sql
Starting Member

33 Posts

Posted - 2012-10-24 : 06:31:58

The original data that I provided was pulled directly from the table...The example I sent for the results is incorrect data; it was just to show how I wanted the original data displayed.. I didn't do any real calculation for the results example.

Novice2Sql
Starting Member

33 Posts

Posted - 2012-10-24 : 06:34:32

Just a note all jobs will start in an "A" status. a job status in that status until the next status opened.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 09:16:00

quote:
Originally posted by Novice2Sql

The original data that I provided was pulled directly from the table...The example I sent for the results is incorrect data; it was just to show how I wanted the original data displayed.. I didn't do any real calculation for the results example.

then can you show exact expected result for given data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Novice2Sql
Starting Member

33 Posts

Posted - 2012-10-26 : 08:07:15

I hope this will help you help me in resolving my request...
Unfortunately, there is only one date field that can be used to calculate the time. Not sure how to code this one at all.because the StatusOpen timestamp is the beginning and ending time for a status. I need the sum of each status..

create table #tmp 
(Job_order varchar(13) null, 
status varchar(1) null, 
statusopened datetime,
facility int null, 
Joborder_ID int null 
)

INSERT
INTO #tmp(Job_order, status,statusopened, facility, Joborder_ID )
VALUES ( 
'0DA51A215531','A','6/15/2012 9:11:34 AM',10,1590164169
'0DA51A215531','C','6/15/2012 9:12:10 AM',10,1590164169
'0DA51A215531','B','6/25/2012 5:40:31 AM',10,1590164169
'0DA51A215531','1','6/26/2012 12:42:16 PM',10,1590164169
'0DA51A215531','C','7/2/2012 3:46:35 PM',10,1590164169
'0DA51A215531','1','7/21/2012 2:48:06 PM',10,1590164169
'0DA51A215531','J','8/14/2012 9:38:20 AM',10,1590164169
'0DA51A215531','1','8/22/2012 9:46:08 AM',10,1590164169
'0DA51A215531','J','8/27/2012 1:16:37 PM',10,1590164169
'0DA51A215531','1','8/27/2012 1:54:29 PM',10,1590164169
'0DA51A215531','J','9/7/2012 4:54:40 PM',10,1590164169
'0DA51A215531','1','9/8/2012 9:55:13 AM',10,1590164169
'0DA51A215531','K','9/10/2012 1:25:44 PM',10,1590164169
'0DA51A215531','B','9/11/2012 10:54:14 AM',10,1590164169
'0DA51A215531','1','9/11/2012 2:01:41 PM',10,1590164169
'0DA51A215531','K','9/11/2012 2:06:39 PM',10,1590164169
'0DA51A215531','1','9/11/2012 2:17:01 PM',10,1590164169
'0DA51A215531','J','9/17/2012 4:03:01 PM',10,1590164169
'0DA51A215531','B','9/18/2012 10:34:47 AM',10,1590164169
'0DA51A215531','G','9/21/2012 11:05:03 AM',10,1590164169
'0DA51A215531','B','9/24/2012 8:00:45 AM',10,1590164169
'0DA51A215531','K','9/25/2012 11:55:53 AM',10,1590164169
'0DA51A215531','B','9/25/2012 1:05:48 PM',10,1590164169
'0DA51A215531','G','9/26/2012 1:59:42 PM',10,1590164169
'0DA51A215531','B','9/27/2012 10:30:22 AM',10,1590164169
'0DA51A215531','K','9/28/2012 1:54:51 PM',10,1590164169
'0DA51A215531','B','9/28/2012 1:57:09 PM',10,1590164169
'0DA51A215531','G','9/28/2012 3:33:15 PM',10,1590164169
'0DA51A215531','E','9/29/2012 1:52:20 PM',10,1590164169
'0DA51A215531','F','9/29/2012 6:35:30 PM',10,1590164169
'0DA51A215531','K','9/29/2012 6:35:49 PM',10,1590164169
'0DA51A215531','B','10/5/2012 9:00:24 AM',10,1590164169
'0DA51A215531','G','10/22/2012 7:06:11 AM',10,1590164169
'0DA51A215531','E','10/22/2012 7:06:17 AM',10,1590164169
'0DA51A215531','F','10/22/2012 7:06:22 AM',10,1590164169
'0DA51A215531','D','10/22/2012 7:06:27 AM',10,1590164169
'0DA51A215531','P','10/22/2012 7:06:33 AM',10,1590164169
'0DA51A215531','F','10/22/2012 3:43:37 PM',10,1590164169
'0DA51A215531','S','10/22/2012 3:44:03 PM',10,1590164169
'0DA51A215531','U','10/22/2012 3:44:12 PM',10,1590164169);

Novice2Sql
Starting Member

33 Posts

Posted - 2012-10-26 : 08:13:04

Here is more data for testing

INSERT
INTO #tmp(Job_order, status,statusopened, facility, Joborder_ID )
VALUES ( 
'0DA51A218434','A','7/25/2012 1:30:50 AM',10,1590167093
'0DA51A218434','C','7/25/2012 1:31:12 AM',10,1590167093
'0DA51A218434','K','7/25/2012 1:33:38 AM',10,1590167093
'0DA51A218434','C','8/3/2012 8:51:28 AM',10,1590167093
'0DA51A218434','B','9/21/2012 7:16:56 AM',10,1590167093
'0DA51A218434','H','9/28/2012 7:11:12 AM',10,1590167093
'0DA51A218434','B','10/3/2012 11:11:49 AM',10,1590167093
'0DA51A218434','G','10/16/2012 9:22:23 AM',10,1590167093
'0DA51A218434','B','10/17/2012 10:25:44 AM',10,1590167093
'0DA51A218434','K','10/18/2012 2:09:47 PM',10,1590167093
'0DA51A218434','B','10/23/2012 11:07:26 AM',10,1590167093
'0DA51A218434','G','10/25/2012 11:47:42 AM',10,1590167093
'0DA51A218434','E','10/26/2012 6:57:53 AM',10,1590167093
'0DA51A218434','F','10/26/2012 1:03:44 PM',10,1590167093
'0DA51A218434','S','10/26/2012 1:04:02 PM',10,1590167093
'0DA51A218434','U','10/26/2012 1:04:12 PM',10,1590167093);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-26 : 10:08:06

something like


;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 statusopened > t.statusopened
             ORDER BY statusopened)t1
 )
 
 SELECT Job_order,Status,SUM(timeinstatus)
 FROM Temp
 GROUP BY Job_order,Status

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources