GROUP BY Problem - 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

SQL Server 2000 Forums

Transact-SQL (2000)

GROUP BY Problem

Author

Topic

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2007-09-20 : 12:25:09

I'm having a difficult time figuring this one out. I have data as follows

Date|Job|Operation|WorkCenter|Hours
9/19/07|00100|1|A|.5
9/19/07|00100|2|B|.5
9/19/07|00100|3|C|.5
9/19/07|00100|4|C|.5
9/19/07|00100|5|D|.5
9/19/07|00100|6|C|.5
9/20/07|00100|7|E|.5

Somehow I need to get the sum of Hours grouped by Date, Job, and consecutive Operations for the same WorkCentre.

The result needs to look like this

Date|Job|Operations|WorkCenter|Hours
9/19/07|00100|1|A|.5
9/19/07|00100|2|B|.5
9/19/07|00100|3-4|C|1.0
9/19/07|00100|5|D|.5
9/19/07|00100|6|C|.5
9/20/07|00100|7|E|.5

The only way I can think to do this (besides looping through the records) is to somehow come up with a group code in an intermediate query to a temp table or table variable like this.

Date|Job|Operation|WorkCenter|GroupCode|Hours
9/19/07|00100|1|A|1|.5
9/19/07|00100|2|B|2|.5
9/19/07|00100|3|C|3|.5
9/19/07|00100|4|C|3|.5
9/19/07|00100|5|D|4|.5
9/19/07|00100|6|C|5|.5
9/20/07|00100|7|E|6|.5

Then I can select like this

SELECT Date
,Job
,GroupCode
,MIN(WorkCenter) AS WorkCenter
,CASE
WHEN MIN(Operation)= MAX(Operation) THEN
CAST(MIN(Operation) AS VARCHAR(4))
ELSE
CAST(MIN(Operation) AS VARCHAR(4)) + ' - ' + CAST(MAX(Operation) AS VARCHAR(4))
END AS Operations
,SUM(Hours) AS Hours
FROM IntermediateTable
GROUP BY Date
,Job
,GroupCode

Does anyone have any ideas how to do this?

For a bonus question - How can I get the row number of the operations that precede each row? For example, results like this -

Row|Date|Job|Operations|WorkCenter|Predecessor|Hours
1|9/19/07|00100|1|A|NULL|.5
2|9/19/07|00100|2|B|1|.5
3|9/19/07|00100|3-4|C|2|1.0
4|9/19/07|00100|5|D|3|.5
5|9/19/07|00100|6|C|4|.5
6|9/20/07|00100|7|E|5|.5

Thanks in advance,

Kevin

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-20 : 13:32:16

(1) Group by Date,Job,Operation and sum the hours (2) Use a function to concatenate the WorkCenter's.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2007-09-20 : 14:08:00

quote:
Originally posted by dinakar

(1) Group by Date,Job,Operation and sum the hours (2) Use a function to concatenate the WorkCenter's.

Unless I misunderstand something this doesn't seem like it will accomplish what I want.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-20 : 14:17:46

Peso haw written much on this

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Jim

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2007-09-20 : 14:28:55

quote:
Originally posted by jimf

Peso haw written much on this

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Jim

I'm not sure the post referred to will help. At least I don't understand how it will. I'm using SQL Server 2000. My main problem is how to get the records grouped correctly.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-20 : 15:05:08

HEre's a quick sample of what I mean.. sorry I have quite a few meetings today..


Declare @T Table (Date datetime,Job varchar(10),Operation int,WorkCenter varchar(10),Hours decimal(5,2) )
insert into @T
Select '9/19/07','00100',1,'A',.5 union all
Select '9/19/07','00100',2,'B',.5 union all
Select '9/19/07','00100',3,'C',.5 union all
Select '9/19/07','00100',4,'C',.5 union all
Select '9/19/07','00100',5,'D',.5 union all
Select '9/19/07','00100',6,'C',.5 union all
Select '9/20/07','00100',7,'E',.5


Select  T.Date, T.Job, T.Operation, T.WorkCenter,  sumhrs = isnull((Select sum(Isnull(T2.Hours,0))  + t.Hours
				from @T T2 
				Where T2.Date = T.date and T2.Job = T.job 
				and (T2.Operation - T.operation = 1)  and T2.WorkCenter = T.workcenter  
				) ,0) 
