Need to find gaps in a sequence which increments b

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

Need to find gaps in a sequence which increments b

Author

Topic

ezapa001
Starting Member

1 Post

Posted - 2014-09-10 : 15:25:18

Hi Friends,

I'm trying to write a query to validate the data.

Here is the scenario:
1. The table has Three columns 1.ID, 2.Sqno, 3. Adj
2. The values for adj are (0,1,2)

Case1: The Sqno should start at '001000' for adj in (0,2) and increment by 2, i.e the next sqno would be '001002' and '001004' so on.
Case2: The sqno should start at '001001' for adj in (1) and increment by 2 i.e the next sqno would be '001003' and '001005' so on.

Finally when you do order by sqno and group by ID it will be a running sqno.

ID Sqno Adj
123A 001000 0
123A 001001 1
123A 001002 2
123A 001003 1
123A 001004 2
123A 001005 1
123A 001006 0
123A 001007 1
123A 001008 2
123A 001009 1
.......

Can you please help me write a query that can validate this scenario.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-09-10 : 16:43:46

Try this:

select id
      ,998+rn*2+case when adj=1 then 1 else 0 end as sqno
      ,adj
  from (select id
              ,adj
              ,row_number() over(partition by id,case when adj=1 then 0 else 1 end) as rn
          from yourtable
       ) as a
 order by id
         ,sqno

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-09-11 : 00:50:40

SELECT ID, SeqNo, Adj, 998 + (Adj % 2) + 2 * ROW_NUMBER() OVER (PARTITION BY ID, Adj % 2 ORDER BY SqNo) AS NewSqNo
FROM dbo.Table1

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources