SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Add Sequence #'s to Record Groups
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sross81
Posting Yak Master

USA
228 Posts

Posted - 12/19/2012 :  11:33:55  Show Profile  Reply with Quote
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

USA
228 Posts

Posted - 12/19/2012 :  11:42:08  Show Profile  Reply with Quote
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

Edited by - sross81 on 12/19/2012 11:48:53
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/19/2012 :  12:17:40  Show Profile  Reply with Quote
Would this work for you?
SELECT AbxEncNbr,
       AbxAntibioticName,
       ROW_NUMBER() OVER (PARTITION BY Enc# 
			ORDER BY AbxAntibioticAdminDate,AbxAntiboticAdminTime) AS 'ABXSeqNum'
FROM   Temp_ABX_PDE
Go to Top of Page

sross81
Posting Yak Master

USA
228 Posts

Posted - 12/19/2012 :  13:51:10  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/19/2012 :  14:03:03  Show Profile  Reply with Quote
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

Edited by - sunitabeck on 12/19/2012 14:03:32
Go to Top of Page

sross81
Posting Yak Master

USA
228 Posts

Posted - 12/19/2012 :  14:05:26  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/19/2012 :  15:10:28  Show Profile  Reply with Quote
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!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000