nested cursor help needed - 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

nested cursor help needed

Author

Topic

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-01-30 : 04:52:55

hi I have one table #tb1 like this

CREATE TABLE #tb1
(
EQUIPMENT_NUMBER varchar(18),
NOTIFICATION_NUMBER varchar(12),
ACTIVITY_CODE varchar(10),
reading int
)
delete from #tb1
insert into #tb1 values ('50001721','502874581','CLEN',100)
insert into #tb1 values ('50001721','502874582','CLEN',200)
insert into #tb1 values ('50001722','44444588','REPR',300)
insert into #tb1 values ('50001722','44444589','REPR',400)
--select * from #tb1
--------------------------------------------------------------------------

CREATE TABLE #tb2
(
EQUIPMENT_NUMBER_ST varchar(18),
EQUIPMENT_NUMBER_END varchar(18),

NOTIFICATION_NUMBER_ST varchar(12),
NOTIFICATION_NUMBER_END varchar(12),

ACTIVITY_CODE_ST varchar(10),
ACTIVITY_CODE_END varchar(10),
reading1 int,
reading2 int,
diff int
)
delete from #tb2
----------------------------------------------------------------
DECLARE
@eqno1 varchar(18),
@notif1 varchar(12),
@code1 varchar(10),
@reading1 int,

@eqno2 varchar(18),
@notif2 varchar(12),
@code2 varchar(10),
@reading2 int,

@diff int

declare c1 cursor for
select
EQUIPMENT_NUMBER ,
NOTIFICATION_NUMBER ,
ACTIVITY_CODE ,
reading
from #tb1
group by
EQUIPMENT_NUMBER ,
NOTIFICATION_NUMBER ,
ACTIVITY_CODE ,
reading

open c1
fetch next from c1 into
@eqno1 ,
@notif1 ,
@code1 ,
@reading1

--print '------start---'
--print @eqno1
--print @notif1
--print @code1
--print @reading1

while (@@fetch_status=0)
begin
-- INNER CURSOR declaration
-- SQL Nested Cursor
declare c2 cursor for
select
EQUIPMENT_NUMBER ,
NOTIFICATION_NUMBER ,
ACTIVITY_CODE ,
reading
from #tb1
group by
EQUIPMENT_NUMBER ,
NOTIFICATION_NUMBER ,
ACTIVITY_CODE ,
reading

open c2
fetch next from c2 into
@eqno2 ,
@notif2 ,
@code2 ,
@reading2

while (@@fetch_status=0)
begin

if @eqno2 = @eqno1 and @code1 = @code2
begin
set @diff = @reading1 - @reading2
end

fetch next from c2 into
@eqno2 ,
@notif2 ,
@code2 ,
@reading2

end -- inner while
fetch next from c2 into
@eqno2 ,
@notif2 ,
@code2 ,
@reading2
close c2
deallocate c2
insert into #tb2 values
(@eqno1 ,
@eqno2 ,
@notif1 ,
@notif2 ,
@code1 ,
@code2 ,
@reading1 ,
@reading2 ,
@diff
)

fetch next from c1 into
@eqno1 ,
@notif1 ,
@code1 ,
@reading1
end -- outer while

close c1
deallocate c1

select * from #tb1
select * from #tb2
---------------------------------------
EquipNo_Start equipno_end Notif_Start Notif_end start_code code_end
50001721 50001721 502874581 502874582 CLEN CLEN
start_reading end_reading difference
100 200 -100

the expected out put must be two rows as below

EquipNo_Start equipno_end Notif_Start Notif_end start_code code_end
50001722 50001722 502874581 502874582 CLEN CLEN
start_reading end_reading difference
100 200 -100
EquipNo_Start equipno_end Notif_Start Notif_end start_code code_end
50001722 50001722 44444588 44444589 REPR REPR
start_reading end_reading difference
300 400 -100

but i m getting 16 rows -as cursor is iterating 16 times so
can anybody tell -where it's goin wrong

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-30 : 05:10:44

Are you using SQL Server 2005?

E 12°55'05.63"
N 56°04'39.26"

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-01-30 : 05:19:13

i m using sql server 2000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-30 : 05:19:21

Look! No cursors at all with SQL Server 2005...

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		Equipment_Number VARCHAR(18),
		Notification_Number VARCHAR(12),
		Activity_Code VARCHAR(10),
		Reading INT
	)

INSERT	@Sample
SELECT	'50001721', '502874581', 'CLEN', 100 UNION ALL
SELECT	'50001721', '502874582', 'CLEN', 200 UNION ALL
SELECT	'50001722', '44444588', 'REPR', 300 UNION ALL
SELECT	'50001722', '44444589', 'REPR', 400

-- Show the result
SELECT		d.Equipment_Number AS Equipment_Number_ST, 
		d.Equipment_Number AS Equipment_Number_END,
		MAX(CASE WHEN d.recID = 1 THEN d.Notification_Number ELSE NULL END) AS Notification_Number_ST,
		MAX(CASE WHEN d.recID = 2 THEN d.Notification_Number ELSE NULL END) AS Notification_Number_END,
		MAX(CASE WHEN d.recID = 1 THEN d.Activity_Code ELSE NULL END) AS Activity_Code_ST,
		MAX(CASE WHEN d.recID = 2 THEN d.Activity_Code ELSE NULL END) AS Activity_Code_END,
		MAX(CASE WHEN d.recID = 1 THEN d.Reading ELSE NULL END) AS Reading_ST,
		MAX(CASE WHEN d.recID = 2 THEN d.Reading ELSE NULL END) AS Reading_END,
		MAX(CASE WHEN d.recID = 2 THEN d.Reading ELSE 0 END)
		- MAX(CASE WHEN d.recID = 1 THEN d.Reading ELSE 0 END) AS Diff
FROM		(
			SELECT	Equipment_Number,
				Notification_Number,
				Activity_Code,
				Reading,
				ROW_NUMBER() OVER (PARTITION BY Equipment_Number ORDER BY Notification_Number) AS recID
			FROM	@Sample
		) AS d
GROUP BY	d.Equipment_Number
ORDER BY	d.Equipment_Number

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-30 : 05:21:18

SQL Server 2000? Still no cursors...

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		Equipment_Number VARCHAR(18),
		Notification_Number VARCHAR(12),
		Activity_Code VARCHAR(10),
		Reading INT
	)

INSERT	@Sample
SELECT	'50001721', '502874581', 'CLEN', 100 UNION ALL
SELECT	'50001721', '502874582', 'CLEN', 200 UNION ALL
SELECT	'50001722', '44444588', 'REPR', 300 UNION ALL
SELECT	'50001722', '44444589', 'REPR', 400

-- Show the result
SELECT		d.Equipment_Number AS Equipment_Number_ST, 
		d.Equipment_Number AS Equipment_Number_END,
		MAX(CASE WHEN d.recID = 1 THEN d.Notification_Number ELSE NULL END) AS Notification_Number_ST,
		MAX(CASE WHEN d.recID = 2 THEN d.Notification_Number ELSE NULL END) AS Notification_Number_END,
		MAX(CASE WHEN d.recID = 1 THEN d.Activity_Code ELSE NULL END) AS Activity_Code_ST,
		MAX(CASE WHEN d.recID = 2 THEN d.Activity_Code ELSE NULL END) AS Activity_Code_END,
		MAX(CASE WHEN d.recID = 1 THEN d.Reading ELSE NULL END) AS Reading_ST,
		MAX(CASE WHEN d.recID = 2 THEN d.Reading ELSE NULL END) AS Reading_END,
		MAX(CASE WHEN d.recID = 2 THEN d.Reading ELSE 0 END)
		- MAX(CASE WHEN d.recID = 1 THEN d.Reading ELSE 0 END) AS Diff
FROM		(
			SELECT	s.Equipment_Number,
				s.Notification_Number,
				s.Activity_Code,
				s.Reading,
				(SELECT COUNT(*) FROM @Sample AS x WHERE x.Equipment_Number = s.Equipment_Number AND x.Notification_Number <= s.Notification_Number) AS recID
			FROM	@Sample AS s
		) AS d
GROUP BY	d.Equipment_Number
ORDER BY	d.Equipment_Number

E 12°55'05.63"
N 56°04'39.26"

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-01-30 : 05:25:03

hello peso
as i m using sql server 2000 so row_number function is not getting recognised into sql server 2000 and i m using sql server 2000
is there any alternative for that

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-01-30 : 05:29:06

SAP FL Eq no Eq Desc Date Notification Number Activity Code Activity Name Reading Construction Type Unit Priority ID
1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 12/15/2008 502874589 CLEN Cleaned 218242 80296971 EA 11
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 7/17/2008 502552391 CLEN Cleaned 208032 80296971 EA 11
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 10/2/2007 502023968 CLEN Cleaned 208032 80296971 EA 11
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-F306400-411401-00801 50001722 Module, Cutting Fluff Ovrnite/wing 295mm 12/15/2008 502874589 CLEN Cleaned 218242 80296971 EA 11
1023-F306400-411401-00801 50001722 Module, Cutting Fluff Ovrnite/wing 295mm 10/4/2008 502737991 REPR Repaired 218242 80296971 EA 3
1023-F306400-411401-00801 50001722 Module, Cutting Fluff Ovrnite/wing 295mm 8/29/2008 502675451 REPR Repaired 218242 80296971 EA 3
1023-F306400-411401-00801 50001722 Module, Cutting Fluff Ovrnite/wing 295mm 8/29/2008 502620150 REPR Repaired 218242 80296971 EA 3
1023-F306400-411401-00801 50001722 Module, Cutting Fluff Ovrnite/wing 295mm 7/17/2008 502552391 CLEN Cleaned 208032 80296971 EA 11
1023-F306400-411401-00801 50001722 Module, Cutting Fluff Ovrnite/wing 295mm 6/18/2008 502495333 REPR Repaired 208032 80296971 EA 3
1023-F306400-411401-00801 50001722 Module, Cutting Fluff Ovrnite/wing 295mm 10/2/2007 502023968 CLEN Cleaned 208032 80296971 EA 11
1023-F306400-411401-00801 50001722 Module, Cutting Fluff Ovrnite/wing 295mm 9/7/2007 501944796 REPR Repaired 197999 80296971 EA 3
1023-F306400-411401-00801 50001722 Module, Cutting Fluff Ovrnite/wing 295mm 9/23/2005 500648350 REPR Repaired 159277 80296971 EA 3
1023-F306400-411401-00801 50001722 Module, Cutting Fluff Ovrnite/wing 295mm 12/17/2004 500182407 REPR Repaired 118935 80296971 EA 3

hi peso thanks i m looking into the solution and i m trying to implement the sql given by u with above data

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-01-30 : 06:21:14

sap fl equi no equip desc completion date notification no activity code activity name total count construction type unit priority id

1023-A615400-443401-00901-00401 50001563 Ultrasonic stack #30 11/14/2005 500722482 REPR Repaired 70628 80159257 EA 3
1023-A614400-443401-00901-00501 50001563 Ultrasonic stack #30 4/15/2005 500357639 REPL Replaced 70404 80159257 EA 2
1023-A615400-443401-00901-00201 50001563 Ultrasonic stack #30 3/21/2005 500329094 REPR Repaired 70321 80159257 EA 3
1023-A615400-443401-00901-00301 50001563 Ultrasonic stack #30 2/25/2005 500303082 REPL Replaced 70321 80159257 EA 2
1023-A615400-443401-00901-00501 50001563 Ultrasonic stack #30 1/28/2005 500246885 REPL Replaced 70281 80159257 EA 2
1023-A615400-443401-00901-00501 50001563 Ultrasonic stack #30 1/8/2005 500213694 REPL Replaced 70281 80159257 EA 2
1023-A614400-443401-00901-00401 50001563 Ultrasonic stack #30 12/14/2004 500187336 REPR Repaired 70260 80159257 EA 3
1023-A614400-443401-00901-00301 50001563 Ultrasonic stack #30 11/29/2004 500154020 REPL Replaced 70260 80159257 EA 2
1023-A615400-443401-00901-07604 50001564 Ultrasonic stack #31 5/2/2008 502455118 REPR Repaired 70723 80159257 EA 3
1023-A615400-443401-00901-07604 50001564 Ultrasonic stack #31 4/30/2008 502452452 REPR Repaired 70723 80159257 EA 3
1023-A615400-443401-00901-07603 50001564 Ultrasonic stack #31 4/24/2008 502442263 REPR Repaired 70723 80159257 EA 3
1023-A615400-443401-00901-07603 50001564 Ultrasonic stack #31 3/8/2008 502350672 REPR Repaired 70723 80159257 EA 3
1023-A611400-443401-00901-00301 50001564 Ultrasonic stack #31 12/15/2007 502165226 REPR Repaired 70723 80159257 EA 3
1023-A611400-443401-00901-00301 50001564 Ultrasonic stack #31 10/11/2007 502065982 REPR Repaired 70723 80159257 EA 3
1023-A611400-443401-00901-00301 50001564 Ultrasonic stack #31 7/26/2007 501915490 REPR Repaired 70723 80159257 EA 3
1023-A615400-443401-00901-00501 50001564 Ultrasonic stack #31 11/14/2005 500728721 REPR Repaired 70556 80159257 EA 3
1023-A615400-443401-00901-00401 50001564 Ultrasonic stack #31 4/25/2005 500390187 REPL Replaced 70338 80159257 EA 2
1023-A615400-443401-00901-00301 50001564 Ultrasonic stack #31 2/8/2005 500267951 REPR Repaired 70338 80159257 EA 3
1023-A614400-443401-00901-00301 50001564 Ultrasonic stack #31 1/24/2005 500244560 REPR Repaired 70307 80159257 EA 3
1023-A611400-443401-00901-00601 50001564 Ultrasonic stack #31 1/17/2005 500229089 REPL Replaced 70307 80159257 EA 2

hi thanks i m checking it for above sample data

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-01-30 : 06:38:21

hi actually it fails for below sample data

CREATE TABLE #temp
(SAP_FUNCT varchar(40),
EQUIP_NO varchar(18),
SHORT_DESCR varchar(100),
COMPL_DATE datetime,
NOTIF_NO varchar(12),
ACTIV_CODE VARCHAR(10),
ACTIV_NAME VARCHAR(100),
TOTAL_COUNT float,
CONSTRUCTION_TYPE varchar(100),
UNIT varchar(50),
PriorityID int,
)
SELECT d.SAP_FUNCT_ as SAP_FL_ST,
d.SAP_FUNCT_ as SAP_FL_END,
d.EQUIP_NO AS Equipment_Number_ST,
d.EQUIP_NO AS Equipment_Number_END,
MAX(CASE WHEN d.recID = 1 THEN d.SHORT_DESCR ELSE NULL END ) as Short_Desc_ST,
MAX(CASE WHEN d.recID = 2 THEN d.SHORT_DESCR ELSE NULL END ) as Short_Desc_END,
MAX(CASE WHEN d.recID = 1 THEN d.COMPL_DATE ELSE NULL END )as Start_Date,
MAX(CASE WHEN d.recID = 2 THEN d.COMPL_DATE ELSE NULL END )as End_Date,
MAX(CASE WHEN d.recID = 1 THEN d.NOTIF_NUMB ELSE NULL END) AS Notification_Number_ST,
MAX(CASE WHEN d.recID = 2 THEN d.NOTIF_NUMB ELSE NULL END) AS Notification_Number_END,
MAX(CASE WHEN d.recID = 1 THEN d.ACTIV_CODE ELSE NULL END) AS Activity_Code_ST,
MAX(CASE WHEN d.recID = 2 THEN d.ACTIV_CODE ELSE NULL END) AS Activity_Code_END,
MAX(CASE WHEN d.recID = 1 THEN d.TOTAL_COUNT ELSE NULL END) AS Reading_ST,
MAX(CASE WHEN d.recID = 1 THEN d.ACTIV_NAME ELSE NULL END) AS Activity_Name_ST,
MAX(CASE WHEN d.recID = 2 THEN d.ACTIV_NAME ELSE NULL END) AS Activity_Name_END,
MAX(CASE WHEN d.recID = 2 THEN d.TOTAL_COUNT ELSE NULL END) AS Reading_END,
MAX(CASE WHEN d.recID = 2 THEN d.TOTAL_COUNT ELSE 0 END)
- MAX(CASE WHEN d.recID = 1 THEN d.TOTAL_COUNT ELSE 0 END) AS Diff,
MAX(CASE WHEN d.recID = 1 THEN d.CONST_TYPE ELSE NULL END) AS Construction_Type_ST,
MAX(CASE WHEN d.recID = 2 THEN d.CONST_TYPE ELSE NULL END) AS Construction_Type_End,
MAX(CASE WHEN d.recID = 1 THEN d.UNIT ELSE NULL END) AS UNIT

FROM (
SELECT
s.SAP_FUNCT ,
s.EQUIP_NUMB ,
s.SHORT_DESCR,
s.COMPL_DATE ,
s.NOTIF_NUMB ,
s.ACTIV_CODE,
s.ACTIV_NAME,
s.TOTAL_COUNT ,
s.CONST_TYPE,
s.UNIT,
(SELECT COUNT(*) FROM #temp AS x
WHERE x.EQUIP_NUMB = s.EQUIP_NUMB
AND x.ACTIV_CODE = s.ACTIV_CODE
order by COMPL_DATE desc
) AS recID
FROM #temp AS s
) AS d
GROUP BY SAP_FUNCT_,d.QUIP_NUMB
ORDER BY d.EQUIP_NUMB

sap fl equi no equip desc completion date notification no activity code activity name total count construction type unit priority id
1023-A615400-443401-00901-00401 50001563 Ultrasonic stack #30 11/14/2005 500722482 REPR Repaired 70628 80159257 EA 2
1023-A614400-443401-00901-00501 50001563 Ultrasonic stack #30 4/15/2005 500357639 REPL Replaced 70404 80159257 EA 3
1023-A615400-443401-00901-00201 50001563 Ultrasonic stack #30 3/21/2005 500329094 REPR Repaired 70321 80159257 EA 2
1023-A615400-443401-00901-00301 50001563 Ultrasonic stack #30 2/25/2005 500303082 REPL Replaced 70321 80159257 EA 2
1023-A615400-443401-00901-00501 50001563 Ultrasonic stack #30 1/28/2005 500246885 REPL Replaced 70281 80159257 EA 2
1023-A615400-443401-00901-00501 50001563 Ultrasonic stack #30 1/8/2005 500213694 REPL Replaced 70281 80159257 EA 3
1023-A614400-443401-00901-00401 50001563 Ultrasonic stack #30 12/14/2004 500187336 REPR Repaired 70260 80159257 EA 2
1023-A614400-443401-00901-00301 50001563 Ultrasonic stack #30 11/29/2004 500154020 REPL Replaced 70260 80159257 EA 3
1023-A615400-443401-00901-07604 50001564 Ultrasonic stack #31 5/2/2008 502455118 REPR Repaired 70723 80159257 EA 3
1023-A615400-443401-00901-07604 50001564 Ultrasonic stack #31 4/30/2008 502452452 REPR Repaired 70723 80159257 EA 3
1023-A615400-443401-00901-07603 50001564 Ultrasonic stack #31 4/24/2008 502442263 REPR Repaired 70723 80159257 EA 3
1023-A615400-443401-00901-07603 50001564 Ultrasonic stack #31 3/8/2008 502350672 REPR Repaired 70723 80159257 EA 3
1023-A611400-443401-00901-00301 50001564 Ultrasonic stack #31 12/15/2007 502165226 REPR Repaired 70723 80159257 EA 3
1023-A611400-443401-00901-00301 50001564 Ultrasonic stack #31 10/11/2007 502065982 REPR Repaired 70723 80159257 EA 3
1023-A611400-443401-00901-00301 50001564 Ultrasonic stack #31 7/26/2007 501915490 REPR Repaired 70723 80159257 EA 3
1023-A615400-443401-00901-00501 50001564 Ultrasonic stack #31 11/14/2005 500728721 REPR Repaired 70556 80159257 EA 2
1023-A615400-443401-00901-00401 50001564 Ultrasonic stack #31 4/25/2005 500390187 REPL Replaced 70338 80159257 EA 3
1023-A615400-443401-00901-00301 50001564 Ultrasonic stack #31 2/8/2005 500267951 REPR Repaired 70338 80159257 EA 3
1023-A614400-443401-00901-00301 50001564 Ultrasonic stack #31 1/24/2005 500244560 REPR Repaired 70307 80159257 EA 2
1023-A611400-443401-00901-00601 50001564 Ultrasonic stack #31 1/17/2005 500229089 REPL Replaced 70307 80159257 EA 3

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-30 : 06:44:29

Can you please post an example with insert statements in place, like I did earlier.
It makes thing SO much easier to work with.

E 12°55'05.63"
N 56°04'39.26"

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-01-30 : 09:10:52

hi peso please find the sql script
thanks in advance for u r co-operation

CREATE TABLE #temp
(SAP_FUNCT varchar(40),
EQUIP_NO varchar(18),
SHORT_DESCR varchar(100),
COMPL_DATE datetime,
NOTIF_NO varchar(12),
ACTIV_CODE VARCHAR(10),
ACTIV_NAME VARCHAR(100),
TOTAL_COUNT float,
CONSTRUCTION_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' , 'REPL' , 'Replaced' , 70404 , 80159257 , 'EA' , 3 )
insert into #temp values ( '1023-A615400-443401-00901-00201' ,50001563 , 'Ultrasonic stack #30 ' , '3/21/2005' , '500329094' , 'SHRP' , 'SHARPENED' , 70321 , 80159257 , 'EA' , 2 )

insert into #temp values ( '1023-A615400-443401-00901-07604' ,50001564 , 'Ultrasonic stack #31 ' , '5/2/2008' , '502455118' , 'REPR' , 'Repaired' , 70723 , 80159257 , 'EA' , 3 )
insert into #temp values ( '1023-A615400-443401-00901-07604' ,50001564 , 'Ultrasonic stack #31 ' , '4/30/2008' , '502452452' , 'REPR' , 'Repaired' , 70723 , 80159257 , 'EA' , 3 )
insert into #temp values ( '1023-A615400-443401-00901-07603' ,50001564 , 'Ultrasonic stack #31 ' , '4/24/2008' , '502442263' , 'REPR' , 'Repaired' , 70723 , 80159257 , 'EA' , 3 )
insert into #temp values ( '1023-A615400-443401-00901-07603' ,50001564 , 'Ultrasonic stack #31 ' , '3/8/2008' , '502350672' , 'REPL' , 'Replaced' , 70723 , 80159257 , 'EA' , 3 )

-----------------------------------------------------
output
-------------------------------------------------------
SAP FL_ST SAP FL_END Equip NO ST_date End_Date ST_Notif END_Notif ST_Code END_CODE ST_Activity_Name End_Activity_Name ST_Reading End_Reading Diff Short_Desc_ST Short_Desc_End Construction_Type_ST Construction_Type_END unit
1023-A615400-443401-00901-00401 1023-A614400-443401-00901-00501 50001563 11/14/2005 3/21/2005 500722482 500329094 REPR REPR Repaired Repaired 70628 70321 307 Ultrasonic stack #30 Ultrasonic stack #30 80159257 80159257 EA
1023-A615400-443401-00901-07604 1023-A615400-443401-00901-07604 50001564 5/2/2008 4/30/2008 502455118 502452452 REPR REPR Repaired Repaired 70723 70723 0 Ultrasonic stack #30 Ultrasonic stack #30 80159257 80159257 EA
1023-A615400-443401-00901-07604 1023-A615400-443401-00901-07603 50001564 4/30/2008 4/24/2008 502452452 502442263 REPR REPR Repaired Repaired 70723 70723 0 Ultrasonic stack #30 Ultrasonic stack #30 80159257 80159257 EA
1023-A615400-443401-00901-07604 1023-A615400-443401-00901-07603 50001564 5/2/2008 4/24/2008 502455118 502442263 REPR REPR Repaired Repaired 70723 70723 0 Ultrasonic stack #30 Ultrasonic stack #30 80159257 80159257 EA

can u please checl for this again
in output it will pick up data only when equipment number is matching and activity code is matching
e.g 50001563=50001563 and REPR=REPR and the rows are arranged date wise descending order in #temp.
max date wise it should calculate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 09:28:53

you sample data and output dont match, how did ST_Reading & End_Reading became REPR in first record?shouldnt it be REPR & REPL? also how did date become 3/21/2005? can you explain it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-30 : 09:40:23

I think he means that he wants first/oldest and last/newest records for each group of something.
The "group" definition has varied between post to post.

E 12°55'05.63"
N 56°04'39.26"

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-01-30 : 09:52:09

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' , 'REPL' , 'Replaced' , 70404 , 80159257 , 'EA' , 3 )
insert into #temp values ( '1023-A615400-443401-00901-00201' ,50001563 , 'Ultrasonic stack #30 ' , '3/21/2005' , '500329094' , 'REPR' , 'SHARPENED' , 70321 , 80159257 , 'EA' , 2 )

insert into #temp values ( '1023-A615400-443401-00901-07604' ,50001564 , 'Ultrasonic stack #31 ' , '5/2/2008' , '502455118' , 'REPR' , 'Repaired' , 70723 , 80159257 , 'EA' , 3 )
insert into #temp values ( '1023-A615400-443401-00901-07604' ,50001564 , 'Ultrasonic stack #31 ' , '4/30/2008' , '502452452' , 'REPR' , 'Repaired' , 70723 , 80159257 , 'EA' , 3 )
insert into #temp values ( '1023-A615400-443401-00901-07603' ,50001564 , 'Ultrasonic stack #31 ' , '4/24/2008' , '502442263' , 'REPR' , 'Repaired' , 70723 , 80159257 , 'EA' , 3 )
insert into #temp values ( '1023-A615400-443401-00901-07603' ,50001564 , 'Ultrasonic stack #31 ' , '3/8/2008' , '502350672' , 'REPL' , 'Replaced' , 70723 , 80159257 , 'EA' , 3 )

m sorry -see now for eq no 50001563 is matching 1st,3rd row
and for eq no 50001564 there 4 rows are (row no 4,5,6,7)thr where in row no 4,5,6 the code is matching so we need to take (4,5),(5,6)(4,6) row llike calculating difference for same equipment wise and code must match and date wise we should sort
but 7 th row is not matching so it will neglect it

any info reqd pls ask

thanks guys in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 09:58:19

[code]select t1.SAP_FUNCT,t2.SAP_FUNCT,t1.EQUIP_NO,t1.COMPL_DATE,t2.COMPL_DATE,...other fields
from Table t1
INNER JOIN Table t2
ON t1.EQUIP_NO=t2.EQUIP_NO
AND t1.ACTIV_CODE=t2.ACTIV_CODE
AND t1.COMPL_DATE>t2.COMPL_DATE
WHERE t1.ACTIV_CODE='REPR'
[/code]

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-01-30 : 10:12:22

actually visakh16 ur query is also giving result but it gives right now 3 rows only (which are for equip no 50001564 )and it's not calculating rows for 50001563 actually total rows must b 4

since for 50001564 --3rows+for 50001563-- 1

as in input data we can see for 50001563 there are first two rows are matching

ican u pls correct above query

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-01-30 : 10:13:41

peso can u please correct u r query for latest data which i posted

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 10:16:14

quote:
Originally posted by qutesanju

actually visakh16 ur query is also giving result but it gives right now 3 rows only (which are for equip no 50001564 )and it's not calculating rows for 50001563 actually total rows must b 4

since for 50001564 --3rows+for 50001563-- 1

as in input data we can see for 50001563 there are first two rows are matching

ican u pls correct above query

as per your posted sample data it should work. check if values posted for 50001563 are correct

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-01-30 : 10:30:59

Posted - 01/30/2009 : 09:52:09
--------------------------------------------------------------------------------

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' , 'Replaced' , 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 )

look for 50001563 i posted as above and right for which one row should flow as

SAP FL_ST SAP FL_END Equip NO ST_date End_Date ST_Notif END_Notif ST_Code END_CODE ST_Activity_Name End_Activity_Name ST_Reading End_Reading Diff Short_Desc_ST Short_Desc_End Construction_Type_ST Construction_Type_END unit
1023-A615400-443401-00901-00401 1023-A614400-443401-00901-00501 50001563 11/14/2005 3/21/2005 500722482 500329094 REPR REPR Repaired Repaired 70628 70321 307 Ultrasonic stack #30 Ultrasonic stack #30 80159257 80159257 EA

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-01-30 : 10:51:51

can any body has the solution for my prob pls
should I choose nested cursor or simple query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 12:03:45

quote:
Originally posted by qutesanju

Posted - 01/30/2009 : 09:52:09
--------------------------------------------------------------------------------

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' , 'Replaced' , 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 )

look for 50001563 i posted as above and right for which one row should flow as

SAP FL_ST SAP FL_END Equip NO ST_date End_Date ST_Notif END_Notif ST_Code END_CODE ST_Activity_Name End_Activity_Name ST_Reading End_Reading Diff Short_Desc_ST Short_Desc_End Construction_Type_ST Construction_Type_END unit
1023-A615400-443401-00901-00401 1023-A614400-443401-00901-00501 50001563 11/14/2005 3/21/2005 500722482 500329094 REPR REPR Repaired Repaired 70628 70321 307 Ultrasonic stack #30 Ultrasonic stack #30 80159257 80159257 EA

are you telling now its not returning?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources