how to eliminate one extra row

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

how to eliminate one extra row

Author

Topic

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-01-30 : 13:40:31

CREATE TABLE #temp
(SAP_FL varchar(40),
EQUIPMENT_NO varchar(18),
SHORT_DESCR varchar(100),
COMPL_DATE datetime,
NOTIFICATION_NO varchar(12),
ACTIVITY_CODE VARCHAR(10),
ACTIVITY_NAME VARCHAR(100),
TOTAL_COUNT float,
CONST_TYPE varchar(100),
UNIT varchar(50),
PriorityID int,
)
-------------------------------------------------------
insert into #temp values ( '1023-A615400-443401-00901-00401' ,50001563 , 'Ultrasonic stack #30 ' , '11/14/2005' , '500722482' , 'REPR' , 'Repaired' , 70628 , 80159257 , 'EA' , 2 )
insert into #temp values ( '1023-A614400-443401-00901-00501' ,50001563 , 'Ultrasonic stack #30 ' , '4/15/2005' , '500357639' , 'REPR' , 'Repaired' , 70404 , 80159257 , 'EA' , 3 )
insert into #temp values ( '1023-A615400-443401-00901-00201' ,50001563 , 'Ultrasonic stack #30 ' , '3/21/2005' , '500329094' , 'REPR' , 'Repaired' , 70321 , 80159257 , 'EA' , 2 )

insert into #temp values ( '1023-A615400-443401-00901-00401' ,50001564 , 'Ultrasonic stack #30 ' , '11/14/2005' , '500722482' , 'REPL' , 'Replaced' , 70628 , 80159257 , 'EA' , 2 )
insert into #temp values ( '1023-A614400-443401-00901-00501' ,50001564 , 'Ultrasonic stack #30 ' , '4/15/2005' , '500357639' , 'REPL' , 'Replaced' , 70404 , 80159257 , 'EA' , 3 )
insert into #temp values ( '1023-A615400-443401-00901-00201' ,50001564 , 'Ultrasonic stack #30 ' , '3/21/2005' , '500329094' , 'REPL' , 'Repaired' , 70321 , 80159257 , 'EA' , 2 )

select
t1.SAP_FL as SAP_FL_ST ,
t2.SAP_FL as SAP_FL_ED,

t1.EQUIPMENT_NO as EQUIPMENT_NO_ST,
t2.EQUIPMENT_NO as EQUIPMENT_NO_ED,

t1.COMPL_DATE as COMPL_DATE_ST ,
t2.COMPL_DATE as COMPL_DATE_ED,

t1.SHORT_DESCR as SHORT_DESCR_ST,
t2.SHORT_DESCR as SHORT_DESCR_ED,

t1.NOTIFICATION_NO as NOTIFICATION_NO_ST,
t2.NOTIFICATION_NO as NOTIFICATION_NO_ED,

t1.ACTIVITY_CODE as ACTIVITY_CODE_ST,
t2.ACTIVITY_CODE as ACTIVITY_CODE_ED ,

t1.ACTIVITY_NAME as ACTIVITY_NAME_ST ,
t2.ACTIVITY_NAME as ACTIVITY_CODE_ED,

t1.TOTAL_COUNT as TOTAL_COUNT_ST ,
t2.TOTAL_COUNT as TOTAL_COUNT_ED ,

t1.TOTAL_COUNT -
t2.TOTAL_COUNT as TOTAL_COUNT_DIFFERENCE,

t1.CONST_TYPE as CONST_TYPE_ST ,
t2.CONST_TYPE as CONST_TYPE_ED ,
t1.UNIT

from (select SAP_FL as 'SAP_FL',
EQUIPMENT_NO as 'EQUIPMENT_NO',
SHORT_DESCR as 'SHORT_DESCR',
COMPL_DATE as 'COMPL_DATE',
NOTIFICATION_NO as 'NOTIFICATION_NO',
ACTIVITY_CODE as 'ACTIVITY_CODE',
ACTIVITY_NAME as 'ACTIVITY_NAME',
CONST_TYPE as 'CONST_TYPE',
UNIT as 'UNIT'
,max(TOTAL_COUNT) as 'TOTAL_COUNT'
from #temp
-- where EQUIPMENT_NO = 50001721 and ACTIVITY_CODE ='CLEN'
group by SAP_FL ,
EQUIPMENT_NO ,
COMPL_DATE ,
NOTIFICATION_NO ,
ACTIVITY_CODE ,
ACTIVITY_NAME ,
SHORT_DESCR ,
CONST_TYPE ,
UNIT )t1
INNER JOIN #temp t2
ON t1.EQUIPMENT_NO=t2.EQUIPMENT_NO
AND t1.ACTIVITY_CODE=t2.ACTIVITY_CODE
AND t1.COMPL_DATE>t2.COMPL_DATE

=====================================
OUTPUT
==================================
1023-A614400-443401-00901-00501 1023-A615400-443401-00901-00201 50001563 50001563 2005-04-15 00:00:00.000 2005-03-21 00:00:00.000 Ultrasonic stack #30 Ultrasonic stack #30 500357639 500329094 REPR REPR Repaired Repaired 70404 70321 83 80159257 80159257 EA
1023-A615400-443401-00901-00401 1023-A615400-443401-00901-00201 50001563 50001563 2005-11-14 00:00:00.000 2005-03-21 00:00:00.000 Ultrasonic stack #30 Ultrasonic stack #30 500722482 500329094 REPR REPR Repaired Repaired 70628 70321 307 80159257 80159257 EA
1023-A615400-443401-00901-00401 1023-A614400-443401-00901-00501 50001563 50001563 2005-11-14 00:00:00.000 2005-04-15 00:00:00.000 Ultrasonic stack #30 Ultrasonic stack #30 500722482 500357639 REPR REPR Repaired Repaired 70628 70404 224 80159257 80159257 EA
1023-A614400-443401-00901-00501 1023-A615400-443401-00901-00201 50001564 50001564 2005-04-15 00:00:00.000 2005-03-21 00:00:00.000 Ultrasonic stack #30 Ultrasonic stack #30 500357639 500329094 REPL REPL Replaced Repaired 70404 70321 83 80159257 80159257 EA
1023-A615400-443401-00901-00401 1023-A615400-443401-00901-00201 50001564 50001564 2005-11-14 00:00:00.000 2005-03-21 00:00:00.000 Ultrasonic stack #30 Ultrasonic stack #30 500722482 500329094 REPL REPL Replaced Repaired 70628 70321 307 80159257 80159257 EA
1023-A615400-443401-00901-00401 1023-A614400-443401-00901-00501 50001564 50001564 2005-11-14 00:00:00.000 2005-04-15 00:00:00.000 Ultrasonic stack #30 Ultrasonic stack #30 500722482 500357639 REPL REPL Replaced Replaced 70628 70404 224 80159257 80159257 EA

here the above query gives me correct output but it is giving an extra row--
like for equipment number 50001563 there are three dates present in input table # temp as '11/14/2005', '4/15/2005' '3/21/2005' and in out put i need to eliminate one extra row
like correct rows are
1)contain start date '11/14/2005' and end date as '4/15/2005'
2)contain start date '4/15/2005' and end date as '3/21/2005'
and extra row is
3)contain start date '11/14/2005' and end date as '3/21/2005'
so can anybody tell me how to eliminate one extra row which is shown above

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-30 : 14:08:23

See ROW_Number() function if you are using SQL Server 2005.

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-01-31 : 04:26:27

i can not use rownumber as i m using sql server 2000,could u please suggest how to overcome this scnerion -which i explained in query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-31 : 08:01:03

whats the rule for elimination of extra row? why do you think it should be excluded?

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-01-31 : 08:49:24

yes visakh it can b eliminated as

select * from #temp1
drop table #temp3
select * into #temp3 from #temp1
alter table #temp3 add row_ID int identity (1,1)
select * from #temp3

delete from #temp3 where exists
(select *
from #temp3 b
where #temp3.EQUIPMENT_NO_ST = b.EQUIPMENT_NO_ST
AND #temp3.ACTIVITY_CODE_ST = b.ACTIVITY_CODE_ST
AND #temp3.COMPL_DATE_ST = b.COMPL_DATE_ST
group by EQUIPMENT_NO_ST,ACTIVITY_CODE_ST
having #temp3.row_ID >min (b.row_ID)
===============================================
but prob is that when i tries it for another equip no which is having more rows then the query i posted fails
e.g u can try this on below code
----------------------------------------
CREATE TABLE #temp
(SAP_FL varchar(40),
EQUIPMENT_NO varchar(18),
SHORT_DESCR varchar(100),
COMPL_DATE datetime,
NOTIFICATION_NO varchar(12),
ACTIVITY_CODE VARCHAR(10),
ACTIVITY_NAME VARCHAR(100),
TOTAL_COUNT float,
CONST_TYPE varchar(100),
UNIT varchar(50),
PriorityID int,
)

————————————————————-
insert into #temp values ( ‘1023-A615400-443401-00901-00401' ,50001564 , ‘Ultrasonic stack #30 ‘ , ‘11/14/2005' , ‘500722482' , ‘REPL’ , ‘Replaced’ , 70628 , 80159257 , ‘EA’ , 2 )
insert into #temp values ( ‘1023-A614400-443401-00901-00501' ,50001564 , ‘Ultrasonic stack #30 ‘ , ‘4/15/2005' , ‘500357639' , ‘REPL’ , ‘Replaced’ , 70404 , 80159257 , ‘EA’ , 3 )
insert into #temp values ( ‘1023-A615400-443401-00901-00201' ,50001564 , ‘Ultrasonic stack #30 ‘ , ‘3/21/2005' , ‘500329094' , ‘REPL’ , ‘Repaired’ , 70321 , 80159257 , ‘EA’ , 2 )

insert into #temp values ( ‘1023-F306400-411401-00801' ,’50001721', ‘Module- Cutting Fluff Ovrnite/wing 295mm’, ‘10/4/2008' ,’502737991',’REPR’, ‘Repaired’, 218242 ,’80296971' ,’EA’, 3)
insert into #temp values ( ‘1023-F306400-411401-00801' ,’50001721', ‘Module- Cutting Fluff Ovrnite/wing 295mm’, ‘8/29/2008' ,’502675451',’REPR’, ‘Repaired’, 218242 ,’80296971' ,’EA’, 3)
insert into #temp values ( ‘1023-F306400-411401-00801' ,’50001721', ‘Module- Cutting Fluff Ovrnite/wing 295mm’, ‘8/29/2008' ,’502620150',’REPR’, ‘Repaired’, 218242 ,’80296971' ,’EA’, 3)
insert into #temp values ( ‘1023-F306400-411401-00801' ,’50001721', ‘Module- Cutting Fluff Ovrnite/wing 295mm’, ‘6/18/2008' ,’502495333',’REPR’, ‘Repaired’, 208032 ,’80296971' ,’EA’, 3)
insert into #temp values ( ‘1023-F306400-411401-00801' ,’50001721', ‘Module- Cutting Fluff Ovrnite/wing 295mm’, ‘9/7/2007' ,’501944796',’REPR’, ‘Repaired’, 197999 ,’80296971' ,’EA’, 3)
insert into #temp values ( ‘1023-F306400-411401-00801' ,’50001721', ‘Module- Cutting Fluff Ovrnite/wing 295mm’, ‘9/23/2005' ,’500648350',’REPR’, ‘Repaired’, 159277 ,’80296971' ,’EA’, 3)
insert into #temp values ( ‘1023-F306400-411401-00801' ,’50001721', ‘Module- Cutting Fluff Ovrnite/wing 295mm’, ‘12/17/2004',’500182407',’REPR’, ‘Repaired’, 118935 ,’80296971' ,’EA’, 3)

—————————————————-

select
t1.SAP_FL as SAP_FL_ST ,
t2.SAP_FL as SAP_FL_ED,

t1.EQUIPMENT_NO as EQUIPMENT_NO_ST,
t2.EQUIPMENT_NO as EQUIPMENT_NO_ED,

t1.COMPL_DATE as COMPL_DATE_ST ,
t2.COMPL_DATE as COMPL_DATE_ED,

t1.SHORT_DESCR as SHORT_DESCR_ST,
t2.SHORT_DESCR as SHORT_DESCR_ED,

t1.NOTIFICATION_NO as NOTIFICATION_NO_ST,
t2.NOTIFICATION_NO as NOTIFICATION_NO_ED,

t1.ACTIVITY_CODE as ACTIVITY_CODE_ST,
t2.ACTIVITY_CODE as ACTIVITY_CODE_ED ,

t1.ACTIVITY_NAME as ACTIVITY_NAME_ST ,
t2.ACTIVITY_NAME as ACTIVITY_CODE_ED,

t1.TOTAL_COUNT as TOTAL_COUNT_ST ,
t2.TOTAL_COUNT as TOTAL_COUNT_ED ,

t1.TOTAL_COUNT -
t2.TOTAL_COUNT as TOTAL_COUNT_DIFFERENCE,

t1.CONST_TYPE as CONST_TYPE_ST ,
t2.CONST_TYPE as CONST_TYPE_ED ,
t1.UNIT

from (select SAP_FL as ‘SAP_FL’,
EQUIPMENT_NO as ‘EQUIPMENT_NO’,
SHORT_DESCR as ‘SHORT_DESCR’,
COMPL_DATE as ‘COMPL_DATE’,
NOTIFICATION_NO as ‘NOTIFICATION_NO’,
ACTIVITY_CODE as ‘ACTIVITY_CODE’,
ACTIVITY_NAME as ‘ACTIVITY_NAME’,
CONST_TYPE as ‘CONST_TYPE’,
UNIT as ‘UNIT’
,max(TOTAL_COUNT) as ‘TOTAL_COUNT’
from #temp
– where EQUIPMENT_NO = 50001721
group by SAP_FL ,
EQUIPMENT_NO ,
COMPL_DATE ,
NOTIFICATION_NO ,
ACTIVITY_CODE ,
ACTIVITY_NAME ,
SHORT_DESCR ,
CONST_TYPE ,
UNIT )t1
INNER JOIN #temp t2
ON t1.EQUIPMENT_NO=t2.EQUIPMENT_NO
AND t1.ACTIVITY_CODE=t2.ACTIVITY_CODE
AND t1.COMPL_DATE>t2.COMPL_DATE

=====================================
OUTPUT–if we considered equi no 50001721
==================================
SAP_FL_ST SAP_FL_ED EQUIPMENT_NO_ST EQUIPMENT_NO_ED COMPL_DATE_ST COMPL_DATE_ED NOTIFICATION_NO_ST NOTIFICATION_NO_ED ACTIVITY_CODE_ST ACTIVITY_CODE_ED ACTIVITY_NAME_ST ACTIVITY_NAME_ED TOTAL_COUNT_ED TOTAL_COUNT_ST TOTAL_COUNT_DIFFERENCE SHORT_DESCR_ST SHORT_DESCR_ED CONST_TYPE_ST CONST_TYPE_ED UNIT
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 10/4/2008 8/29/2008 502737991 502675451 REPR REPR Repaired Repaired 218242 218242 0 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 10/4/2008 8/29/2008 502737991 502620150 REPR REPR Repaired Repaired 218242 218242 0 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 8/29/2008 502675451 502620150 REPR REPR Repaired Repaired 218242 218242 0 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 10/4/2008 6/18/2008 502737991 502495333 REPR REPR Repaired Repaired 218242 208032 10210 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 6/18/2008 502620150 502495333 REPR REPR Repaired Repaired 218242 208032 10210 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 6/18/2008 502675451 502495333 REPR REPR Repaired Repaired 218242 208032 10210 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 10/4/2008 9/7/2007 502737991 501944796 REPR REPR Repaired Repaired 218242 197999 20243 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 6/18/2008 9/7/2007 502495333 501944796 REPR REPR Repaired Repaired 208032 197999 10033 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 9/7/2007 502620150 501944796 REPR REPR Repaired Repaired 218242 197999 20243 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 9/7/2007 502675451 501944796 REPR REPR Repaired Repaired 218242 197999 20243 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 10/4/2008 9/23/2005 502737991 500648350 REPR REPR Repaired Repaired 218242 159277 58965 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 6/18/2008 9/23/2005 502495333 500648350 REPR REPR Repaired Repaired 208032 159277 48755 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 9/23/2005 502620150 500648350 REPR REPR Repaired Repaired 218242 159277 58965 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 9/23/2005 502675451 500648350 REPR REPR Repaired Repaired 218242 159277 58965 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 9/7/2007 9/23/2005 501944796 500648350 REPR REPR Repaired Repaired 197999 159277 38722 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 10/4/2008 12/17/2004 502737991 500182407 REPR REPR Repaired Repaired 218242 118935 99307 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 6/18/2008 12/17/2004 502495333 500182407 REPR REPR Repaired Repaired 208032 118935 89097 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 12/17/2004 502620150 500182407 REPR REPR Repaired Repaired 218242 118935 99307 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 12/17/2004 502675451 500182407 REPR REPR Repaired Repaired 218242 118935 99307 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 9/23/2005 12/17/2004 500648350 500182407 REPR REPR Repaired Repaired 159277 118935 40342 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 9/7/2007 12/17/2004 501944796 500182407 REPR REPR Repaired Repaired 197999 118935 79064 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-A614400-443401-00901-00501 1023-A615400-443401-00901-00201 50001564 50001564 3/21/2005 4/15/2005 500357639 500329094 REPL REPL Replaced Repaired 70404 70321 83 Ultrasonic stack #30 Ultrasonic stack #30 80159257 80159257 EA
1023-A615400-443401-00901-00401 1023-A614400-443401-00901-00501 50001564 50001564 4/15/2005 11/14/2005 500722482 500357639 REPL REPL Replaced Replaced 70628 70404 224 Ultrasonic stack #30 Ultrasonic stack #30 80159257 80159257 EA
1023-A615400-443401-00901-00401 1023-A615400-443401-00901-00201 50001564 50001564 3/21/2005 11/14/2005 500722482 500329094 REPL REPL Replaced Repaired 70628 70321 307 Ultrasonic stack #30 Ultrasonic stack #30 80159257 80159257 EA

here in output it puts tries to take start date as max date and calcuates difference based on max(date)- all remaining dates

but i want in this sequence

1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 10/4/2008 502737991 REPR Repaired 218242 80296971 EA 3
1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 8/29/2008 502675451 REPR Repaired 218242 80296971 EA 3
1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 8/29/2008 502620150 REPR Repaired 218242 80296971 EA 3
1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 6/18/2008 502495333 REPR Repaired 208032 80296971 EA 3
1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 9/7/2007 501944796 REPR Repaired 197999 80296971 EA 3
1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 9/23/2005 500648350 REPR Repaired 159277 80296971 EA 3
1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 12/17/2004 500182407 REPR Repaired 118935 80296971 EA 3
1023-A614400-443401-00901-00501 1023-A615400-443401-00901-00201 50001564 50001564 3/21/2005 4/15/2005 500357639 500329094 REPL REPL Replaced Repaired 70404 70321 83 Ultrasonic stack #30 Ultrasonic stack #30 80159257 80159257 EA
1023-A615400-443401-00901-00401 1023-A614400-443401-00901-00501 50001564 50001564 4/15/2005 11/14/2005 500722482 500357639 REPL REPL Replaced Replaced 70628 70404 224 Ultrasonic stack #30 Ultrasonic stack #30 80159257 80159257 EA
—————————————————————————
can anybody has the solution for this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-31 : 09:23:31

nope i was asking whats the rule which decides which row should be returned and which to be eliminated?

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-01-31 : 10:23:03

suppose i have these 3 dates in input
‘11/14/2005' ,
‘4/15/2005'
‘3/21/2005'
only specific rule for this is
if we arrange them in descending order then
we need to display
start date ‘11/14/2005' - end date ‘4/15/2005'
start date ‘4/15/2005' - end date ‘3/21/2005'
like first max date - seocnd max date
but right now it tries to calculate difference between
first date(‘11/14/2005')and third date also (‘3/21/2005')
viz start date ‘11/14/2005' , - end date ‘4/15/2005'
that means this row is unwanted

so can u pls tell how to do this as this is an urgent issue
pls help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-31 : 13:55:41

something like


select
t1.SAP_FL as SAP_FL_ST ,
MAX(t2.SAP_FL) as SAP_FL_ED, 

t1.EQUIPMENT_NO as EQUIPMENT_NO_ST,
MAX(t2.EQUIPMENT_NO) as EQUIPMENT_NO_ED,

t1.COMPL_DATE as COMPL_DATE_ST ,
MAX(t2.COMPL_DATE) as COMPL_DATE_ED, 

t1.SHORT_DESCR as SHORT_DESCR_ST,
MAX(t2.SHORT_DESCR) as SHORT_DESCR_ED,

t1.NOTIFICATION_NO as NOTIFICATION_NO_ST,
MAX(t2.NOTIFICATION_NO) as NOTIFICATION_NO_ED,

t1.ACTIVITY_CODE as ACTIVITY_CODE_ST,
MAX(t2.ACTIVITY_CODE) as ACTIVITY_CODE_ED ,

t1.ACTIVITY_NAME as ACTIVITY_NAME_ST ,
MAX(t2.ACTIVITY_NAME) as ACTIVITY_CODE_ED,

t1.TOTAL_COUNT as TOTAL_COUNT_ST ,
MAX(t2.TOTAL_COUNT) as TOTAL_COUNT_ED ,

t1.TOTAL_COUNT -
MAX(t2.TOTAL_COUNT) as TOTAL_COUNT_DIFFERENCE,

t1.CONST_TYPE as CONST_TYPE_ST ,
MAX(t2.CONST_TYPE) as CONST_TYPE_ED ,
t1.UNIT 

from (select SAP_FL as ‘SAP_FL’,
EQUIPMENT_NO as ‘EQUIPMENT_NO’,
SHORT_DESCR as ‘SHORT_DESCR’,
COMPL_DATE as ‘COMPL_DATE’,
NOTIFICATION_NO as ‘NOTIFICATION_NO’,
ACTIVITY_CODE as ‘ACTIVITY_CODE’,
ACTIVITY_NAME as ‘ACTIVITY_NAME’,
CONST_TYPE as ‘CONST_TYPE’,
UNIT as ‘UNIT’
,max(TOTAL_COUNT) as ‘TOTAL_COUNT’
from #temp
– where EQUIPMENT_NO = 50001721
group by SAP_FL ,
EQUIPMENT_NO ,
COMPL_DATE ,
NOTIFICATION_NO ,
ACTIVITY_CODE ,
ACTIVITY_NAME ,
SHORT_DESCR ,
CONST_TYPE ,
UNIT )t1
INNER JOIN #temp t2
ON t1.EQUIPMENT_NO=t2.EQUIPMENT_NO
AND t1.ACTIVITY_CODE=t2.ACTIVITY_CODE
AND t1.COMPL_DATE>t2.COMPL_DATE
GROUP BY t1.SAP_FL,
t1.EQUIPMENT_NO ,
t1.COMPL_DATE,
t1.SHORT_DESCR,
t1.NOTIFICATION_NO,
t1.ACTIVITY_CODE,
t1.ACTIVITY_NAME,
t1.TOTAL_COUNT,
t1.CONST_TYPE,
t1.UNIT

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-01 : 04:19:31

thanks vishkh u r great man!!!
it worked
but will it work for all data containing 4000 rows?
i m checking

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-01 : 12:54:29

i think it should work fine...anyways check & lemme know

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-02 : 02:16:23

Vishkh it worked but now prob is
suppose i have rows in #temp table like this
SAP_FL EQUIPMENT_NO SHORT_DESCR COMPL_DATE NOTIFICATION_NO ACTIVITY_CODE ACTIVITY_NAME TOTAL_COUNT CONST_TYPE UNIT
1023-F306400-411401-00801 50001722 Module, Cutting Fluff 295MM Overnight 12/15/2008 502871484 CLEN Cleaned 991855 1705917 EA
1023-F306400-411401-00801 50001722 Module, Cutting Fluff 295MM Overnight 10/29/2008 502793134 CLEN Cleaned 940532 1705917 EA
1023-F306400-414601-00801 50001722 Module, Cutting Fluff 295MM Overnight 8/21/2008 502670374 REPR Repaired 880917 1705917 EA
1023-F306400-411401-00801 50001722 Module, Cutting Fluff 295MM Overnight 8/9/2008 502646687 GRCH Grade/Brand Change 880917 1705917 EA
1023-F306400-411401-00801 50001722 Module, Cutting Fluff 295MM Overnight 3/14/2008 502366096 REPR Repaired 826254 1705917 EA
1023-F306400-411401-00801 50001722 Module, Cutting Fluff 295MM Overnight 9/18/2007 502001373 REPL Replaced 787762 1705917 EA
1023-F306400-411401-00801 50001722 Module, Cutting Fluff 295MM Overnight 12/28/2006 501407837 CLEN Cleaned 500063 1705917 EA
1023-F306400-411401-00801 50001722 Module, Cutting Fluff 295MM Overnight 5/13/2005 500410030 REPR Repaired 131679 1705917 EA

-----------------------------------------------------------------
output-as below-try to copy nelow output in excel sheet so that u can understand
--------------------------------------------------------------
SAP_FL_ST SAP_FL_ED EQUIPMENT_NO_ST EQUIPMENT_NO_ED COMPL_DATE_ST COMPL_DATE_ED SHORT_DESCR_ST SHORT_DESCR_ED NOTIFICATION_NO_ST NOTIFICATION_NO_ED ACTIVITY_CODE_ST ACTIVITY_CODE_ED ACTIVITY_NAME_ST ACTIVITY_NAME_ED TOTAL_COUNT_ST TOTAL_COUNT_ED TOTAL_COUNT_Diff CONST_TYPE_ST CONST_TYPE_ED Unit
1023-F306400-411401-00801 1023-F306400-411401-00801 50001722 50001722 5/13/2005 3/14/2008 Module, Cutting Fluff 295MM Overnight Module, Cutting Fluff 295MM Overnight 502366096 500410030 REPR REPR Repaired Repaired 826254 131679 694575 1705917 1705917 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001722 50001722 12/28/2006 10/29/2008 Module, Cutting Fluff 295MM Overnight Module, Cutting Fluff 295MM Overnight 502793134 501407837 CLEN CLEN Cleaned Cleaned 940532 500063 440469 1705917 1705917 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001722 50001722 10/29/2008 12/15/2008 Module, Cutting Fluff 295MM Overnight Module, Cutting Fluff 295MM Overnight 502871484 502793134 CLEN CLEN Cleaned Cleaned 991855 940532 51323 1705917 1705917 EA
1023-F306400-414601-00801 1023-F306400-411401-00801 50001722 50001722 3/14/2008 8/21/2008 Module, Cutting Fluff 295MM Overnight Module, Cutting Fluff 295MM Overnight 502670374 502366096 REPR REPR Repaired Repaired 880917 826254 54663 1705917 1705917 EA
----------------------------------------------------------------
here in input if u watch activity code GRCH and REPL
for which rows are only one-
in this case can we have put like this
--------------------------------------------------------
SAP_FL_ST SAP_FL_ED EQUIPMENT_NO_ST EQUIPMENT_NO_ED COMPL_DATE_ST COMPL_DATE_ED SHORT_DESCR_ST SHORT_DESCR_ED NOTIFICATION_NO_ST NOTIFICATION_NO_ED ACTIVITY_CODE_ST ACTIVITY_CODE_ED ACTIVITY_NAME_ST ACTIVITY_NAME_ED TOTAL_COUNT_ST TOTAL_COUNT_ED TOTAL_COUNT_Diff CONST_TYPE_ST CONST_TYPE_ED Unit
1023-F306400-411401-00801 null or zero 50001722 null or zero 8/9/2008 null or zero Module, Cutting Fluff 295MM Overnight null or zero 502646687 null or zero GRCH null or zero Grade/Brand Change null or zero 880917 null or zero 880917 1705917 null or zero EA
1023-F306400-411401-00801 null or zero 50001722 null or zero 9/18/2007 null or zero Module, Cutting Fluff 295MM Overnight null or zero 502001373 null or zero REPL null or zero Replaced null or zero 787762 null or zero 787762 1705917 null or zero EA
----------------------------------------------------------
can u pls advise on this also?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 02:26:26

ok..in that case make the join left outer rather than inner join

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-02 : 04:42:56

great visakh !!! excellent.
in which city ur staying in india?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 08:35:42

welcome
http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=33104

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-03 : 08:19:13

visakh there's prob for similer requirement
suppose i have following input
1022-A302400-414601-00801 000000000050030511 Anvil Roll Assembly, 7.50 wide 2008-11-26 11:36:43.000 000502779252 SHRP Sharpened-Grind 56659 000000000001200847 EA
1022-F708400-414601-00801 000000000050030511 Anvil Roll Assembly, 7.50 wide 2008-11-24 07:16:32.000 000502319764 LUBE Lubricated 56659 000000000001200847 EA
1022-F716400-414601-00801 000000000050030511 Anvil Roll Assembly, 7.50 wide 2008-09-22 08:01:05.000 000502646226 SHRP Sharpened-Grind 56659 000000000001200847 EA
1022-F708400-414601-00801 000000000050030511 Anvil Roll Assembly, 7.50 wide 2008-07-22 12:05:50.000 000502166642 LUBE Lubricated 1 000000000001200847 EA
---------------------------------------------------------------------------------
here output will b like this
----------------------------------------------------------------------------------
1022-A302400-414601-00801 1022-F716400-414601-00801 000000000050030511 2008-11-24 07:16:32.000 2008-11-26 11:36:43.000 Anvil Roll Assembly, 7.50 wide Anvil Roll Assembly, 7.50 wide 000502646226 000502779252 SHRP SHRP Sharpened-Grind Sharpened-Grind 56659 56659 0 000000000001200847 000000000001200847 EA
1022-F708400-414601-00801 1022-F716400-414601-00801 000000000050030511 2008-09-22 08:01:05.000 2008-11-24 07:16:32.000 Anvil Roll Assembly, 7.50 wide Anvil Roll Assembly, 7.50 wide 000502646226 000502319764 SHRP LUBE Sharpened-Grind Lubricated 56659 56659 0 000000000001200847 000000000001200847 EA
1022-F716400-414601-00801 1022-F708400-414601-00801 000000000050030511 2008-07-22 12:05:50.000 2008-09-22 08:01:05.000 Anvil Roll Assembly, 7.50 wide Anvil Roll Assembly, 7.50 wide 000502166642 000502646226 LUBE SHRP Lubricated Sharpened-Grind 1 56659 56658 000000000001200847 000000000001200847 EA

---------------------------------------------------------------------------
i tried with u r last query all fields are flowing right except
ACTIVITY_CODE_ST ,
ACTIVITY_CODE_ED,
ACTIVITY_NAME_ST,
ACTIVITY_NAME_ED ,
can u please suggest this as well
it's same like last query u posted only
we dont need the condition t1.ACTIVITY_CODE=t2.ACTIVITY_CODE(from last code)
i commented same in the query u posted but still there's prob pls help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 08:51:54

so whats should be expected result?

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-03 : 09:00:48

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-03 : 09:03:15

SAP_FL_ST SAP_FL_ED EQUIPMENT_NO_ST EQUIPMENT_NO_ED COMPL_DATE_ST COMPL_DATE_ED SHORT_DESCR_ST SHORT_DESCR_ED NOTIFICATION_NO_ST NOTIFICATION_NO_ED ACTIVITY_CODE_ST ACTIVITY_CODE_ED ACTIVITY_NAME_ST ACTIVITY_NAME_ED TOTAL_COUNT_ST TOTAL_COUNT_ED TOTAL_COUNT_Diff CONST_TYPE_ST CONST_TYPE_ED Unit
1022-A302400-414601-00801 1022-F716400-414601-00801 000000000050030511 000000000050030511 2008-11-24 07:16:32.000 2008-11-26 11:36:43.000 Anvil Roll Assembly, 7.50 wide Anvil Roll Assembly, 7.50 wide 000502646226 000502779252 SHRP SHRP Sharpened-Grind Sharpened-Grind 56659 56659 0 000000000001200847 000000000001200847 EA
1022-F708400-414601-00801 1022-F716400-414601-00801 000000000050030511 000000000050030511 2008-09-22 08:01:05.000 2008-11-24 07:16:32.000 Anvil Roll Assembly, 7.50 wide Anvil Roll Assembly, 7.50 wide 000502646226 000502319764 LUBE SHRP Lubricated Sharpened-Grind 56659 56659 0 000000000001200847 000000000001200847 EA
1022-F716400-414601-00801 1022-F708400-414601-00801 000000000050030511 000000000050030511 2008-07-22 12:05:50.000 2008-09-22 08:01:05.000 Anvil Roll Assembly, 7.50 wide Anvil Roll Assembly, 7.50 wide 000502166642 000502646226 SHRP LUBE Sharpened-Grind Lubricated 1 56659 56658 000000000001200847 000000000001200847 EA

this can b out put suppose we have 4 rows perticular rows equipment in input then in output we have 3 rows flowing

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-03 : 09:04:34

visakh i m sorry i posted wrong output let me post correct output

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-03 : 09:16:33

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-03 : 09:20:32

drop table #temp
CREATE TABLE #temp
(SAP_FL varchar(40),
EQUIPMENT_NO varchar(18),
SHORT_DESCR varchar(100),
COMPL_DATE datetime,
NOTIFICATION_NO varchar(12),
ACTIVITY_CODE VARCHAR(10),
ACTIVITY_NAME VARCHAR(100),
TOTAL_COUNT float,
CONST_TYPE varchar(100),
UNIT varchar(50)

)

-------------------------------------------------------------------------------
insert into #temp values ( '1022-A302400-414601-00801','50030511' , 'Anvil Roll Assembly- 7.50 wide' , '11/26/2008' , '502779252' , 'SHRP' , 'Sharpened-Grind' ,'56659' ,'1200847' , 'EA')
insert into #temp values( '1022-F708400-414601-00801','50030511' , 'Anvil Roll Assembly- 7.50 wide' , '11/24/2008' , '502319764' , 'LUBE' , 'Lubricated' ,'56659' ,'1200847' , 'EA')
insert into #temp values( '1022-F716400-414601-00801','50030511' , 'Anvil Roll Assembly- 7.50 wide' , '9/22/2008' , '502646226' , 'SHRP' , 'Sharpened-Grind' ,'56659' ,'1200847' , 'EA')
insert into #temp values( '1022-F708400-414601-00801','50030511' , 'Anvil Roll Assembly- 7.50 wide' , '7/22/2008' , '502166642' , 'LUBE' , 'Lubricated' ,'56659' ,'1200847' , 'EA')

insert into #temp values ( '1022-A302400-414601-00801','50030512' , 'Anvil Roll Assembly- 7.50 wide' , '12/26/2008' , '502779252' , 'FABR' , 'Fabricated' ,'56659' ,'1200847' , 'EA')
insert into #temp values( '1022-F708400-414601-00801','50030512' , 'Anvil Roll Assembly- 7.50 wide' , '11/24/2008' , '502319764' , 'LUBE' , 'Lubricated' ,'56659' ,'1200847' , 'EA')
-------------------------------------------------------------------------------
select * from #temp
select
t1.SAP_FL as SAP_FL_ST ,
MAX(t2.SAP_FL) as SAP_FL_ED,

t1.EQUIPMENT_NO as EQUIPMENT_NO_ST,
MAX(t2.EQUIPMENT_NO) as EQUIPMENT_NO_ED,

MAX(t2.COMPL_DATE) as COMPL_DATE_ST,
t1.COMPL_DATE as COMPL_DATE_ED ,

t1.SHORT_DESCR as SHORT_DESCR_ST,
MAX(t2.SHORT_DESCR) as SHORT_DESCR_ED,

MAX(t2.NOTIFICATION_NO) as NOTIFICATION_NO_ST,
t1.NOTIFICATION_NO as NOTIFICATION_NO_ED,

t1.ACTIVITY_CODE as ACTIVITY_CODE_ST,
MAX(t2.ACTIVITY_CODE) as ACTIVITY_CODE_ED ,

t1.ACTIVITY_NAME as ACTIVITY_NAME_ST ,
MAX(t2.ACTIVITY_NAME) as ACTIVITY_NAME_ED,

MAX(t2.TOTAL_COUNT) as TOTAL_COUNT_ST ,
t1.TOTAL_COUNT as TOTAL_COUNT_ED ,
t1.TOTAL_COUNT -MAX(t2.TOTAL_COUNT) as TOTAL_COUNT_DIFFERENCE,

t1.CONST_TYPE as CONST_TYPE_ST ,
MAX(t2.CONST_TYPE) as CONST_TYPE_ED ,
t1.UNIT

from
(select SAP_FL as 'SAP_FL',
EQUIPMENT_NO as 'EQUIPMENT_NO',
SHORT_DESCR as 'SHORT_DESCR',
COMPL_DATE as 'COMPL_DATE',
NOTIFICATION_NO as 'NOTIFICATION_NO',
ACTIVITY_CODE as 'ACTIVITY_CODE',
ACTIVITY_NAME as 'ACTIVITY_NAME',
CONST_TYPE as 'CONST_TYPE',
UNIT as 'UNIT'
,max(TOTAL_COUNT) as 'TOTAL_COUNT'
from #temp
--where EQUIPMENT_NO = 000000000050030511
group by
SAP_FL ,
EQUIPMENT_NO ,
COMPL_DATE ,
NOTIFICATION_NO ,
ACTIVITY_CODE ,
ACTIVITY_NAME ,
SHORT_DESCR ,
CONST_TYPE ,
UNIT
)t1
inner JOIN #temp t2
ON t1.EQUIPMENT_NO=t2.EQUIPMENT_NO
--AND t1.ACTIVITY_CODE=t2.ACTIVITY_CODE
AND t1.COMPL_DATE>t2.COMPL_DATE
GROUP BY t1.SAP_FL,
t1.EQUIPMENT_NO ,
t1.COMPL_DATE,
t1.NOTIFICATION_NO,
t1.ACTIVITY_CODE,
t1.ACTIVITY_NAME,
t1.TOTAL_COUNT,
t1.SHORT_DESCR,
t1.CONST_TYPE,
t1.UNIT
-------------------------------------------------------------------------------
output-pls copy this output rows in excel sheet so that u can understand
---------------------------------------------------------------

SAP_FL_ST SAP_FL_ED EQUIPMENT_NO_ST COMPL_DATE_ST COMPL_DATE_ED SHORT_DESCR_ST SHORT_DESCR_ED NOTIFICATION_NO_ST NOTIFICATION_NO_ED ACTIVITY_CODE_ST ACTIVITY_CODE_ED ACTIVITY_NAME_ST ACTIVITY_NAME_ED TOTAL_COUNT_ED TOTAL_COUNT_ST TOTAL_COUNT_DIFFERENCE CONST_TYPE_ST CONST_TYPE_ED UNIT
1022-A302400-414601-00801 1022-F716400-414601-00801 50030511 11/24/2008 11/26/2008 Anvil Roll Assembly, 7.50 wide Anvil Roll Assembly, 7.50 wide 502646226 502779252 LUBE SHRP Sharpened-Grind Sharpened-Grind 56659 56659 0 1200847 1200847 EA
1022-F708400-414601-00801 1022-F716400-414601-00801 50030511 9/22/2008 11/24/2008 Anvil Roll Assembly, 7.50 wide Anvil Roll Assembly, 7.50 wide 502646226 502319764 SHRP LUBE Sharpened-Grind Lubricated 56659 56659 0 1200847 1200847 EA
1022-F716400-414601-00801 1022-F708400-414601-00801 50030511 7/22/2008 9/22/2008 Anvil Roll Assembly, 7.50 wide Anvil Roll Assembly, 7.50 wide 502166642 502646226 LUBE SHRP Lubricated Sharpened-Grind 1 56659 56658 1200847 1200847 EA

1022-A302400-414601-00801 1022-F708400-414601-00801 50030512 50030512 2008-11-24 00:00:00.000 2008-12-26 00:00:00.000 Anvil Roll Assembly- 7.50 wide Anvil Roll Assembly- 7.50 wide 502319764 502779252 LUBE FABR Fabricated Lubricated 56659 56659 0 1200847 1200847 EA

----------------------------------------------------------------------------
now this is correct one pls check-u posted the query given by u also

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources