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
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Novice2Sql
Starting Member

33 Posts
Posted - 10/12/2012 :  06:50:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1430 Posts
Posted - 10/12/2012 :  07:17:01  Show Profile  Reply with Quote
Can you just explore your question?
And also provide expected output........

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47171 Posts
Posted - 10/12/2012 :  12:54:09  Show Profile  Reply with Quote
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/
Go to Top of Page

Novice2Sql
Starting Member

33 Posts
Posted - 10/16/2012 :  06:57:49  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 10/16/2012 :  07:25:14  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47171 Posts
Posted - 10/16/2012 :  09:08:18  Show Profile  Reply with Quote
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/
Go to Top of Page

Novice2Sql
Starting Member

33 Posts
Posted - 10/17/2012 :  03:07:45  Show Profile  Reply with Quote
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 :


JOB_ID TIME_IN_STATUS "A" STATUS "B" STATUS "E" STATUS DATE_OF_CHANGE
MOW5887 30MINS 20 MINS
Go to Top of Page

Novice2Sql
Starting Member

33 Posts
Posted - 10/17/2012 :  03:07:46  Show Profile  Reply with Quote
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 :


JOB_ID TIME_IN_STATUS "A" STATUS "B" STATUS "E" STATUS DATE_OF_CHANGE
MOW5887 30MINS 20 MINS
Go to Top of Page

Novice2Sql
Starting Member

33 Posts
Posted - 10/17/2012 :  03:13:06  Show Profile  Reply with Quote
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

Go to Top of Page

Novice2Sql
Starting Member

33 Posts
Posted - 10/17/2012 :  03:26:12  Show Profile  Reply with Quote
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

Go to Top of Page

Novice2Sql
Starting Member

33 Posts
Posted - 10/17/2012 :  03:33:30  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 10/17/2012 :  07:05:32  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47171 Posts
Posted - 10/17/2012 :  23:00:23  Show Profile  Reply with Quote
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/
Go to Top of Page

Novice2Sql
Starting Member

33 Posts
Posted - 10/22/2012 :  04:12:37  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47171 Posts
Posted - 10/22/2012 :  12:27:10  Show Profile  Reply with Quote
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/
Go to Top of Page

Novice2Sql
Starting Member

33 Posts
Posted - 10/24/2012 :  06:31:58  Show Profile  Reply with Quote
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.
Go to Top of Page

Novice2Sql
Starting Member

33 Posts
Posted - 10/24/2012 :  06:34:32  Show Profile  Reply with Quote
Just a note all jobs will start in an "A" status. a job status in that status until the next status opened.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47171 Posts
Posted - 10/24/2012 :  09:16:00  Show Profile  Reply with Quote
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/
Go to Top of Page

Novice2Sql
Starting Member

33 Posts
Posted - 10/26/2012 :  08:07:15  Show Profile  Reply with Quote
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);
Go to Top of Page

Novice2Sql
Starting Member

33 Posts
Posted - 10/26/2012 :  08:13:04  Show Profile  Reply with Quote
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);
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47171 Posts
Posted - 10/26/2012 :  10:08:06  Show Profile  Reply with Quote
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/
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next 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.19 seconds. Powered By: Snitz Forums 2000