Version Flag - 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 2005 Forums

Transact-SQL (2005)

Version Flag

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-04-29 : 18:30:16

Guys,

I have following scenario where I need to populate version flag based on the partyid, count and seq columns.

Here is it how it should populate for each new count of a partyid the max(seq) should be versioned as 1

For example

PARTY COUNT SEQ VERSION
_________________________________________________
100 1 1 1
101 1 1 0
101 1 2 1
101 2 1 0
101 2 2 0
101 2 3 1
102 3 1 1

Is there any way to populate this version flag.

Any sugggestions and inputs would help

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 02:27:03

[code]DECLARE @Sample TABLE (Party TINYINT, Count TINYINT, Seq TINYINT, Version TINYINT)

INSERT @Sample
SELECT 100, 1, 1, NULL UNION ALL
SELECT 101, 1, 1, NULL UNION ALL
SELECT 101, 1, 2, NULL UNION ALL
SELECT 101, 2, 1, NULL UNION ALL
SELECT 101, 2, 2, NULL UNION ALL
SELECT 101, 2, 3, NULL UNION ALL
SELECT 102, 3, 1, NULL

SELECT *
FROM @Sample

UPDATE s
SET s.Version = 1 - SIGN(RecID - 1)
FROM (
SELECT Version,
ROW_NUMBER() OVER (PARTITION BY Party, Count ORDER BY Seq DESC) AS RecID
FROM @Sample
) AS s

SELECT *
FROM @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-05-02 : 15:23:21

Thanks Peso

Based on version flag, I am also trying to populate an ID column with following values.

PARTY COUNT SEQ VERSION ID
_________________________________________________
100 1 1 1 1
101 1 1 0 2
101 1 2 1 2
101 2 1 0 3
101 2 2 0 3
101 2 3 1 3
102 3 1 1 4

But my procedure doesnt seem to do the trick since I am using version flag = 1 is there any to achieve this

create PROCEDURE [dbo].[ntest] AS
DECLARE @totalRecords INT
DECLARE @ctr INT, @key INT, @subID INT
SET @ctr = 1
--- get the last igrp ID
SELECT @key = 0
-- gets the total records
SELECT @totalRecords = MAX([ID]) from criminal_disposition_caa44040

WHILE @ctr <= @totalRecords
BEGIN
---- Get the SubID
SELECT @subID = VERSION
FROM criminal_disposition_caa44040
WHERE [ID] = @ctr
---- Increment the Key if SubID = 1
IF @subID = 1 SET @key = @key + 1

---- Updates the Table
UPDATE criminal_disposition_caa44040 SET iid = @key
WHERE [ID] = @ctr

--increment the counter
SET @ctr = @ctr + 1
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-02 : 16:26:31

[code]UPDATE s
SET s.Version = 1 - SIGN(s.RecID - 1),
s.ID = s.vID
FROM (
SELECT Version,
ROW_NUMBER() OVER (PARTITION BY Party, Count ORDER BY Seq DESC) AS RecID,
ID,
DENSE_RANK() OVER (ORDER BY Party, Count) AS vID
FROM @Sample
) AS s[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources