March 26, 2007 at 3:50 pm
I have a view of Nurse Units, census count and census date:
create view dbo.census_history
as
select nurs_sta, cen_dtime, sum(tot_cen) as tot_cen
from dbo.daily_census_summary
where nurs_sta in (select nurs_sta from dbo.nrs_sta_xref where active = 1)
group by nurs_sta, cen_dtime
So... select nurs_sta, tot_cen for a given date would return (for example):
nurs_sta tot_cen
-------- -------
abc 5
def 12
ghi 10
jkl 4
Now they want nurs_sta abc and def to be combined into abc. So I would need to return:
nurs_sta tot_cen
-------- -------
abc 17
ghi 10
jkl 4
How do I do that in my view?
March 26, 2007 at 4:35 pm
well, you could do it by subquerying the data... i.e.:
create view dbo.census_history
as
select nurs_sta, cen_dtime, sum(tot_cen) as tot_cen
from
(
select case when nurs_sta = 'def' then 'abc' else nurs_sta end as nurs_sta, cen_dtime, tot_cen
from dbo.daily_census_summary
where nurs_sta in (select nurs_sta from dbo.nrs_sta_xref where active = 1)
) x
group by nurs_sta, cen_dtime
If you're going to need to maintain this grouping across different queries it might be better to normalize it by adding another table... i.e.:
group_name nurs_sta
-----------------------
group_1 abc
group_1 def
group_2 ghi
and then join to that...
March 27, 2007 at 6:23 am
Thank you very much. I knew the answer was in a subquery but couldn't work it out. I was trying case/else in the select statement and subqueries in the where clause. All the wrong places. Thanks again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply