Problem with Serial No.- Need Help!

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)

Problem with Serial No.- Need Help!

Author

Topic

azmi
Starting Member

37 Posts

Posted - 2006-04-14 : 02:59:56

[code]
Hi guys, i need help on below sproc;

--EXEC TEST_SRL_NO 'HTHTIB','BP',1
ALTER PROCEDURE TEST_SRL_NO
@company_no glcompany,
@locn_no gllocn,
@lang_id cmn_langid
AS
BEGIN
SET NOCOUNT ON
DECLARE @COUNT_OF_CUST_NO INT,
@CNTRY_CNTR INT,
@CNTRY_REC INT,
@CNTRY_TMP CHAR(3),
@X INT,
@CNTRY_CNT INT,
@CUST_TMP cmn_cust_ven_code,
@CUST_CNTR INT,
@CUST_REC INT,
@C_SL_NO INT,
@X_CNTR INT,
@Y_REC INT,
@B1 INT,
@B2 INT

CREATE TABLE #DMP_REC
(
CNTRY_SL_NO INT,
CNTRY_CODE CHAR(3),
CUST_SL_NO INT,
CUST_CODE cmn_cust_ven_code,
ITEM_CODE IMSSTKNO
)
INSERT INTO #DMP_REC
SELECT 0,'MAL',0,'11000004','CC2002SBP160L' UNION
SELECT 0,'MAL',0,'11000005','LB1033FBB163H' UNION
SELECT 0,'MAL',0,'11000005','LB1033FBB162H' UNION
SELECT 0,'MAL',0,'11000006','LB1033WMB162H' UNION
SELECT 0,'MAL',0,'11000007','HB1001XXP500C' UNION
SELECT 0,'GHN',0,'19000029','HB9006XXC080C' UNION
SELECT 0,'GHN',0,'11000030','HB1001XXP500C' UNION
SELECT 0,'HKG',0,'19000135','HB9024XXS080C' UNION
SELECT 0,'INS',0,'19000040','HB9026XXS080C' UNION
SELECT 0,'INS',0,'19000043','HB9026XXS080C'

/*CREATE CNTRY TMP*/
CREATE TABLE #CNTRY_TMP
(
SL_NO INT,
CNTRY_CODE CHAR(3),
REC_CNT INT
)
INSERT INTO #CNTRY_TMP
SELECT DISTINCT 0,CNTRY_CODE ,COUNT(CUST_CODE)
FROM #DMP_REC
GROUP BY CNTRY_CODE

/*CREATE CUST DATA*/
CREATE TABLE #CUST_DATA
(
SL_NO INT,
NO INT,
CNTRY_CODE CHAR(3),
CUST_NO cmn_cust_ven_code,
CUST_CNT INT
)
INSERT INTO #CUST_DATA
SELECT DISTINCT 0,0,CNTRY_CODE,CUST_CODE,0 /*CONTAINS SAME CUST NO FOR EACH CNTRY_CODE*/
FROM #DMP_REC

--SELECT @COUNT_OF_CUST_NO
---------------
SELECT @CUST_REC = COUNT(*) FROM #CUST_DATA
SELECT @CUST_CNTR = 1
SET ROWCOUNT 1
/*UPDATE NO FOR #CUST_DATA */
WHILE (@CUST_CNTR <= @CUST_REC) /*1-9 REC*/
BEGIN
UPDATE #CUST_DATA
SET NO = @CUST_CNTR
WHERE NO = 0

SELECT @CUST_TMP = CUST_NO
FROM #CUST_DATA
WHERE NO = @CUST_CNTR

SELECT @CUST_CNTR = @CUST_CNTR + 1
END
SET ROWCOUNT 0
---------------
SELECT @CNTRY_REC = COUNT(*) FROM #CNTRY_TMP
SELECT @CNTRY_CNTR = 1
SET ROWCOUNT 1
WHILE (@CNTRY_CNTR <= @CNTRY_REC) /*NO(S) FO COUNTRY PROCESS : 1 - 4*/
BEGIN
/*UPDATE SL_NO FOR #CNTRY_TMP*/
UPDATE #CNTRY_TMP
SET SL_NO = @CNTRY_CNTR
WHERE SL_NO = 0

/*ASSIGN VAR FOR CNTRY_CODE BASED ON SL_NO : EX 1->GHN*/
SELECT @CNTRY_TMP = CNTRY_CODE
FROM #CNTRY_TMP
WHERE SL_NO = @CNTRY_CNTR

/*ASSIGN VAR FOR CNT CUST_NO FOR EACH CONTRY_CODE*/
SELECT @COUNT_OF_CUST_NO = COUNT(CUST_NO)
FROM #CUST_DATA
WHERE CNTRY_CODE = @CNTRY_TMP
GROUP BY CNTRY_CODE

/*UPDATE CNTRY SL_NO FOR #DMP_REC*/
UPDATE #DMP_REC
SET CNTRY_SL_NO = @CNTRY_CNTR
WHERE CNTRY_CODE = @CNTRY_TMP
AND CNTRY_SL_NO = 0

/*UPDATE CUST_CNT TO #CUST_DATA*/
UPDATE #CUST_DATA
SET CUST_CNT = @COUNT_OF_CUST_NO
WHERE CNTRY_CODE = @CNTRY_TMP

SELECT @B2 = REC_CNT
FROM #CNTRY_TMP
WHERE SL_NO = @CNTRY_CNTR

SELECT @B1 = 1
WHILE (@B1 <= @B2)
BEGIN
UPDATE #DMP_REC
SET CUST_SL_NO = @B1
WHERE CNTRY_CODE = @CNTRY_TMP
AND CUST_SL_NO = 0

SELECT @B1 = @B1 + 1
END
--SELECT @CNTRY_TMP,@COUNT_OF_CUST_NO
--SELECT @CNTRY_TMP '@CNTRY_TMP',@CNTRY_CNT '@CNTRY_CNT'
SELECT @X = 1
WHILE (@X <= @COUNT_OF_CUST_NO) /*FOR TOTAL OF CUST COUNT FOR EACH COUNTRY - REMOVE DUPLICATE*/
BEGIN
/*UPDATE #CUST_DATA FOR CNTRY_CODE SL_NO*/
UPDATE #CUST_DATA
SET SL_NO = @CNTRY_CNTR
WHERE CNTRY_CODE = @CNTRY_TMP
AND SL_NO = 0

/*UPDATE CNTRY SL_NO FOR #DMP_REC*/
UPDATE #DMP_REC
SET CNTRY_SL_NO = @CNTRY_CNTR
WHERE CNTRY_CODE = @CNTRY_TMP
AND CNTRY_SL_NO = 0

--SELECT @Y_REC '@Y_REC'
SELECT @X = @X + 1
END
SELECT @X = 1

SELECT @CNTRY_CNTR = @CNTRY_CNTR + 1
END
SET ROWCOUNT 0

/*OUTPUT RESULT*/
-- SELECT * FROM #CNTRY_TMP
-- SELECT * FROM #CUST_DATA
SELECT * FROM #DMP_REC

/*PERFORM CLEAN UP*/
DROP TABLE #DMP_REC

END /*MAIN SPROC*/

HERE IS OUTPUT ON ABOVE SPROC:
CNTRY_SL_NO CNTRY_CODE CUST_SL_NO CUST_CODE ITEM_CODE
----------- ---------- ----------- ------------------ ----------------
1 GHN 1 11000030 HB1001XXP500C
1 GHN 2 19000029 HB9006XXC080C
2 HKG 1 19000135 HB9024XXS080C
3 INS 1 19000040 HB9026XXS080C
3 INS 2 19000043 HB9026XXS080C
4 MAL 1 11000004 CC2002SBP160L
4 MAL 2 11000005 LB1033FBB162H
4 MAL 3 11000005 LB1033FBB163H
4 MAL 4 11000006 LB1033WMB162H
4 MAL 5 11000007 HB1001XXP500C

REQUIRED OUTPUT HERE :
CNTRY_SL_NO CNTRY_CODE CUST_SL_NO CUST_CODE ITEM_CODE
----------- ---------- ----------- ------------------ ----------------
1 GHN 1 11000030 HB1001XXP500C
1 GHN 2 19000029 HB9006XXC080C
2 HKG 1 19000135 HB9024XXS080C
3 INS 1 19000040 HB9026XXS080C
3 INS 2 19000043 HB9026XXS080C
4 MAL 1 11000004 CC2002SBP160L
4 MAL 2 11000005 LB1033FBB162H
4 MAL 2 11000005 LB1033FBB163H
4 MAL 3 11000006 LB1033WMB162H
4 MAL 4 11000007 HB1001XXP500C

Referred on green colour, I got problem how to update same CUST_SL_NO if there is
1 CNTRY_CODE containts same CUST_CODE.The red colour is the correct one and that is what i need.
Or is there is any easiest way to do this. Thanks.
[/code]

azmi
Starting Member

37 Posts

Posted - 2006-04-16 : 21:53:17

anyone can help me, your help is much appreciate. Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-16 : 22:18:28

Use the following codes to update the #DMP_REC instead of the while loop

	update	t
		set	CNTRY_SL_NO = (select count(distinct x.CNTRY_CODE) from #DMP_REC x where x.CNTRY_CODE <= t.CNTRY_CODE)
	from	#DMP_REC t

	update	t
		set	CUST_SL_NO = (select count(distinct x.CUST_CODE) from #DMP_REC x where x.CNTRY_CODE = t.CNTRY_CODE and x.CUST_CODE <= t.CUST_CODE)
	from	#DMP_REC t

azmi
Starting Member

37 Posts

Posted - 2006-04-16 : 23:20:38

Thank you very much khtan,i appreaciate your help. Well the code is very simple and it's work perfectly.I'll never thinks about that. Once agains thanks a lots...

azmi
Starting Member

37 Posts

Posted - 2006-04-17 : 00:31:00

khtan i have a doubt here, based on code SQL will automatically sort by CNTRY_CODE. How do i maintant sort order of the CNTRY_CODE as same as INSERT data at the first proc, means CNTRY_CODE 'MAL' will come first then follow other CNTRY_CODE don't bother. thanks...


--EXEC TEST_SRL_NO_2ND 'HTHTIB','BP',1

ALTER PROCEDURE TEST_SRL_NO_2ND
	@company_no             glcompany,
	@locn_no		gllocn,
	@lang_id		cmn_langid
AS
BEGIN 
	SET NOCOUNT ON

	CREATE TABLE #DMP_REC
	(
		CNTRY_SL_NO	INT,
		CNTRY_CODE	CHAR(3),
		CUST_SL_NO	INT,
		CUST_CODE	cmn_cust_ven_code,
		ITEM_CODE	IMSSTKNO
	)
	INSERT INTO #DMP_REC
	SELECT 0,'MAL',0,'11000004','CC2002SBP160L'	UNION 
	SELECT 0,'MAL',0,'11000005','LB1033FBB163H'	UNION 
	SELECT 0,'MAL',0,'11000005','LB1033FBB162H'	UNION 
	SELECT 0,'MAL',0,'11000006','LB1033WMB162H'	UNION 
	SELECT 0,'MAL',0,'11000007','HB1001XXP500C'	UNION 
	SELECT 0,'GHN',0,'19000029','HB9006XXC080C'	UNION 
	SELECT 0,'GHN',0,'11000030','HB1001XXP500C'	UNION 
	SELECT 0,'HKG',0,'19000135','HB9024XXS080C'	UNION 
	SELECT 0,'INS',0,'19000040','HB9026XXS080C'	UNION 
	SELECT 0,'INS',0,'19000043','HB9026XXS080C'	
	UPDATE	t
	SET	CNTRY_SL_NO = (SELECT COUNT(DISTINCT x.CNTRY_CODE) FROM #DMP_REC x WHERE x.CNTRY_CODE <= t.CNTRY_CODE)
	FROM	#DMP_REC t

	UPDATE	t
	SET	CUST_SL_NO = (SELECT COUNT(DISTINCT x.CUST_CODE) FROM #DMP_REC x WHERE x.CNTRY_CODE = t.CNTRY_CODE AND x.CUST_CODE <= t.CUST_CODE)
	FROM	#DMP_REC t

	/*OUTPUT RESULT*/
	SELECT * FROM #DMP_REC

	/*PERFORM CLEAN UP*/
	DROP TABLE #DMP_REC

END /*MAIN SPROC*/

/*
OUTPUT LIKE THIS...
CNTRY_SL_NO CNTRY_CODE CUST_SL_NO  CUST_CODE          ITEM_CODE        
----------- ---------- ----------- ------------------ ---------------- 
1           MAL        1           11000004           CC2002SBP160L   
1           MAL        2           11000005           LB1033FBB162H   
1           MAL        2           11000005           LB1033FBB163H   
1           MAL        3           11000006           LB1033WMB162H   
1           MAL        4           11000007           HB1001XXP500C   
2           GHN        1           11000030           HB1001XXP500C   
2           GHN        2           19000029           HB9006XXC080C   
3           HKG        1           19000135           HB9024XXS080C   
4           INS        1           19000040           HB9026XXS080C   
4           INS        2           19000043           HB9026XXS080C   
*/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-17 : 00:41:30

try this

	UPDATE	t
	SET	CNTRY_SL_NO = (SELECT COUNT(DISTINCT x.CNTRY_CODE) FROM #DMP_REC x WHERE x.CNTRY_CODE >= t.CNTRY_CODE)
	FROM	#DMP_REC t

azmi
Starting Member

37 Posts

Posted - 2006-04-17 : 00:59:24

ohh like that,just change the '>=' operation. Now i know that for what?..Thanks agains khtan..

azmi
Starting Member

37 Posts

Posted - 2006-04-17 : 02:27:50

Sorry I'am again...khtan,i had tested the code. For my case, it's is valid if i got last CNTRYC_CODE is 'MAL' sort in alphabetical order. If there is others CONTRY_CODE like 'NEZ' or 'YMN' it's is not valid anymore for my case. How can i fixed CNTRY_CODE = 'MAL' IS ALWAYS at the top means CNTRY_SL_NO for 'MAL' is equal to 1.


CNTRY_SL_NO CNTRY_CODE CUST_SL_NO  CUST_CODE          ITEM_CODE        FLAG 
----------- ---------- ----------- ------------------ ---------------- ---- 
6           GHN        1           11000030           HB1001XXP500C    B
6           GHN        2           19000029           HB9006XXC080C    B
5           HKG        1           19000135           HB9024XXS080C    B
4           INS        1           19000040           HB9026XXS080C    B
4           INS        2           19000043           HB9026XXS080C    B
3           MAL        1           11000004           CC2002SBP160L    A
3           MAL        2           11000005           LB1033FBB162H    A
3           MAL        2           11000005           LB1033FBB163H    A
3           MAL        3           11000006           LB1033WMB162H    A
3           MAL        4           11000007           HB1001XXP500C    A
2           NEZ        1           19000040           HB9026XXS081C    B
1           YMN        1           19000040           HB9026XXS082C    B

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-17 : 02:39:35

Try this..

	UPDATE	t
	SET	CNTRY_SL_NO = (SELECT COUNT(DISTINCT x.CNTRY_CODE) FROM #DMP_REC x 
				WHERE   (case when x.CNTRY_CODE = 'MAL' then '1' else '0' end) + x.CNTRY_CODE >= 
					(case when t.CNTRY_CODE = 'MAL' then '1' else '0' end) + t.CNTRY_CODE)
	FROM	#DMP_REC t

azmi
Starting Member

37 Posts

Posted - 2006-04-17 : 06:21:31

Yes..that what my case all about. Anywhere thanks agains khtan cause helping me to solve my problem. Have a nice day...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources