Need help with sequencing

Question

Post reply

Need help with sequencing

surf4profit

SSC Rookie

Points: 27
More actions
January 23, 2015 at 11:53 am

#294731

I have a

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

surf4profit (1/23/2015)
I have a

Are we supposed to fill in the blank?

I am going to guess "purple man eating elephant".

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

Sean Lange (1/23/2015)
surf4profit (1/23/2015)
I have a
Are we supposed to fill in the blank?
I am going to guess "purple man eating elephant".

No, it's: "I have a dream." 😉

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

surf4profit SSC Rookie Points: 27 More actions · Answer 3

Sorry I tried to edit the original post and could save the edit.

I have a query that I need to add a sequence to for a report and I am drawing a blank on how to accomplish it. I have the following query that generate a long list of data

SELECT variable1 AS PatientBillingID

FROM table1 AS table1 FULL OUTER JOIN

table2 AS table2 ON table1.vid = table2.vid INNER JOIN

table3 AS table3 ON table2.cid = table3.cid FULL OUTER JOIN

table4 AS table4 ON table2.cpt = table4.cpt INNER JOIN

WHERE (table1.dsc >= CONVERT(DATETIME, '2014-01-01 00:00:00', 102)) AND (table1.dsc < CONVERT(DATETIME,

'2015-01-01 00:00:00', 102))

ORDER BY table2.cptts, PatientBillingID

I need to add a column that sequence them by distinct value (like below)

10010802995

10010802996

10010802997

10010802998

10010802999

100108029910

100108029911

100108029912

100108029913

10010803480

10010803481

10010803482

10010803483

10010803484

10010803485

Thanks in advance for any replies

Sean Lange SSC Guru Points: 286609 More actions · Answer 4

surf4profit (1/23/2015)
Sorry I tried to edit the original post and could save the edit.
I have a query that I need to add a sequence to for a report and I am drawing a blank on how to accomplish it. I have the following query that generate a long list of data
SELECT variable1 AS PatientBillingID
FROM table1 AS table1 FULL OUTER JOIN
table2 AS table2 ON table1.vid = table2.vid INNER JOIN
table3 AS table3 ON table2.cid = table3.cid FULL OUTER JOIN
table4 AS table4 ON table2.cpt = table4.cpt INNER JOIN
WHERE (table1.dsc >= CONVERT(DATETIME, '2014-01-01 00:00:00', 102)) AND (table1.dsc < CONVERT(DATETIME,
'2015-01-01 00:00:00', 102))
ORDER BY table2.cptts, PatientBillingID
I need to add a column that sequence them by distinct value (like below)
10010802995
10010802996
10010802997
10010802998
10010802999
100108029910
100108029911
100108029912
100108029913
10010803480
10010803481
10010803482
10010803483
10010803484
10010803485
Thanks in advance for any replies

You can use ROW_NUMBER. What is the criteria for the order?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286609 More actions · Answer 5

This should produce the results you have in your example.

SELECT variable1 AS PatientBillingID

, ROW_NUMBER() over (order by variable1 desc) - 1 as RowNum --added the minus 1 since ROW_NUMBER starts with 1

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

surf4profit SSC Rookie Points: 27 More actions · Answer 6

Thanks for the response but all that is just giving me the row count minus 1 not the sequence of the distinct variable1. If variable 1 value is 123456 and that is in the list 6 times, I need the second column to record 0 by the 1st 123456, 1 by the second, 2 by the thirsd and so on. When the variable1 is distinct again it start the sequence all over again with 0 Like below

10012486150

10012578480

10012578481

10010981570

10011519640

10010802990

10010802991

10010802992

10010802993

10010802994

10010803480

10010803481

10010803482

10010803483

10010803484

10010803485

10010803486

Sean Lange SSC Guru Points: 286609 More actions · Answer 7

Oops forgot to include partition.

SELECT variable1 AS PatientBillingID

, ROW_NUMBER() over (partition by variable1 order by variable1 desc) - 1 as RowNum --added the minus 1 since ROW_NUMBER starts with 1

You should read up about ROW_NUMBER so that you understand what it is doing. Remember that you are the one that has to support the code. 🙂

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/