January 14, 2003 at 9:17 am
I'm trying to count the number of admissions to a unit.
My records are ordered by DATE and the admitting UNIT is the first UNIT with the minumum DATE for the URN.
A sample of my records appears below.
URNDATEUNIT
35825602003-01-03 00:00:00.000CVICUE
35825602003-01-03 00:00:00.000CVICU
35825602003-01-03 00:00:00.000CVICUE
36923292003-01-10 00:00:00.0003OR
36923292003-01-10 00:00:00.0003OR
36092822003-01-07 00:00:00.000CVICU
36092822003-01-07 00:00:00.000CVICUE
Desired output:
count unit date
1 CVICUE 2003-01-03 00:00:00.000
1 3OR 2003-01-10 00:00:00.000
1 CVICU 2003-01-07 00:00:00.000
January 14, 2003 at 9:28 am
Can you give a bit more detail? I see two records in the sample with the same URN, Date and UNIT 3692329 2003-01-10 00:00:00.000 3OR but you only have a count of 1 in the desired output. Are you wanting a distinct list or an actual group by listing?
Michelle
January 14, 2003 at 10:00 am
I want a distinct list.
For the duplicate record you mentioned I only want to count it once (the first occurrence) because the first unit for the minimum startdate for the URN is the unit a person is admitted to (I'm trying to count the number of admissions to a unit per day. Movement between units or within a unit on the same day should be ignored).
thanks,
stewart
January 15, 2003 at 4:56 am
select count(*) as 'count',c.unit,c.[date]
from (select distinct a.urn,a.[date],a.unit from #t a
inner join (select urn,min([date]) as [date],min(unit) as 'unit' from #t group by urn) b
on b.urn = a.urn and b.[date] = a.[date] and b.unit = a.unit) c
group by c.unit,c.[date]
will give you
1 CVICU 2003-01-03 00:00:00.000
1 CVICU 2003-01-07 00:00:00.000
1 3OR 2003-01-10 00:00:00.000
the problem as I see it is due to different units for the same urn and date and which unit should be used!
Far away is close at hand in the images of elsewhere.
Anon.
January 15, 2003 at 5:22 am
It would help you to add an INDENTITY column to the table then you can query like so.
SELECT COUNT(*), UNIT, [DATE] FROm tblX oqX WHERE [identcol] = (SELECT MIN(iqX.[identcol]) FROM tblX iqX WHERE iqX.URN = oqX.URN) GROUP BY UNIT, [DATE]
However if this is a large table it could be slow and it might be to your bennifit to write a table out with the MIN identcol per URN, then join the table as needed.
Otherwise you will need to create a temp table and use a cursor to find the first record for each URN.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply