How to get this output ?
-
Hi everybody
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
-
/* I always work best with examples, so I've recreated your table (sort of), populated it with some test
data and then provided a SQL Script to report it the way you want. There might be a way to do it without
joining with the in-memory table but I don't know it of the top of my head. */
if exists (select 1 from sysobjects where id = object_id('child_') and type = 'U') drop table child_
go
create table child_(
childId_ numeric(10,0) not null identity,
ChildAgeByYear_ integer,
ReferralSource_ varchar(10),
NumberOfReferrals_ integer,
constraint pk_child_ primary key (childID_)
)
go
insert into child_ (ChildAgeByYear_,ReferralSource_,NumberOfReferrals_) values (2,'Self',2)
insert into child_ (ChildAgeByYear_,ReferralSource_,NumberOfReferrals_) values (2,'Open',1)
insert into child_ (ChildAgeByYear_,ReferralSource_,NumberOfReferrals_) values (3,'Self',3)
insert into child_ (ChildAgeByYear_,ReferralSource_,NumberOfReferrals_) values (3,'Open',1)
insert into child_ (ChildAgeByYear_,ReferralSource_,NumberOfReferrals_) values (1,'Self',2)
insert into child_ (ChildAgeByYear_,ReferralSource_,NumberOfReferrals_) values (4,'Self',2)
go
--select * from child_
--This is the key part of the script. You create an in-memory table (could be done with a temp table)
--and populate it with all the years you want to report on (I suppose 0 to 100 would be sufficient) and
--then join your real table to the in-memory table and group by the colums you want the "counts/sums" for.
begin
declare @t_ table (age_ int)
declare @i_ int
set @i_ = 0
while @i_ < 10
begin
insert into @t_ values (@i_)
set @i_ = @i_ + 1
end
--select * from @t_
select age_, coalesce(referralsource_,'N/A'), coalesce(sum(numberofreferrals_),0)
from child_ right outer join @t_ on childagebyyear_ = age_
group by age_, referralsource_
order by age_
end
-
Thank you very much for your kind help, and fast response.
i could sorted out the sql,
thanks very much again
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply