I have a requirement like this
I have a table call Child which looks like following
ChildId ChildAgeByYear ReferralSoure NoOfReferral
Ch01 02 Self 2
Ch01 02 Open 1
Ch03 02 Self 1
Now my problem is I need to display the above Child table information like this
AgeGroup ReferralSoure NoOfReferral
0 0
1 0
2 Self 3
Open 1
3 0
4 0
I need to count the child age group by child age like, 1, 2, 3, 4, and their ReferralSource and NoOfReferral for each child depends on the ReferralSource
Any Idea ?
Regards
Suis
Perhaps something like this:
|||Thank you very muchdeclare @.child table
( ChildId varchar(5),
ChildAgeByYear integer,
ReferralSource varchar(12),
NoOfReferral integer
)
insert into @.child
select 'Ch01', 2, 'Self', 2 union all
select 'Ch01', 2, 'Open', 1 union all
select 'Ch03', 2, 'Self', 1
--select * from @.childselect AgeGroup,
isnull(ReferralSource, '') as ReferralSource,
sum( case when NoOfReferral is null then 0
else NoOfReferral end
) as NoOfReferral
from ( select 0 as AgeGroup union all select 1 union all
select 2 union all select 3 union all select 4
) as n
left join @.child c
on n.AgeGroup = c.ChildAgeByYear
group by AgeGroup,
ReferralSource
order by AgeGroup,
sum( case when NoOfReferral is null then 0
else NoOfReferral end
) desc/*
AgeGroup ReferralSource NoOfReferral
-- --
0 0
1 0
2 Self 3
2 Open 1
3 0
4 0
*/
4 your quick response,i will let u now the outcome once i test this
best regards
suis