From @T T

You can do the concatenation 3-4 at the front end ... or even write a sub-query to do that here in T-SQL itsef.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2007-09-20 : 16:28:13

quote:
Originally posted by dinakar

HEre's a quick sample of what I mean.. sorry I have quite a few meetings today..

Declare @T Table (Date datetime,Job varchar(10),Operation int,WorkCenter varchar(10),Hours decimal(5,2) )
insert into @T
Select '9/19/07','00100',1,'A',.5 union all
Select '9/19/07','00100',2,'B',.5 union all
Select '9/19/07','00100',3,'C',.5 union all
Select '9/19/07','00100',4,'C',.5 union all
Select '9/19/07','00100',5,'D',.5 union all
Select '9/19/07','00100',6,'C',.5 union all
Select '9/20/07','00100',7,'E',.5

Select T.Date, T.Job, T.Operation, T.WorkCenter, sumhrs = isnull((Select sum(Isnull(T2.Hours,0)) + t.Hours
from @T T2
Where T2.Date = T.date and T2.Job = T.job
and (T2.Operation - T.operation = 1) and T2.WorkCenter = T.workcenter
) ,0)
From @T T

You can do the concatenation 3-4 at the front end ... or even write a sub-query to do that here in T-SQL itsef.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

I do appreciate the response. However, I don't see that this is accomplishing what I need.

Again, I need to group consecutive operations by date, job, and workcenter. Using the sample data from the original post, operations 3 and 4 would be combined. Also, operation 6 would be listed seperately since operation 5 is for a different work center.

The solution appears to be to somehow generate a value to group on and the only way I can think to do this is by looping through the rows. I was hoping to figure out a better way.

Kevin

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-20 : 16:57:04

Does this work for you?

Declare @T Table (Date datetime,Job varchar(10),Operation int,WorkCenter varchar(10),Hours decimal(5,2) )
insert into @T
Select '9/19/07','00100',1,'A',.5 union all
Select '9/19/07','00100',2,'B',.5 union all
Select '9/19/07','00100',3,'C',.5 union all
Select '9/19/07','00100',4,'C',.5 union all
Select '9/19/07','00100',5,'D',.5 union all
Select '9/19/07','00100',6,'C',.5 union all
Select '9/20/07','00100',7,'E',.5



SELECT
	Date,
	Job,
	WorkCenter,
	CAST(MIN(Operation) AS VARCHAR) + 
		CASE
			WHEN MIN(Operation) = MAX(Operation) THEN ''
			ELSE ' - ' + CAST(MAX(Operation) AS VARCHAR)

		END	AS Operations,
	SUM(Hours) AS Hours
FROM 
	@T AS T
GROUP BY
	Date,
	Job,
	WorkCenter

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2007-09-20 : 17:04:25

quote:

Originally posted by Lamprey

Does this work for you?

Declare @T Table (Date datetime,Job varchar(10),Operation int,WorkCenter varchar(10),Hours decimal(5,2) )
insert into @T
Select '9/19/07','00100',1,'A',.5 union all
Select '9/19/07','00100',2,'B',.5 union all
Select '9/19/07','00100',3,'C',.5 union all
Select '9/19/07','00100',4,'C',.5 union all
Select '9/19/07','00100',5,'D',.5 union all
Select '9/19/07','00100',6,'C',.5 union all
Select '9/20/07','00100',7,'E',.5



SELECT
	Date,
	Job,
	WorkCenter,
	CAST(MIN(Operation) AS VARCHAR) + 
		CASE
			WHEN MIN(Operation) = MAX(Operation) THEN ''
			ELSE ' - ' + CAST(MAX(Operation) AS VARCHAR)

		END	AS Operations,
	SUM(Hours) AS Hours
FROM 
	@T AS T
GROUP BY
	Date,
	Job,
	WorkCenter

No, the problem is that operations 3,4,and 6 would be grouped together.

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-20 : 17:12:59

In your data there is no way to determine 2 consecutive rows without the WorkCenter column and the data in that column is string type which seems to be complicating things.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2007-09-20 : 17:17:49

quote:
Originally posted by dinakar

In your data there is no way to determine 2 consecutive rows without the WorkCenter column and the data in that column is string type which seems to be complicating things.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

I guess that's why I asked. Unless there is a way to generate a value using a SQL statement to correctly group the records, it looks like I'm stuck looping through them one by one.

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-20 : 17:51:38

I see what you want to do. Given your sample data this should work:

Declare @T Table (Date datetime,Job varchar(10),Operation int,WorkCenter varchar(10),Hours decimal(5,2) )
insert into @T
Select '9/19/07','00100',1,'A',.5 union all
Select '9/19/07','00100',2,'B',.5 union all
Select '9/19/07','00100',3,'C',.5 union all
Select '9/19/07','00100',4,'C',.5 union all
Select '9/19/07','00100',5,'D',.5 union all
Select '9/19/07','00100',6,'C',.5 union all
Select '9/20/07','00100',7,'E',.5



SELECT
	Date,
	Job,
	WorkCenter,
	CAST(MIN(Operation) AS VARCHAR) + 
		CASE
			WHEN MIN(Operation) = MAX(Operation) THEN ''
			ELSE ' - ' + CAST(MAX(Operation) AS VARCHAR)

		END	AS Operations,
	SUM(Hours) AS Hours
FROM
(
	SELECT
		Operation,
		Date,
		Job,
		WorkCenter,
		Hours,
		(SELECT WorkCenter FROM @T WHERE Operation = (SELECT MAX(Operation) FROM @T WHERE Operation < T.Operation)) AS Prev,
		(SELECT WorkCenter FROM @T WHERE Operation = (SELECT MIN(Operation) FROM @T WHERE Operation > T.Operation)) AS Next
		
	FROM 
		@T AS T
) Temp
GROUP BY
	Date,
	Job,
	WorkCenter,
	CASE
		WHEN Temp.WorkCenter = Temp.Prev OR Temp.WorkCenter = Temp.Next THEN 0
		ELSE 1
	END
ORDER BY
	Date,
	Job,
	MIN(Operation),
	WorkCenter

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-20 : 17:59:42

Hi Lamprey

Changing the dataset to this:

Declare @T Table (Date datetime,Job varchar(10),Operation int,WorkCenter varchar(10),Hours decimal(5,2) )
insert into @T
Select '9/19/07','00100',1,'A',.5 union all
Select '9/19/07','00100',2,'B',.5 union all
Select '9/19/07','00100',3,'C',.5 union all
Select '9/19/07','00100',4,'C',.5 union all
Select '9/19/07','00100',5,'D',.5 union all
Select '9/19/07','00100',6,'C',.5 union all
Select '9/19/07','00100',7,'C',.5 union all
Select '9/20/07','00100',8,'E',.5

screws up the result. Looks like looping is the way...

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2007-09-20 : 18:04:35

quote:

Originally posted by Lamprey

I see what you want to do. Given your sample data this should work:

Declare @T Table (Date datetime,Job varchar(10),Operation int,WorkCenter varchar(10),Hours decimal(5,2) )
insert into @T
Select '9/19/07','00100',1,'A',.5 union all
Select '9/19/07','00100',2,'B',.5 union all
Select '9/19/07','00100',3,'C',.5 union all
Select '9/19/07','00100',4,'C',.5 union all
Select '9/19/07','00100',5,'D',.5 union all
Select '9/19/07','00100',6,'C',.5 union all
Select '9/20/07','00100',7,'E',.5



SELECT
	Date,
	Job,
	WorkCenter,
	CAST(MIN(Operation) AS VARCHAR) + 
		CASE
			WHEN MIN(Operation) = MAX(Operation) THEN ''
			ELSE ' - ' + CAST(MAX(Operation) AS VARCHAR)

		END	AS Operations,
	SUM(Hours) AS Hours
FROM
(
	SELECT
		Operation,
		Date,
		Job,
		WorkCenter,
		Hours,
		(SELECT WorkCenter FROM @T WHERE Operation = (SELECT MAX(Operation) FROM @T WHERE Operation < T.Operation)) AS Prev,
		(SELECT WorkCenter FROM @T WHERE Operation = (SELECT MIN(Operation) FROM @T WHERE Operation > T.Operation)) AS Next
		
	FROM 
		@T AS T
) Temp
GROUP BY
	Date,
	Job,
	WorkCenter,
	CASE
		WHEN Temp.WorkCenter = Temp.Prev OR Temp.WorkCenter = Temp.Next THEN 0
		ELSE 1
	END
ORDER BY
	Date,
	Job,
	MIN(Operation),
	WorkCenter

I'm not worthy...

You're amazing! I'll test it on live data but it looks like it works.

My humble thanks.

Kevin

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2007-09-20 : 18:08:57

quote:
Originally posted by dinakar

Hi Lamprey

Changing the dataset to this:

Declare @T Table (Date datetime,Job varchar(10),Operation int,WorkCenter varchar(10),Hours decimal(5,2) )
insert into @T
Select '9/19/07','00100',1,'A',.5 union all
Select '9/19/07','00100',2,'B',.5 union all
Select '9/19/07','00100',3,'C',.5 union all
Select '9/19/07','00100',4,'C',.5 union all
Select '9/19/07','00100',5,'D',.5 union all
Select '9/19/07','00100',6,'C',.5 union all
Select '9/19/07','00100',7,'C',.5 union all
Select '9/20/07','00100',8,'E',.5

screws up the result. Looks like looping is the way...

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Yes, that does appear to be a problem. This is a real possibility in the data.

Kevin

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-20 : 18:17:57

This works for both datasets.

SELECT
	Date,
	Job,
	WorkCenter,
	CAST(MIN(Operation) AS VARCHAR) + 
		CASE
			WHEN MIN(Operation) = MAX(Operation) THEN ''
			ELSE ' - ' + CAST(MAX(Operation) AS VARCHAR)

		END	AS Operations,
	SUM(Hours) AS Hours
FROM
(
	SELECT
		Operation,
		Date,
		Job,
		WorkCenter,
		Hours,
		(SELECT COUNT(*) FROM @T A WHERE Operation < T.Operation AND WorkCenter <> T.WorkCenter) AS Num
	FROM 
		@T AS T
) AS Temp
GROUP BY
	Date,
	Job,
	WorkCenter,
	Num

ORDER BY
	Date,
	Job,
	MIN(Operation),
	WorkCenter

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-20 : 18:41:53

quote:

Originally posted by Lamprey

This works for both datasets.

SELECT
	Date,
	Job,
	WorkCenter,
	CAST(MIN(Operation) AS VARCHAR) + 
		CASE
			WHEN MIN(Operation) = MAX(Operation) THEN ''
			ELSE ' - ' + CAST(MAX(Operation) AS VARCHAR)

		END	AS Operations,
	SUM(Hours) AS Hours
FROM
(
	SELECT
		Operation,
		Date,
		Job,
		WorkCenter,
		Hours,
		(SELECT COUNT(*) FROM @T A WHERE Operation < T.Operation AND WorkCenter <> T.WorkCenter) AS Num
	FROM 
		@T AS T
) AS Temp
GROUP BY
	Date,
	Job,
	WorkCenter,
	Num

ORDER BY
	Date,
	Job,
	MIN(Operation),
	WorkCenter

Perfect.. now have some

..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-20 : 18:43:11

Oh I'm gunna after today (work related pain). :)

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2007-09-20 : 18:46:21

Thanks again for your help!

Kevin

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2007-09-21 : 14:00:12

One last update. I did make one small modification as follows -

SELECT
Date,
Job,
WorkCenter,
CAST(MIN(Operation) AS VARCHAR) +
CASE
WHEN MIN(Operation) = MAX(Operation) THEN ''
ELSE ' - ' + CAST(MAX(Operation) AS VARCHAR)

END AS Operations,
SUM(Hours) AS Hours
FROM
(
SELECT
Operation,
Date,
Job,
WorkCenter,
Hours,
(SELECT COUNT(*) FROM @T A WHERE Job = T.Job AND Operation < T.Operation AND WorkCenter <> T.WorkCenter) AS Num
FROM
@T AS T
) AS Temp
GROUP BY
Date,
Job,
WorkCenter,
Num

ORDER BY
Date,
Job,
MIN(Operation),
WorkCenter

Have a great weekend.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-21 : 14:35:37

Morris, Adding that extra big breaks it.. FYI..

You do not need the Job in that sub-query because, according to the sample data, Operation is a sequence number and that is the key to making the query work correctly.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources