Same Table - Subset of Info - 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

Same Table - Subset of Info

Author

Topic

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2007-10-25 : 09:52:06

I'm beating my head up against a wall here. I have one very LARGE table called op_hist.

CREATE TABLE [dbo].[op_hist] (
[op_trnbr] [int] NOT NULL ,
[op_type] [varchar] (80) ,
[op_date] [smalldatetime] NULL ,
[op_act_setup] [decimal](38, 10) NULL ,
[op_act_run] [decimal](38, 10) NULL ,
[op_qty_comp] [decimal](38, 10) NULL ,
[op_wkctr] [varchar] (80) ,
[op_part] [varchar] (30)

)

I need to be able to sum items grouped by op_wkctr and op_part and op_date. Here's my latest version which of course does not work.

SELECT op_wkctr, pcs, hrs.b
FROM
dbo.op_hist
INNER JOIN
(
SELECT op_part, SUM(op_qty_comp) as pcs
FROM dbo.op_hist
where op_type='BACKFLSH'
group by op_hist.op_part

UNION ALL

SELECT op_part, SUM(op_act_setup + op_act_run) as b
FROM dbo.op_hist
where op_type='LABOR'
group by op_hist.op_part
) AS hrs
ON op_hist.op_part = hrs.op_part
where (op_wkctr = 'P-P36' or op_wkctr = 'P-P38' or op_wkctr='P39') and op_date ='10/22/07'

Help me oby one - you're my only hope.

Sincerely,
Frustrated!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-25 : 09:58:03

Try

SELECT 
	op_part, 
	SUM(case when op_type='BACKFLSH' then op_qty_comp else 0 end) as pcs,
	SUM(case when op_type='LABOR' then coalesce(op_act_setup,0) + coalesce(op_act_run,0) else 0 end) as b
FROM 
	dbo.op_hist
where 
	(op_wkctr = 'P-P36' or op_wkctr = 'P-P38' or op_wkctr='P39') and op_date ='10/22/07'
group by op_hist.op_part

Madhivanan

Failing to plan is Planning to fail

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2007-10-25 : 10:01:56

OMG! How simple of a solution was that?? I was trying to make a mountain out of a mole hill. Thanks so much! I'm forever in your debt - that worked perfectly.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-25 : 10:06:53

quote:
Originally posted by Girlnet

OMG! How simple of a solution was that?? I was trying to make a mountain out of a mole hill. Thanks so much! I'm forever in your debt - that worked perfectly.

You are welcome

Read about Cross-tab Reports in sql server help file for more informations on such type of output

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 10:09:04

Just to make sure op_qty_comp is not null and the date in ISO standard...

SELECT		op_part, 
		SUM(CASE WHEN op_type = 'BACKFLSH' THEN COALESCE(op_qty_comp, 0) ELSE 0 END) AS pcs,
		SUM(CASE WHEN op_type = 'LABOR' THEN COALESCE(op_act_setup, 0) + COALESCE(op_act_run, 0) ELSE 0 END) AS b
FROM		dbo.op_hist
WHERE		op_wkctr IN ('P-P36', 'P-P38', 'P39')
		AND op_date = '20071022'
GROUP BY	op_part

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources