Add Sequence #'s to Record Groups

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 2008 Forums

Transact-SQL (2008)

Add Sequence #'s to Record Groups

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2012-12-19 : 11:33:55

Hello,

I have a table with records like this:

Enc#, AbxAntiboticName,AbxAntibioticAdminDate, AbxAntiboticAdminTime
cp555, name1, 12/1/2012, 8:00
cp555, name2, 12/4/2012, 14:25
cp555, name3, 12/5/2012, 3:25

The enc # is the field I want to group on. It can be any random assigned number. I want to group by encounter and then show the 1st, 2nd, 3rd, etc antibiotic that was received based on the date and time. There could be alot more than just 3 and multiple on the same day, etc.

So add a field called AbxSequence and have it be 1, 2, 3 etc.

Enc#, AbxAntiboticName,AbxAntibioticAdminDate, AbxAntiboticAdminTime, abxsequence
cp555, name1, 12/1/2012, 8:00,1
cp555, name2, 12/4/2012, 14:25,2
cp555, name3, 12/5/2012, 3:25,3

I have been trying to find an example online, but everything I found seems to be related to having specific predefined groups and in my example the encounter number isn't predefined.

Thanks in Advance!
Sherri

sross81
Posting Yak Master

228 Posts

Posted - 2012-12-19 : 11:42:08

I have tried using this, but for one encounter number with 5 antibiotics in 2 days it only gives a sequence number of 1, 2

select AbxEncNbr,AbxAntibioticName, ROW_NUMBER() OVER(ORDER BY MAX(AbxAntibioticAdministrationDate), MAX(AbxAntibioticAdministrationTime)) as
'ABXSeqNum'
from Temp_ABX_PDE
group by AbxEncNbr, AbxAntibioticName

I've also tried this and it just basically numbers the entire list so one encounter number with 9 antibitoics goes 1-9 and then it switches to 10 on the next encounter number. I need it to start over on the new encounter number:

select AbxEncNbr,AbxAntibioticName, ROW_NUMBER() OVER(ORDER BY AbxEncNbr,AbxAntibioticName,AbxAntibioticAdministrationDate
,AbxAntibioticAdministrationTime) as 'ABXSeqNum'
from Temp_ABX_PDE
group by AbxEncNbr, AbxAntibioticName, AbxAntibioticAdministrationDate,AbxAntibioticAdministrationTime
order by AbxEncNbr, AbxAntibioticName, AbxAntibioticAdministrationDate,AbxAntibioticAdministrationTime

Thanks in Advance!
Sherri

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-19 : 12:17:40

Would this work for you?

SELECT AbxEncNbr,
       AbxAntibioticName,
       ROW_NUMBER() OVER (PARTITION BY Enc# 
			ORDER BY AbxAntibioticAdminDate,AbxAntiboticAdminTime) AS 'ABXSeqNum'
FROM   Temp_ABX_PDE

sross81
Posting Yak Master

228 Posts

Posted - 2012-12-19 : 13:51:10

Thank you for your reply. It is still a little off. Here is my code:

SELECT AbxEncNbr,
AbxAntibioticName,
ROW_NUMBER() OVER (PARTITION BY AbxEncNbr
ORDER BY AbxAntibioticAdministrationDate,AbxAntibioticAdministrationTime) AS 'ABXSeqNum'
FROM Temp_ABX_PDE

Raw data

AbxEncNbr bxAntibioticName AbxAntibioticAdministrationDate AbxAntibioticAdministrationTime
CP123455555 Rocephin 12/29/2011 18:25
CP123455555 Azithromycin 12/29/2011 18:58
CP123455555 Aztreonam 1/1/2012 11:38
CP123455555 Rocephin 1/1/2012 14:31
CP123455555 Zithromax 1/1/2012 15:50

Queried Data. I think that it should be Rocephin, AZithromycin based on the date and times.....the one's are 1/1/2012 seem backwards too.
AbxEncNbr AbxAntibioticName ABXSeqNum
CP123455555 Aztreonam 1
CP123455555 Rocephin 2
CP123455555 Zithromax 3
CP123455555 Rocephin 4
CP123455555 Azithromycin 5

Any other ideas?

quote:

Originally posted by sunitabeck

Would this work for you?

SELECT AbxEncNbr,
       AbxAntibioticName,
       ROW_NUMBER() OVER (PARTITION BY Enc# 
			ORDER BY AbxAntibioticAdminDate,AbxAntiboticAdminTime) AS 'ABXSeqNum'
FROM   Temp_ABX_PDE

Thanks in Advance!
Sherri

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-19 : 14:03:03

What is the data type of the AbxAntibioticAdministrationDate and AbxAntibioticAdministrationTime columns? You can find out using

SELECT
	COLUMN_NAME,DATA_TYPE
FROM
	INFORMATION_SCHEMA.COLUMNS
WHERE
	TABLE_NAME = 'Temp_ABX_PDE'
	AND COLUMN_NAME IN
	('AbxAntibioticAdministrationDate','AbxAntibioticAdministrationTime')

If they are not DATETIME and TIME (or one of its variants, change the code to the following)

SELECT AbxEncNbr,
AbxAntibioticName,
ROW_NUMBER() OVER (PARTITION BY AbxEncNbr 
ORDER BY 
	CAST(AbxAntibioticAdministrationDate AS DATE),
	CAST(AbxAntibioticAdministrationTime AS TIME)) AS 'ABXSeqNum'
FROM Temp_ABX_PDE

sross81
Posting Yak Master

228 Posts

Posted - 2012-12-19 : 14:05:26

It was the data types! I didn't even think of that. I am just getting back into using SQL again. Thank you so much for pointing that out :).

quote:
Originally posted by sunitabeck

What is the data type of the AbxAntibioticAdministrationDate and AbxAntibioticAdministrationTime columns? You can find out using
SELECT
COLUMN_NAME,DATA_TYPE
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'Temp_ABX_PDE'
AND COLUMN_NAME IN
('AbxAntibioticAdministrationDate','AbxAntibioticAdministrationTime')
If they are not DATETIME and TIME (or one of its variants, change the code to the following)
SELECT AbxEncNbr,
AbxAntibioticName,
ROW_NUMBER() OVER (PARTITION BY AbxEncNbr
ORDER BY
CAST(AbxAntibioticAdministrationDate AS DATE),
CAST(AbxAntibioticAdministrationTime AS TIME)) AS 'ABXSeqNum'
FROM Temp_ABX_PDE

Thanks in Advance!
Sherri

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-19 : 15:10:28

You are quite welcome!

Many of the experts on this forum would suggest that if at all possible, data that represents DATE, DATETIME, TIME etc. should be stored in columns of appropriate data types (such as DATE, DATETIME, TIME etc.) Same goes for other types of data as well - to avoid this type of problem.

Also, if you use varchar for the data type of a column that stores date/time, someone could (inadvertently perhaps) store something that is not a date (for example the text "Joe's bar") into that column. That would cause the above query to fail!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources