Not Exists - 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)

Not Exists

Author

Topic

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2008-10-07 : 08:49:12

I have this statement . . .

SELECT op_wkctr as WC, op_part as PartNo,

case when datepart(hour,dateadd(s, Max(op_time), op_date)) between 00 and 06 then dateadd(day,-1,op.op_date) else op.op_date end as RunDate

FROM dbo.QAD_ro_det AS ro RIGHT OUTER JOIN
dbo.QAD_op_hist AS op ON ro.ro_routing = op.op_part AND ro.ro_op = op.op_wo_op where (op_wkctr Like 'T-%' or op_wkctr Like 'P-%') and op_shift Between 1 and 3 and

NOT EXISTS (Select r.RowID from TAM_DP_QAD_Reporting r where r.WC = op.op_wkctr and r.PartNo = op.op_part and r.RunDate = op.op_date and r.OP = op.op_wo_op and r.Shift=op.op_shift and r.OperatorID = op.op_emp and r.Plant=op.op_site) GROUP BY op_wkctr, op_part, op_date

The problem is when the query runs again - it sees records that have been adjusted due to the time of day (see above) as new records and adds them again.

I've tried several different options but nothing is working for me. Does anyone have any suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 09:58:32

post some sample data from your table and illustrate the output you want. its very difficult to understand what current query does.

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2008-10-07 : 10:28:24

It's comparing two tables and inserting if it doesn't exist.

Table1
1 4/1/08
2 4/2/08
3 4/3/08 (Evalutes time of day - if between midnight and 6AM - Turns date back 1 day (above).

Table2
1 4/1/08
2 4/2/08
3 4/3/08
3 4/4/08

It would insert 1 into Table 2. It would also insert 3 again cause when it compares, it doesn't take into account the time change occurring between midnight and 6AM and therefore, adds it again.

Does that help?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-07 : 10:33:47

I'd match by KEY instead of date.

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2008-10-07 : 10:43:13

I was trying to shorten it for readability . . . . but it sums a group of records up. So I can't use the original key. Here's the unshortened query . . . I apologize.

Insert into TAM_DP_QAD_Reporting (WC, [PartNo], RunDate, OP, Shift, OperatorID,
ToolNo, MultiOut, SPM, PcsProduced, Scrap, DownTime, Labor, Plant, RunRate)

SELECT op_wkctr as WC, op_part as PartNo,
case when datepart(hour,dateadd(s, Max(op_time), op_date)) between 00 and 06 then dateadd(day,-1,op.op_date) else op.op_date end as RunDate, op_wo_op as Op, op_shift as Shift, op_emp,
Case
when ro.ro_tool = COALESCE (ro.ro_tool, Null) Then op.op_part
when ro.ro_tool <> op.op_part Then ro.ro_tool
else op.op_part end as ToolNo, ro.ro__dec01 as MultiOut,

Case
when ro.ro__dec02 > 0 then convert(decimal(5, 1), (1/ro.ro__dec02) * sum(op.op_qty_comp)) else 0 end as SPM,

SUM(CASE WHEN op_type='BACKFLSH' THEN op_qty_comp ELSE 0 END) AS [PcsProd],
SUM(CASE WHEN op_type='SCRAP-O' THEN op__dec01 ELSE 0 END) AS [Scrap],
SUM(CASE WHEN op_type='DOWNTIME' THEN op_act_setup + op_act_run ELSE 0 END) AS [DownTime],
SUM(CASE WHEN op_type='LABOR' THEN op_act_setup + op_act_run ELSE 0 END) AS [Labor],
op_site,
case when ro.ro__dec01 > 0 then convert(decimal(5, 0), ((ro.ro__dec02/ro.ro__dec01)/60)) else 0 end as RunRate

FROM dbo.QAD_ro_det AS ro RIGHT OUTER JOIN
dbo.QAD_op_hist AS op ON ro.ro_routing = op.op_part AND ro.ro_op = op.op_wo_op
where (op_wkctr Like 'T-%' or op_wkctr Like 'P-%') and op_shift Between 1 and 3 and
NOT EXISTS (Select r.RowID from TAM_DP_QAD_Reporting r where r.WC = op.op_wkctr and r.PartNo = op.op_part and r.RunDate = op.op_date and r.OP = op.op_wo_op and r.Shift=op.op_shift and r.OperatorID = op.op_emp and r.Plant=op.op_site)

GROUP BY op_wkctr, op_part, op_date, op_wo_op, op_shift, op_emp, ro.ro__dec02, ro.ro__dec01, ro.ro_tool, op.op_site,ro.ro_end
HAVING ( (ro.ro_end IS NULL))

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-07 : 10:50:14

[code]
Insert into TAM_DP_QAD_Reporting (WC, [PartNo], RunDate, OP, Shift, OperatorID,
ToolNo, MultiOut, SPM, PcsProduced, Scrap, DownTime, Labor, Plant, RunRate)

SELECT
op_wkctr as WC,
op_part as PartNo,
case when datepart(hour,dateadd(s, Max(op_time), op_date)) between 00 and 06 then dateadd(day,-1,op.op_date) else op.op_date end as RunDate,
op_wo_op as Op,
op_shift as Shift,
op_emp,
Case
when ro.ro_tool = COALESCE (ro.ro_tool, Null) Then op.op_part
when ro.ro_tool <> op.op_part Then ro.ro_tool
else op.op_part end as ToolNo,
ro.ro__dec01 as MultiOut,

Case
when ro.ro__dec02 > 0 then convert(decimal(5, 1), (1/ro.ro__dec02) * sum(op.op_qty_comp)) else 0 end as SPM,

SUM(CASE WHEN op_type='BACKFLSH' THEN op_qty_comp ELSE 0 END) AS [PcsProd],
SUM(CASE WHEN op_type='SCRAP-O' THEN op__dec01 ELSE 0 END) AS [Scrap],
SUM(CASE WHEN op_type='DOWNTIME' THEN op_act_setup + op_act_run ELSE 0 END) AS [DownTime],
SUM(CASE WHEN op_type='LABOR' THEN op_act_setup + op_act_run ELSE 0 END) AS [Labor],
op_site,
case when ro.ro__dec01 > 0 then convert(decimal(5, 0), ((ro.ro__dec02/ro.ro__dec01)/60)) else 0 end as RunRate

FROM dbo.QAD_ro_det AS ro RIGHT OUTER JOIN
dbo.QAD_op_hist AS op ON ro.ro_routing = op.op_part AND ro.ro_op = op.op_wo_op
where (op_wkctr Like 'T-%' or op_wkctr Like 'P-%') and op_shift Between 1 and 3 and
NOT EXISTS (Select r.RowID from TAM_DP_QAD_Reporting r
where r.WC = op.op_wkctr
and r.PartNo = op.op_part
and r.RunDate = case when datepart(hour,dateadd(s, Max(op_time), op_date)) between 00 and 06 then dateadd(day,-1,op.op_date) else op.op_date end
and r.OP = op.op_wo_op
and r.Shift=op.op_shift
and r.OperatorID = op.op_emp
and r.Plant=op.op_site)

GROUP BY op_wkctr, op_part, op_date, op_wo_op, op_shift, op_emp, ro.ro__dec02, ro.ro__dec01, ro.ro_tool, op.op_site,ro.ro_end
HAVING ( (ro.ro_end IS NULL))[/code]

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2008-10-07 : 10:58:07

Thank you for your efforts. I appreciate it.

I already tried that - I get this "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-07 : 11:16:31

The problem is not the NOT EXISTS Your group by has to include the " case when "

Insert into TAM_DP_QAD_Reporting (WC, [PartNo], RunDate, OP, Shift, OperatorID,
ToolNo, MultiOut, SPM, PcsProduced, Scrap, DownTime, Labor, Plant, RunRate)

SELECT 
op_wkctr as WC, 
op_part as PartNo,
case when datepart(hour,dateadd(s, Max(op_time), op_date)) between 00 and 06 then dateadd(day,-1,op.op_date) else op.op_date end as RunDate, 
op_wo_op as Op, 
op_shift as Shift, 
op_emp,
Case
when ro.ro_tool = COALESCE (ro.ro_tool, Null) Then op.op_part
when ro.ro_tool <> op.op_part Then ro.ro_tool
else op.op_part end as ToolNo, 
ro.ro__dec01 as MultiOut,

Case
when ro.ro__dec02 > 0 then convert(decimal(5, 1), (1/ro.ro__dec02) * sum(op.op_qty_comp)) else 0 end as SPM,

SUM(CASE WHEN op_type='BACKFLSH' THEN op_qty_comp ELSE 0 END) AS [PcsProd],
SUM(CASE WHEN op_type='SCRAP-O' THEN op__dec01 ELSE 0 END) AS [Scrap],
SUM(CASE WHEN op_type='DOWNTIME' THEN op_act_setup + op_act_run ELSE 0 END) AS [DownTime],
SUM(CASE WHEN op_type='LABOR' THEN op_act_setup + op_act_run ELSE 0 END) AS [Labor],
op_site,
case when ro.ro__dec01 > 0 then convert(decimal(5, 0), ((ro.ro__dec02/ro.ro__dec01)/60)) else 0 end as RunRate

FROM dbo.QAD_ro_det AS ro RIGHT OUTER JOIN
dbo.QAD_op_hist AS op ON ro.ro_routing = op.op_part AND ro.ro_op = op.op_wo_op
where (op_wkctr Like 'T-%' or op_wkctr Like 'P-%') and op_shift Between 1 and 3 and
NOT EXISTS (Select r.RowID from TAM_DP_QAD_Reporting r 
where r.WC = op.op_wkctr 
and r.PartNo = op.op_part 
and r.RunDate = case when datepart(hour,dateadd(s, Max(op.op_time), op.op_date)) between 00 and 06 then dateadd(day,-1,op.op_date) else op.op_date end 
and r.OP = op.op_wo_op 
and r.Shift=op.op_shift 
and r.OperatorID = op.op_emp 
and r.Plant=op.op_site)

GROUP BY op_wkctr, op_part,
case when datepart(hour,dateadd(s, Max(op.op_time), op.op_date)) between 00 and 06 then dateadd(day,-1,op.op_date) else op.op_date end 
 , op_wo_op, op_shift, op_emp, ro.ro__dec02, ro.ro__dec01, ro.ro_tool, op.op_site,ro.ro_end
HAVING ( (ro.ro_end IS NULL))

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2008-10-07 : 11:36:10

Don't think you can do a case statement in a group by clause . . . Or can you?

"Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause."

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-07 : 11:45:12

sure you can, also your include in your group by for "case when" in the following columns ToolNo , SPM and RunRate

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2008-10-07 : 14:23:43

I'm still getting the same results - seems like there should be a better way. . . Did I miss something?

SELECT op_wkctr as WC, op_part as PartNo,
case when datepart(hour,dateadd(s, Max(op_time), op_date)) between 00 and 06 then dateadd(day,-1,op.op_date) else op.op_date end as RunDate,
op_wo_op as Op, op_shift as Shift, op_emp,
Case
when ro.ro_tool = COALESCE (ro.ro_tool, Null) Then op.op_part
when ro.ro_tool <> op.op_part Then ro.ro_tool
else op.op_part end as ToolNo, ro.ro__dec01 as MultiOut,

Case
when ro.ro__dec02 > 0 then convert(decimal(5, 1), (1/ro.ro__dec02) * sum(op.op_qty_comp)) else 0 end as SPM,

SUM(CASE WHEN op_type='BACKFLSH' THEN op_qty_comp ELSE 0 END) AS [PcsProd],
SUM(CASE WHEN op_type='SCRAP-O' THEN op__dec01 ELSE 0 END) AS [Scrap],
SUM(CASE WHEN op_type='DOWNTIME' THEN op_act_setup + op_act_run ELSE 0 END) AS [DownTime],
SUM(CASE WHEN op_type='LABOR' THEN op_act_setup + op_act_run ELSE 0 END) AS [Labor],
op_site,
case when ro.ro__dec01 > 0 then convert(decimal(5, 0), ((ro.ro__dec02/ro.ro__dec01)/60)) else 0 end as RunRate

FROM dbo.QAD_ro_det AS ro RIGHT OUTER JOIN
dbo.QAD_op_hist AS op ON ro.ro_routing = op.op_part AND ro.ro_op = op.op_wo_op
where (op_wkctr Like 'T-%' or op_wkctr Like 'P-%') and op_shift Between 1 and 3 and

NOT EXISTS (Select r.RowID from TAM_DP_QAD_Reporting r where r.WC = op.op_wkctr and r.PartNo = op.op_part
and r.RunDate = case when datepart(hour,dateadd(s, Max(op.op_time), op.op_date)) between 00 and 06 then dateadd(day,-1,op.op_date) else op.op_date end
and r.OP = op.op_wo_op and r.Shift=op.op_shift and r.OperatorID = op.op_emp
and r.Plant=op.op_site)

GROUP BY op_wkctr, op_part,
case when datepart(hour,dateadd(s, Max(op.op_time), op.op_date)) between 00 and 06 then dateadd(day,-1,op.op_date) else op.op_date end,
op_wo_op, op_shift, op_emp,
Case
when ro.ro__dec02 > 0 then convert(decimal(5, 1), (1/ro.ro__dec02) * sum(op.op_qty_comp)) else 0 end,
Case when ro.ro_tool = COALESCE (ro.ro_tool, Null) Then op.op_part
when ro.ro_tool <> op.op_part Then ro.ro_tool
else op.op_part end as ToolNo,
op.op_site,
case when ro.ro__dec01 > 0 then convert(decimal(5, 0), ((ro.ro__dec02/ro.ro__dec01)/60)) else 0 end
HAVING ( (ro.ro_end IS NULL))

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-07 : 15:32:25

sorry, you are correct, and I fixed for ya:

SELECT 
op_wkctr as WC, 
op_part as PartNo,
case when datepart(hour,dateadd(s, max(op_time), op_date)) between 00 and 06 then dateadd(day,-1,op_date) else op_date end as RunDate,
 op_wo_op as Op, 
 op_shift as Shift, 
 op_emp,
 Case
 when ro.ro_tool = COALESCE (ro.ro_tool, Null) Then op.op_part
 when ro.ro_tool <> op.op_part Then ro.ro_tool
 else op.op_part end as ToolNo, 
 ro.ro__dec01 as MultiOut,
  Case
  when ro.ro__dec02 > 0 then convert(decimal(5, 1), (1/ro.ro__dec02) * sum(op.op_qty_comp)) else 0 end as SPM,
 op_site,
 case when ro.ro__dec01 > 0 then convert(decimal(5, 0), ((ro.ro__dec02/ro.ro__dec01)/60)) else 0 end as RunRate,
  SUM(CASE WHEN op_type='BACKFLSH' THEN op_qty_comp ELSE 0 END) AS [PcsProd],
  SUM(CASE WHEN op_type='SCRAP-O' THEN op__dec01 ELSE 0 END) AS [Scrap],
  SUM(CASE WHEN op_type='DOWNTIME' THEN op_act_setup + op_act_run ELSE 0 END) AS [DownTime],
  SUM(CASE WHEN op_type='LABOR' THEN op_act_setup + op_act_run ELSE 0 END) AS [Labor]

FROM dbo.QAD_ro_det AS ro RIGHT OUTER JOIN
dbo.QAD_op_hist AS op ON ro.ro_routing = op.op_part AND ro.ro_op = op.op_wo_op
where (op_wkctr Like 'T-%' or op_wkctr Like 'P-%') and op_shift Between 1 and 3 
and not exists (

	select 1 from dbo.QAD_op_hist op, TAM_DP_QAD_Reporting r
 where r.WC = op.op_wkctr and r.PartNo = op.op_part
  and r.RunDate = case when datepart(hour,dateadd(s, (select max(op_time) from dbo.QAD_op_hist), op.op_date)) between 00 and 06 then dateadd(day,-1,op.op_date) else op.op_date end
  and r.OP = op.op_wo_op and r.Shift=op.op_shift and r.OperatorID = op.op_emp
  and r.Plant=op.op_site)

GROUP BY op_wkctr,
op_part, 
op_date,
op_wo_op, op_shift, op_emp, Case
 when ro.ro_tool = COALESCE (ro.ro_tool, Null) Then op.op_part
 when ro.ro_tool <> op.op_part Then ro.ro_tool
 else op.op_part end, ro.ro__dec01, ro.ro__dec02
,op_site

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-07 : 15:34:26

in the NOT EXISTS part use

(select max(op_time) from QAD_op_hist instead of MAX(op.op_time) fixed it. I used your original group by.

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2008-10-07 : 15:48:24

OUTSTANDING hanbingl! It ran with no errors. YEAH!! It looks like it gonna work just fine. I would have never have gotten that to work right.

Thanks again for your help!

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2008-10-08 : 14:55:29

Was wondering if you would help me take one last look at this thing. You're suggestions ran without error - but does not return any records (when I know there are records to be seen - even deleted half the table).

I'm sure it's in here . . . When I take out dbo.QAD_op_hist op - it returns records but it's filled with duplications.

NOT EXISTS (

select 1 from dbo.QAD_op_hist op, TAM_DP_QAD_Reporting r <------------------------------
where r.WC = op.op_wkctr and r.PartNo = op.op_part
and r.RunDate = case when datepart(hour,dateadd(s, (select max(op_time) from dbo.QAD_op_hist), op.op_date)) between 00 and 06 then dateadd(day,-1,op.op_date) else op.op_date end
and r.OP = op.op_wo_op and r.Shift=op.op_shift and r.OperatorID = op.op_emp
and r.Plant=op.op_site

)

I have been looking at this WAY to long. Help me Obi-Wan Kenobi - you are my only hope.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-08 : 16:41:03

yeah i changed your original query all you need to change in the NOT EXISTS part was the replace op.op_date with case when datepart(hour,dateadd(s, (select Max(op_time) from dbo.QAD_op_hist), op_date)) between 00 and 06 then dateadd(day,-1,op.op_date) else op.op_date end


SELECT op_wkctr as WC, op_part as PartNo,
case when datepart(hour,dateadd(s, Max(op_time), op_date)) between 00 and 06 then dateadd(day,-1,op.op_date) else op.op_date end as RunDate, op_wo_op as Op, op_shift as Shift, op_emp,
Case
when ro.ro_tool = COALESCE (ro.ro_tool, Null) Then op.op_part
when ro.ro_tool <> op.op_part Then ro.ro_tool
else op.op_part end as ToolNo, ro.ro__dec01 as MultiOut,

Case
when ro.ro__dec02 > 0 then convert(decimal(5, 1), (1/ro.ro__dec02) * sum(op.op_qty_comp)) else 0 end as SPM,

SUM(CASE WHEN op_type='BACKFLSH' THEN op_qty_comp ELSE 0 END) AS [PcsProd],
SUM(CASE WHEN op_type='SCRAP-O' THEN op__dec01 ELSE 0 END) AS [Scrap],
SUM(CASE WHEN op_type='DOWNTIME' THEN op_act_setup + op_act_run ELSE 0 END) AS [DownTime],
SUM(CASE WHEN op_type='LABOR' THEN op_act_setup + op_act_run ELSE 0 END) AS [Labor],
op_site,
case when ro.ro__dec01 > 0 then convert(decimal(5, 0), ((ro.ro__dec02/ro.ro__dec01)/60)) else 0 end as RunRate

FROM dbo.QAD_ro_det AS ro RIGHT OUTER JOIN
dbo.QAD_op_hist AS op ON ro.ro_routing = op.op_part AND ro.ro_op = op.op_wo_op
where (op_wkctr Like 'T-%' or op_wkctr Like 'P-%') and op_shift Between 1 and 3 and
NOT EXISTS (Select r.RowID from 
TAM_DP_QAD_Reporting r 
where r.WC = op.op_wkctr 
and r.PartNo = op.op_part 
and r.RunDate = case when datepart(hour,dateadd(s, (select Max(op_time) from dbo.QAD_op_hist),
 op_date)) between 00 and 06 then dateadd(day,-1,op.op_date) else op.op_date end 
and r.OP = op.op_wo_op 
and r.Shift=op.op_shift 
and r.OperatorID = op.op_emp 
and r.Plant=op.op_site)

GROUP BY op_wkctr, op_part, op_date, op_wo_op, op_shift, op_emp, ro.ro__dec02, ro.ro__dec01, ro.ro_tool, op.op_site,ro.ro_end
HAVING ( (ro.ro_end IS NULL))

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2008-10-08 : 17:17:10

Yep, I got it working now . . . FINALLY! I also had to change the case statement for the RunDate. Thanks very much for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources