Here i have used temparory Table for concating all required data.
CREATE TABLE ##GlobalTempTable1(
Empid int,
Enq int,
Call1 int,
HouseCall int,
Email int,
Sms int
)
declare @FranchiseId int
declare @Empid int
declare @Enq int
declare @Call int
declare @HouseCall int
declare @Email int
declare @Sms int
declare @ID int
set @ID = 2
select @Empid = max(followupby) from ERP.FollowUpMaster where franchiseid=2
while (@ID <= @Empid)
begin
select @HouseCall=count(followupid) from ERP.FollowUpMaster
where datepart(month,entrydate)=datepart(month,getdate()) and datepart(year,entrydate)=datepart(year,getdate())
and followupby=@ID and followupmode='House call'
select @Email=count(followupid) from ERP.FollowUpMaster
where datepart(month,entrydate)=datepart(month,getdate()) and datepart(year,entrydate)=datepart(year,getdate())
and followupby=@ID and followupmode='E-mail'
select @Call=count(followupmode) from ERP.FollowUpMaster
where datepart(month,entrydate)=datepart(month,getdate()) and datepart(year,entrydate)=datepart(year,getdate())
and followupby=@ID and followupmode = 'call'
select @Sms=count(followupid) from ERP.FollowUpMaster
where datepart(month,entrydate)=datepart(month,getdate()) and datepart(year,entrydate)=datepart(year,getdate())
and followupby=@ID and followupmode='SMS'
if(@HouseCall != 0 or @Call != 0 or @Email != 0 or @Sms != 0)
begin
INSERT into ##GlobalTempTable1(
Empid,
Call1 ,
HouseCall ,
Email ,
Sms
)
values
(
@ID,
@Call,
@HouseCall,
@Email,
@Sms
)
END
SET @franchiseId = ''
SET @Enq = ''
SET @Sms = ''
SET @Call = ''
SET @HouseCall = ''
SET @Email = ''
SET @ID = @ID + 1
END
select * from ##GlobalTempTable1
drop table ##GlobalTempTable1