August 16, 2012 at 12:50 am
Hello All,
I have one table which have one field sr_no. in this field data up to 1.to ..100.
In this if i delete some record then sr_No not Maritain.
I want to find missing number and update sr_No :
like i am having column value of sr_no is (1,3,5,6,7,9)
i wnt o/p like (1,2,3,4,5,6)
with one sp. (in this sr_No.3 convert in to 2 and sr_No:5 convert in to 3 ,and sr_No:6 convert into 4..... )
August 16, 2012 at 2:39 am
kishor.patgir (8/16/2012)
Hello All,I have one table which have one field sr_no. in this field data up to 1.to ..100.
In this if i delete some record then sr_No not Maritain.
I want to find missing number and update sr_No :
like i am having column value of sr_no is (1,3,5,6,7,9)
i wnt o/p like (1,2,3,4,5,6)
with one sp. (in this sr_No.3 convert in to 2 and sr_No:5 convert in to 3 ,and sr_No:6 convert into 4..... )
Why not use a view to generate the number dynamically?
August 16, 2012 at 2:52 am
Is SR_NO used as a foreign key to any other tables? Either through physical foreign keys or via application inserting into table and getting the SR_NO at insertion time?
What would happen if you needed to roll back the delete and you had already updated the SR_NO's. Do you re add SR_NO 2 in as SR_NO 2 even though SR_NO 3 is now actually SR_NO 2
August 16, 2012 at 4:21 am
sr_no v_Dae Ref_Ac_ID debit credit
12012-03-01 125 2000 NULL
12012-03-01 125 NULL 2000
22012-03-02 168 3000 NULL
22012-03-02 168 NULL 3000
32012-03-01 125 NULL 2000
32012-03-02 168 6000 NULL
42012-03-02 168 NULL 3000
42012-03-02 168 6000 NULL
62012-03-02 168 NULL 3000
62012-03-01 125 NULL 2000
72012-03-02 168 9000 NULL
72012-03-02 168 NULL 9000
82012-03-02 168 NULL 3000
82012-03-02 168 3000 NULL
102012-03-02 168 NULL 3000
102012-03-01 125 NULL 2000
132012-03-02 168 3000 NULL
132012-03-02 168 NULL 3000
***
***My Table structure is above(double entry accounting),
If here sr_no not maintain like(1,1,2,2,3,3,5,5,6,6,7,7,9,9.. continue)
--then I want to arrange (1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9...) here sr_no 5 convert in to 4 and 6 convert in to 5 ,7 convert to 6, one bye one,..! I not want missing gap betn numbers..
August 16, 2012 at 4:24 am
Itzik Ben Gan has a number of examples on how to find islands and gaps in sets of numbers in his book Inside T-SQL Querying. I'd suggest picking up a copy.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 16, 2012 at 4:26 am
It would be polite of you to answer mine and Anthony's questions - they are there for a reason: we are trying to understand your reasons for doing this, whether you might be setting yourself up for problems and whether there may be better solution.
August 16, 2012 at 5:07 am
ok..I Solve It finally..
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SortingDailySRNo]
@pPeriodID INT
,@pFirmID INT
---,@pTodayDate datetime
AS
BEGIN
SET NOCOUNT ON;
DECLARE @cnt INT
DECLARE @Daily_Sr_No int
Declare @ReturnValue as varchar(max);
declare @minsr int
SET @ReturnValue = '';
DECLARE dbCursor CURSOR FOR
SELECT distinct Daily_Sr_No
From FAS_Transaction
where sr_no=2
OPEN dbCursor
FETCH NEXT FROM dbCursor
INTO @Daily_Sr_No
BEGIN TRANSACTION
BEGIN TRY
SET @cnt = 1
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE FAS_Transaction
SET Daily_Sr_No=@cnt
WHERE Daily_Sr_No=@Daily_Sr_No
print @Daily_Sr_No
FETCH NEXT FROM dbCursor
INTO @Daily_Sr_No
SET @cnt = @cnt + 1
END
CLOSE dbCursor;
DEALLOCATE dbCursor;
print @cnt
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
END
I use Count number of row an update it (here I use Sr_No=1 for debit and Sr_No=2 for credit and i use another field daily_Sr_No and update rows...)..
thanks
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply