Row sequencing - 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)

Row sequencing

Author

Topic

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2012-04-03 : 16:39:51

Sample Data

declare @t table (polnum char(8), eff_date int, trx_code char(2), cov_seq char(2))
insert @t
          select 'L1234567','20120401', 'EA','01'
union all select 'L1234567','20120401', 'GS','03'
union all select 'L1234567','20120401', 'GS','04'
union all select 'L1234567','20120401', 'FA','01'
union all select 'L1234567','20120401', 'NA','01'
union all select 'L1234567','20120401', 'FA','03'
union all select 'L1234567','20120401', 'NA','03'
union all select 'L1234567','20120401', 'FA','04'
union all select 'L1234567','20120401', 'NA','04'
union all select 'L1234567','20120402', 'NA','01'
union all select 'L1234567','20120402', 'NA','03'
union all select 'L1234567','20120402', 'NA','04'
union all select 'L1234567','20120403', 'NA','01'
union all select 'L1234567','20120403', 'NA','03'
union all select 'L1234567','20120403', 'NA','04'


union all select 'L9999999','20120401', 'GS','02'
union all select 'L9999999','20120401', 'EA','01'
union all select 'L9999999','20120401', 'NA','02'
union all select 'L9999999','20120401', 'FA','01'
union all select 'L9999999','20120401', 'NA','01'
union all select 'L9999999','20120401', 'FA','02'

here's what I have so far

SELECT RANK() OVER (
PARTITION BY polnum ORDER BY
eff_date
,CASE WHEN trx_code = 'EA' THEN -2
	ELSE CASE WHEN trx_code = 'GS' THEN -1
			ELSE 0			
			END
	END
, cov_seq
, CASE WHEN trx_code = 'FA' THEN -2
		ELSE CASE WHEN trx_code = 'NA' THEN -1
				ELSE 0
				END
		END
) AS [new_admin_seq]
, *
FROM @t

Expected Results

1	L1234567	20120401	EA	01
2	L1234567	20120401	GS	03
3	L1234567	20120401	GS	04
4	L1234567	20120401	FA	01
4	L1234567	20120401	NA	01
6	L1234567	20120401	FA	03
6	L1234567	20120401	NA	03
8	L1234567	20120401	FA	04
8	L1234567	20120401	NA	04
10	L1234567	20120402	NA	01
11	L1234567	20120402	NA	03
12	L1234567	20120402	NA	04
13	L1234567	20120403	NA	01
14	L1234567	20120403	NA	03
15	L1234567	20120403	NA	04
1	L9999999	20120401	EA	01
2	L9999999	20120401	GS	02
3	L9999999	20120401	FA	01
3	L9999999	20120401	NA	01
5	L9999999	20120401	FA	02
5	L9999999	20120401	NA	02

Logic -

The EA record always goes first. Followed by the GS record (ordered by the cov_seq). Followed by a pair of FA and NA records (again ordered based on cov_seq and for the same effdate as EA and GS). Followed by all other NAs (or any other trx_code) for the other effdates (ordered by effdate and cov_seq)

My query gives this

new_admin_seq        polnum   eff_date    trx_code cov_seq
-------------------- -------- ----------- -------- -------
1                    L1234567 20120401    EA       01
2                    L1234567 20120401    GS       03
3                    L1234567 20120401    GS       04
4                    L1234567 20120401    FA       01
5                    L1234567 20120401    NA       01
6                    L1234567 20120401    FA       03
7                    L1234567 20120401    NA       03
8                    L1234567 20120401    FA       04
9                    L1234567 20120401    NA       04
10                   L1234567 20120402    NA       01
11                   L1234567 20120402    NA       03
12                   L1234567 20120402    NA       04
13                   L1234567 20120403    NA       01
14                   L1234567 20120403    NA       03
15                   L1234567 20120403    NA       04
1                    L9999999 20120401    EA       01
2                    L9999999 20120401    GS       02
3                    L9999999 20120401    FA       01
4                    L9999999 20120401    NA       01
5                    L9999999 20120401    FA       02
6                    L9999999 20120401    NA       02

I still need to get the same sequence # for the FA and NA pairs as seen in the expected result.

Been a while since I worked on SQL. Any help is appreciated.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2012-04-03 : 18:48:21

Think this might work.

Hope someone will come up with a better way to do it.

SELECT RANK() OVER (
PARTITION BY polnum ORDER BY
eff_date
,CASE WHEN trx_code = 'EA' THEN -2
	ELSE CASE WHEN trx_code = 'GS' THEN -1
			ELSE 0			
			END
	END
, cov_seq
, CASE WHEN trx_code IN ('FA','NA') THEN -1
		ELSE 0
		END
) AS [new_admin_seq]
, *
FROM @t
ORDER BY polnum, [new_admin_seq], trx_code

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-04 : 20:58:37

[code]
declare @t table (polnum char(8), eff_date int, trx_code char(2), cov_seq char(2))
insert @t
select 'L1234567','20120401', 'EA','01'
union all select 'L1234567','20120401', 'GS','03'
union all select 'L1234567','20120401', 'GS','04'
union all select 'L1234567','20120401', 'FA','01'
union all select 'L1234567','20120401', 'NA','01'
union all select 'L1234567','20120401', 'FA','03'
union all select 'L1234567','20120401', 'NA','03'
union all select 'L1234567','20120401', 'FA','04'
union all select 'L1234567','20120401', 'NA','04'
union all select 'L1234567','20120402', 'NA','01'
union all select 'L1234567','20120402', 'NA','03'
union all select 'L1234567','20120402', 'NA','04'
union all select 'L1234567','20120403', 'NA','01'
union all select 'L1234567','20120403', 'NA','03'
union all select 'L1234567','20120403', 'NA','04'

union all select 'L9999999','20120401', 'GS','02'
union all select 'L9999999','20120401', 'EA','01'
union all select 'L9999999','20120401', 'NA','02'
union all select 'L9999999','20120401', 'FA','01'
union all select 'L9999999','20120401', 'NA','01'
union all select 'L9999999','20120401', 'FA','02'

select rank() over (partition by polnum
order by case when trx_code in ('FA','NA') then 1
else 0 end,eff_date,cov_seq) as rn,
* from @t

output
------------------------------------
rn polnum eff_date trx_code cov_seq
1 L1234567 20120401 EA 01
2 L1234567 20120401 GS 03
3 L1234567 20120401 GS 04
4 L1234567 20120401 FA 01
4 L1234567 20120401 NA 01
6 L1234567 20120401 FA 03
6 L1234567 20120401 NA 03
8 L1234567 20120401 FA 04
8 L1234567 20120401 NA 04
10 L1234567 20120402 NA 01
11 L1234567 20120402 NA 03
12 L1234567 20120402 NA 04
13 L1234567 20120403 NA 01
14 L1234567 20120403 NA 03
15 L1234567 20120403 NA 04
1 L9999999 20120401 EA 01
2 L9999999 20120401 GS 02
3 L9999999 20120401 FA 01
3 L9999999 20120401 NA 01
5 L9999999 20120401 FA 02
5 L9999999 20120401 NA 02

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources