I have created a stored procedure for summary for all kind of numeric data present in my database:
ALTER PROCEDURE [dbo].[GetUserDashBoardSummarry]
@Date as nvarchar(25),
@UserId as bigint = 0
AS
BEGIN
declare @Data as Table (
ColdCall bigint null ,
WrongNo bigint null ,
NewInq bigint null ,
A bigint null ,
B bigint null ,
C bigint null ,
D bigint null ,
E bigint null ,
F bigint null ,
Followup bigint null ,
ID bigint null,
NEW bigint null,
Meeting_Reminder bigint null,
KYC bigint null,
Wealth_Protection bigint null,
DONE bigint null,
Review_meet bigint null,
Sampoorna_Surksha bigint null,
QUOTATION bigint null,
Sam_Raksh_review_report bigint null,
Portfolio_Review_for_existing_client bigint null,
Seminar bigint null,
Seminar_Registration bigint null,
Registration_confirmation bigint null,
For_safal_investor bigint null,
JUST_2_HOUR_TO_GO bigint null
)
insert into @Data(ColdCall,WrongNo,NewInq,A,B,C,D,E,F,Followup,ID)
select
0,0,0,
sum((CASE WHEN [inq_category] = 16 then 1 else 0 end)) as A,
sum((CASE WHEN [inq_category] = 17 then 1 else 0 end)) as B,
sum((CASE WHEN [inq_category] = 18 then 1 else 0 end)) as C,
sum((CASE WHEN [inq_category] = 19 then 1 else 0 end)) as D,
sum((CASE WHEN [inq_category] = 20 then 1 else 0 end)) as E,
sum((CASE WHEN [inq_category] = 21 then 1 else 0 end)) as F,
count(*) as cnt,[followup_by] as ID From [dbo].[followup] where 1=1
and dbo.Formated_Date(followup_date) = dbo.Formated_Date(@Date)
and ( @UserId = 0 OR [followup_by] = @UserId)
group by followup_by
insert into @Data(ColdCall,WrongNo,NewInq,A,B,C,D,E,F,Followup,ID)
select
0,0,
count(*) as inq,
0,0,0,0,0,0,0,
[Inq_By] as ID
from [dbo].[Inq_Master]
where dbo.Formated_Date(Date_Time) = dbo.Formated_Date(@Date)
and ( @UserId = 0 OR [Inq_By] = @UserId)
group by [Inq_By]
insert into @Data(ColdCall,WrongNo,NewInq,A,B,C,D,E,F,Followup,ID)
select
sum((CASE WHEN status = 1 then 1 else 0 end)) as ColdCall,
sum((CASE WHEN status = 3 then 1 else 0 end)) as WrongNo,
0,0,0,0,0,0,0,0,0,0,
UserId as ID
from [dbo].[ColdCall]
where dbo.Formated_Date(DateTime) = dbo.Formated_Date(@Date)
and ( @UserId = 0 OR UserId = @UserId)
group by UserId
select
(select [username] From [dbo].[web_user_master] where [pk_id] = ID) as Name,
sum(ColdCall) + sum(WrongNo) + sum(Followup) as Total,
sum(ColdCall) as ColdCall,
sum(WrongNo) as WrongNo,
sum(NewInq) as NewInq,
sum(Followup) - sum(NewInq) as Old_F,
'|' as '|',
sum(Followup) as Tot_F,
sum(A) as A,
sum(B) as B,
sum(C) as C,
sum(D) as D,
sum(E) as E,
sum(F) as F
from @Data
group by ID
insert into @Data(NEW,Meeting_Reminder,KYC,Wealth_Protection,DONE,Review_meet,Sampoorna_Surksha,QUOTATION,Sam_Raksh_review_report,Portfolio_Review_for_existing_client,Seminar,Seminar_Registration,Registration_confirmation,For_safal_investor,JUST_2_HOUR_TO_GO)
select
(select [username] From [dbo].[web_user_master] where [pk_id] = [followup_by]) as Name,
sum((CASE WHEN party_category =1 then 1 else 0 end)) as NEW,
sum((CASE WHEN party_category =2 then 1 else 0 end)) as Meeting_Reminder,
sum((CASE WHEN party_category =3 then 1 else 0 end)) as KYC,
sum((CASE WHEN party_category =4 then 1 else 0 end)) as Wealth_Protection,
sum((CASE WHEN party_category =29 then 1 else 0 end)) as DONE,
sum((CASE WHEN party_category =33 then 1 else 0 end)) as Review_meet,
sum((CASE WHEN party_category =111 then 1 else 0 end)) as Sampoorna_Surksha,
sum((CASE WHEN party_category =112 then 1 else 0 end)) as QUOTATION,
sum((CASE WHEN party_category =113 then 1 else 0 end)) as Sam_Raksh_review_report,
sum((CASE WHEN party_category =114 then 1 else 0 end)) as Portfolio_Review_for_existing_client,
sum((CASE WHEN party_category =115 then 1 else 0 end)) as Seminar,
sum((CASE WHEN party_category =10147 then 1 else 0 end)) as Seminar_Registration,
sum((CASE WHEN party_category =10148 then 1 else 0 end)) as Registration_confirmation,
sum((CASE WHEN party_category =10149 then 1 else 0 end)) as For_safal_investor,
sum((CASE WHEN party_category =10150 then 1 else 0 end)) as JUST_2_HOUR_TO_GO,
count(*) as cnt , [followup_by] as ID
From [dbo].[followup_party] where 1=1
and dbo.Formated_Date(followup_date) = dbo.Formated_Date(@Date)
and ( @UserId = 0 OR [followup_by] = @UserId)
group by [followup_by]
END
When I execute query then it gives the following errors:
Msg 121, Level 15, State 1, Procedure GetUserDashBoardSummarry, Line 68
The select list for the INSERT statement contains more items than the insert list.
Msg 121, Level 15, State 1, Procedure GetUserDashBoardSummarry, Line 98
The select list for the INSERT statement contains more items than the insert list.
How can i resolve this? What's going wrong here?
