| Author |
 Topic  |
|
|
sross81
Posting Yak Master
USA
228 Posts |
 Posted - 12/19/2012 : 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
USA
228 Posts |
Posted - 12/19/2012 : 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 |
Edited by - sross81 on 12/19/2012 11:48:53 |
 |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/19/2012 : 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
USA
228 Posts |
Posted - 12/19/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/19/2012 : 14:03:03
|
What is the data type of the AbxAntibioticAdministrationDate and AbxAntibioticAdministrationTime columns? You can find out usingSELECT
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 |
|
|
|
sross81
Posting Yak Master
USA
228 Posts |
Posted - 12/19/2012 : 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 usingSELECT
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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/19/2012 : 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!! |
|
|
| |
Topic |
|
|
|
Add Sequence #'s to Record Groups
