Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Question

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

mahinder.singh.chauhan

SSC Veteran

Points: 250
More actions
July 14, 2013 at 11:57 pm

#150438

Hello All
I m not able to find this error Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
but i have checked so many times it showing data follows this error
please help me out thanks in advance
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Created Date: 23/04/2013
ALTER PROC [dbo].[UDSP_CREATE_REPORT]
@Role_Id INT, --99 for all stores, 100 regionid,
@User_Id INT, --2 Weekly, 4 Quarter
@Range INT,
@start_Date nvarchar(max),
@end_Date nvarchar(max),
@Day_Name varchar(50),
@Report_Type int,
@LoginUserID int=0
AS
BEGIN
SET NOCOUNT ON;
DECLARE @WhereClause nvarchar(max)
IF (@Range=2)
BEGIN
--Print Weekly
SET @end_Date = 'Convert(datetime,'''+@start_Date+''',101)';
SET @start_Date='select DATEADD(DD,-6,Convert(Datetime,'''+@start_Date+''',101))';
END
ELSE IF(@Range=3)
BEGIN
SET @end_Date = 'Convert(datetime,'''+@end_Date+''',101)';
SET @start_Date = 'select Convert(datetime,'''+@start_Date+''',101)';
END
ELSE IF(@Range=4)
BEGIN
Declare @Quarter nvarchar(100)
SET @Quarter = @start_Date
SET @start_Date='select case when datepart(weekday,DATEADD(quarter,(YEAR(getdate())-1900)*4+'+@start_Date+'-1,0))!=4 then
DATEADD(DD,4-datepart(weekday, DATEADD(quarter,(YEAR(getdate())-1900)*4+'+@start_Date+'-1,0)),DATEADD(quarter,(YEAR(getdate())-1900)*4+'+@start_Date+'-1,0))
else DATEADD(quarter,(YEAR(getdate())-1900)*4+'+@start_Date+'-1,0) end';
SET @end_Date = ' convert(datetime,DATEADD(quarter,(YEAR(getdate())-1900)*4+'''+@Quarter+''',0)-1,101)';
END
DECLARE @DAY VARCHAR(10)
declare @storeId int
set @storeId = 0
DECLARE @TEMP_STORES TABLE(ID INT IDENTITY(1,1),STOREID INT,STORENUMBER VARCHAR(50))
INSERT INTO @TEMP_STORES SELECT StoreID,StoreNumber FROM [dbo].[Fetch_Stores](@Role_Id,@User_Id,@LoginUserID)
--select * from @TEMP_STORES
--IF(@Role_Id=99)
--BEGIN
--INSERT INTO @TEMP_STORES SELECT StoreID,StoreNumber FROM Stores s WHERE s.IsDeleted=0
--END
--ELSE IF(@Role_Id=-99)
--BEGIN
--INSERT INTO @TEMP_STORES SELECT StoreID,StoreNumber FROM Stores s WHERE s.IsDeleted=0 and s.StoreId=@User_Id
--END
--ELSE IF(@Role_Id=100)
--BEGIN
--INSERT INTO @TEMP_STORES SELECT StoreID,StoreNumber FROM Stores s WHERE s.RegionId=@User_Id and s.IsDeleted=0
--END
--ELSE
--BEGIN
--INSERT INTO @TEMP_STORES
--SELECT s.StoreID,StoreNumber FROM Stores s
--INNER JOIN UserStores us on us.StoreID=s.StoreId
--AND us.UserId=UserId AND s.IsDeleted=0
--AND us.UserId=@User_Id
--END
--declare @tempWages table (rate float, sdate datetime,StoreId int) -- this table for store wageRate as per stores
DECLARE @TempPayroll TABLE(empid int, WedWage float, ThuWage float, FriWage float, SatWage float, SunWage float, MonWage float, TueWage float, WedReg float,
ThuReg float,FriReg float,SatReg float,SunReg float,MonReg float,TueReg float,WedOT float,ThuOT float,FriOT float,SatOT float,SunOT float,
MonOT float,TueOT float,WedDT float,ThuDT float,FriDT float,SatDT float,SunDT float,MonDT float,TueDT float)
DECLARE @tmpData TABLE(ID INT IDENTITY(1,1),total_Unit int,total_Hour decimal(9,1),NetSales decimal(18,2),WageRate decimal(9,1),RomaSalad decimal(18,2),Net_Coke decimal(18,2),InvoicesCredit decimal(18,2),TotalUnitPrice decimal(9,1),Is_24_Hour bit,storeId int)
DECLARE @tblFPRA TABLE(ID INT IDENTITY(1,1),Productivity decimal(9,1),NetSales decimal(18,2),RomaSalad decimal(18,2),Net_Coke decimal(18,2),InvoicesCredit decimal(18,2),TotalUnitPrice decimal(18,2),FoodCost decimal(18,2),FoodCostPerc decimal(18,2),Is_24_Hour bit,storeId int,LaborCostPer decimal(9,2))
DECLARE @tmpHourTest TABLE(ID INT IDENTITY(1,1),tot_Hour decimal(9,1),tot_Unit int,stId int)
create table #tempFPRA (sdate datetime,edate datetime)
insert into #tempFPRA exec (@start_Date+','+@end_date)
--select * from #tempFPRA
declare @TempName table(StoreNumber nvarchar(1000),storeID int,Area varchar(100),DM varchar(100),RM varchar(100),rowid int IDENTITY(1,1))
INSERT INTO @TempName select * from
(select distinct isnull(u.FirstName,'')+' '+isnull(u.LastName,'') as Name,r.RoleName,s.StoreNumber,s.StoreId,Rs.RegionName from stores s
left join UserStores us on s.StoreId =us.StoreID
left join Regions Rs on Rs.RegionId=s.RegionId
left join users u on u.userId=us.UserId
left join Roles r on u.RoleId=r.RoleId
and r.RoleId in (2,4)
and u.ISDeleted=0) as p
pivot (max(Name) FOR Rolename in(DM,RM))as pvt
--select * from @TempName
DECLARE @WeekStartDate DATETIME
SET @WeekStartDate = (SELECT sdate FROM #tempFPRA)
SELECT sdate FROM #tempFPRA
IF(SELECT COUNT(*) FROM @TEMP_STORES) >0
BEGIN
WHILE(SELECT COUNT(*) FROM @TEMP_STORES ) > 0
BEGIN
DECLARE @STOREIDs INT
DECLARE @ID INT
SELECT TOP 1 @ID = ID,@STOREIDs = STOREID FROM @TEMP_STORES
DECLARE @Sql nvarchar(max)
declare @sql1 nvarchar(max)
--select * from @TEMP_STORES where ID = @ID
IF(@Report_Type = 1)
BEGIN
insert into @TempPayroll EXEC [dbo].[Sp_Fetch_Employee_Daily_Data_Hours_Rates] @WeekStartDate,@STOREIDs
SET @Sql='select Total_Unit,TOTAL_HOUR,z.Net_Sales,a.RomaSalad as RomaSalad,b.Net_Coke,d.InvoicesCredit,c.TotalUnitPrice,y.Is_24_Hour,x.StoreId from
(
select Total_Unit,StoreId
from
(
select round(SUM((ISNULL(dmd.Monday,0))+(ISNULL(dmd.Tuesday,0))+(ISNULL(dmd.Wednesday,0))+(ISNULL(dmd.Thursday,0))+(ISNULL(dmd.Friday,0))+
(ISNULL(dmd.Saturday,0))+
(ISNULL(dmd.Sunday,0))
),2) As Unit,StoreId from DccsMetricData dmd
inner join #tempFPRA td on convert(datetime,dmd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,dmd.WeekStartDate,101)<=convert(datetime,td.edate,101)
where dmd.MetricId = 17 and dmd.StoreId = '+CAST(@STOREIDs AS VARCHAR)+' and (IsWrongData = ''No'' or IsWrongData is null )
GROUP BY STOREID
)tmpUnit
unpivot
(
Total_Unit for tot_unit in (Unit)
)temp1
)x
left outer join
(
SELECT TOTAL_HOUR,StoreId,Is_24_Hour
FROM
(
SELECT round(SUM((ISNULL(EDD.MondayHour,0))+(ISNULL(EDD.TuesdayHour,0))+(ISNULL(EDD.WednesdayHour,0))+(ISNULL(EDD.ThursdayHour,0))+(ISNULL(EDD.FridayHour,0))+(ISNULL(EDD.Saturdayhour,0))+
(ISNULL(EDD.SundayHour,0))),2) as hour,e.StoreId,s.Is_24_Hour FROM EMPLOYEEDAILYDATA EDD INNER JOIN Employees E ON EDD.EmpId = E.EmpId
INNER JOIN Stores S ON S.StoreId = E.StoreId
inner join #tempFPRA td on convert(datetime,edd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,edd.WeekStartDate,101)<=convert(datetime,td.edate,101)
WHERE E.StoreId = '+CAST(@STOREIDs AS VARCHAR)+' group by e.StoreId,Is_24_Hour
)TEST
UNPIVOT
(
TOTAL_HOUR FOR TOT_HOURS IN (hour)
)temp
)y on x.StoreId = y.StoreId
left outer join
(
SELECT Net_Sales,StoreId
FROM
(
SELECT round(SUM((ISNULL(EDD.Monday,0))+(ISNULL(EDD.Tuesday,0))+(ISNULL(EDD.Wednesday,0))+(ISNULL(EDD.Thursday,0))+(ISNULL(EDD.Friday,0))+(ISNULL(EDD.Saturday,0))+
(ISNULL(EDD.Sunday,0))),2) as NetSales,EDD.StoreId FROM EmployeeDailyDataBottom EDD
INNER JOIN Stores S ON S.StoreId = EDD.StoreId
inner join #tempFPRA td on convert(datetime,edd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,edd.WeekStartDate,101)<=convert(datetime,td.edate,101)
WHERE edd.Name=''netsales'' and EDD.StoreId = '+CAST(@STOREIDs AS VARCHAR)+' GROUP BY EDD.StoreID
)TEST
UNPIVOT
(
Net_Sales FOR Sales IN (NetSales)
)temp
)z on z.StoreId = y.StoreId
left outer join
(
SELECT RomaSalad,StoreId
FROM
(
SELECT round(SUM((ISNULL(EDD.Monday,0))+(ISNULL(EDD.Tuesday,0))+(ISNULL(EDD.Wednesday,0))+(ISNULL(EDD.Thursday,0))+(ISNULL(EDD.Friday,0))+(ISNULL(EDD.Saturday,0))+
(ISNULL(EDD.Sunday,0))),2) as RomaSalaid,EDD.StoreId FROM EmployeeDailyDataBottom EDD
INNER JOIN Stores S ON S.StoreId = EDD.StoreId
inner join #tempFPRA td on convert(datetime,edd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,edd.WeekStartDate,101)<=convert(datetime,td.edate,101)
WHERE EDD.Name=''romasalad'' and EDD.StoreId ='+CAST(@STOREIDs AS VARCHAR)+' GROUP BY EDD.StoreID
)TEST
UNPIVOT
(
RomaSalad FOR Salad IN (RomaSalaid)
)temp
) a on a.StoreID = z.StoreID
left outer join
(
SELECT Net_Coke,StoreId
FROM
(
SELECT round(SUM((ISNULL(EDD.Monday,0))+(ISNULL(EDD.Tuesday,0))+(ISNULL(EDD.Wednesday,0))+(ISNULL(EDD.Thursday,0))+(ISNULL(EDD.Friday,0))+(ISNULL(EDD.Saturday,0))+
(ISNULL(EDD.Sunday,0))),2) as Coke,EDD.StoreId FROM EmployeeDailyDataBottom EDD
INNER JOIN Stores S ON S.StoreId = EDD.StoreId
inner join #tempFPRA td on convert(datetime,edd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,edd.WeekStartDate,101)<=convert(datetime,td.edate,101)
WHERE EDD.Name=''coke'' and EDD.StoreId ='+CAST(@STOREIDs AS VARCHAR)+' GROUP BY EDD.StoreID
)TEST
UNPIVOT
(
Net_Coke FOR Cokes IN (Coke)
)temp
)b on b.storeId = a.StoreId
left outer join
(
SELECTTotalUnitPrice,StoreId
(
SELECT SUM(CONVERT(DECIMAL(18, 2), mw.UnitPrice*mwd.Variance )) AS TotalUnitPrice,mwd.StoreId from MasterWISRData mwd
inner JOIN MasterWISR mw ON mw.WisrId=mwd.MasterwiserId
inner join Stores S ON S.StoreId = mwd.StoreId
inner join Regions Rs ON Rs.RegionId=S.RegionId
inner join #tempFPRA td on convert(datetime,mwd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,mwd.WeekStartDate,101)<=convert(datetime,td.edate,101)
WHERE mw.UnitPrice IS NOT NULL and mwd.StoreId ='+CAST(@STOREIDs AS VARCHAR)+' group by mwd.StoreId
)TEST
)c on c.StoreId=b.StoreId
left outer join
(
SELECT InvoicesCredit,StoreId
FROM
(
SELECT round(SUM((ISNULL(EDD.Monday,0))+(ISNULL(EDD.Tuesday,0))+(ISNULL(EDD.Wednesday,0))+(ISNULL(EDD.Thursday,0))+(ISNULL(EDD.Friday,0))+(ISNULL(EDD.Saturday,0))+
(ISNULL(EDD.Sunday,0))),2) as Credit,EDD.StoreId FROM EmployeeDailyDataBottom EDD
INNER JOIN Stores S ON S.StoreId = EDD.StoreId
inner join #tempFPRA td on convert(datetime,edd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,edd.WeekStartDate,101)<=convert(datetime,td.edate,101)
WHERE EDD.Name=''romacokecredit'' and EDD.StoreId ='+CAST(@STOREIDs AS VARCHAR)+' GROUP BY EDD.StoreID
)TEST
UNPIVOT
(
InvoicesCredit FOR InvoicesCredits IN (Credit)
)temp
)d on d.storeId = c.StoreId'
--print @sql
END
ELSE
BEGIN
insert into @TempPayroll EXEC [dbo].[Sp_Fetch_Employee_Daily_Data_Hours_Rates] @WeekStartDate,@STOREIDs
SET @Sql='select Total_Unit,TOTAL_HOUR,z.Net_Sales,a.RomaSalad as RomaSalad,b.Net_Coke,d.InvoicesCredit,c.TotalUnitPrice,y.Is_24_Hour,x.StoreId from
(
select Total_Unit,StoreId
from
(
select round(SUM(ISNULL('+@Day_Name+',0)),2) AS Unit,StoreId from DccsMetricData dmd
inner join #tempFPRA td on convert(datetime,dmd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,dmd.WeekStartDate,101)<=convert(datetime,td.edate,101)
where MetricId = 17 and StoreId = '+CAST(@STOREIDs AS VARCHAR)+' and (IsWrongData = ''No'' or IsWrongData is null ) GROUP BY STOREID
)tmpUnit
unpivot
(
Total_Unit for tot_unit in (Unit)
)temp1
)x
left outer join
(
SELECT TOTAL_HOUR,StoreId,Is_24_Hour
FROM
(
SELECT isnull(ROUND(SUM(EDD.'+@Day_Name+'Hour'+'),2),0.0) as hour,e.StoreId,s.Is_24_Hour FROM EMPLOYEEDAILYDATA EDD INNER JOIN Employees E ON EDD.EmpId = E.EmpId
INNER JOIN Stores S ON S.StoreId = E.StoreId
inner join #tempFPRA td on convert(datetime,edd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,edd.WeekStartDate,101)<=convert(datetime,td.edate,101)
WHERE E.StoreId = '+CAST(@STOREIDs AS VARCHAR)+' group by e.StoreId,Is_24_Hour
)TEST
UNPIVOT
(
TOTAL_HOUR FOR TOT_HOURS IN (hour)
)temp
)y on x.StoreId = y.StoreId
left outer join
(
SELECT Net_Sales,StoreId
FROM
(
SELECT isnull(ROUND(EDD.'+@Day_Name+',2),0.0) as NetSales,EDD.StoreId FROM EmployeeDailyDataBottom EDD
INNER JOIN Stores S ON S.StoreId = EDD.StoreId
inner join #tempFPRA td on convert(datetime,edd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,edd.WeekStartDate,101)<=convert(datetime,td.edate,101)
WHERE edd.Name=''netsales'' and EDD.StoreId = '+CAST(@STOREIDs AS VARCHAR)+'
)TEST
UNPIVOT
(
Net_Sales FOR Sales IN (NetSales)
)temp
)z on z.StoreId = y.StoreId
left outer join
(
SELECT RomaSalad,StoreId
FROM
(
SELECT isnull(ROUND(EDD.'+@Day_Name+',2),0.0) as RomaSalaid,EDD.StoreId FROM EmployeeDailyDataBottom EDD
INNER JOIN Stores S ON S.StoreId = EDD.StoreId
inner join #tempFPRA td on convert(datetime,edd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,edd.WeekStartDate,101)<=convert(datetime,td.edate,101)
WHERE edd.Name=''romasalad'' and EDD.StoreId = '+CAST(@STOREIDs AS VARCHAR)+'
)TEST
UNPIVOT
(
RomaSalad FOR Salad IN (RomaSalaid)
)temp
) a on a.StoreId=z.StoreId
left outer join
(
SELECT Net_Coke,StoreId
FROM
(
SELECT isnull(ROUND(EDD.'+@Day_Name+',2),0.0) as Coke,EDD.StoreId FROM EmployeeDailyDataBottom EDD
INNER JOIN Stores S ON S.StoreId = EDD.StoreId
inner join #tempFPRA td on convert(datetime,edd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,edd.WeekStartDate,101)<=convert(datetime,td.edate,101)
WHERE edd.Name=''coke'' and EDD.StoreId = '+CAST(@STOREIDs AS VARCHAR)+'
)TEST
UNPIVOT
(
Net_Coke FOR Cokes IN (Coke)
)temp
)b on b.StoreId=a.StoreId
left outer join
(
SELECT InvoicesCredit,StoreId
FROM
(
SELECT isnull(ROUND(EDD.'+@Day_Name+',2),0.0) as Credit,EDD.StoreId FROM EmployeeDailyDataBottom EDD
INNER JOIN Stores S ON S.StoreId = EDD.StoreId
inner join #tempFPRA td on convert(datetime,edd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,edd.WeekStartDate,101)<=convert(datetime,td.edate,101)
WHERE edd.Name=''romasaladcokecredit'' and EDD.StoreId = '+CAST(@STOREIDs AS VARCHAR)+'
)TEST
UNPIVOT
(
InvoicesCredit FOR InvoicesCredits IN (Credit)
)temp
)d on b.StoreId=d.StoreId
left outer join
(SELECT TotalUnitPrice,StoreId
FROM
(
SELECT SUM(CONVERT(DECIMAL(9, 1), mw.UnitPrice*mwd.Variance )) AS TotalUnitPrice,mwd.StoreId from MasterWISRData mwd
inner JOIN MasterWISR mw ON mw.WisrId=mwd.MasterwiserId
inner join Stores S ON S.StoreId = mwd.StoreId
inner join Regions Rs ON Rs.RegionId=S.RegionId
inner join #tempFPRA td on convert(datetime,mwd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,mwd.WeekStartDate,101)<=convert(datetime,td.edate,101)
WHERE mw.UnitPrice IS NOT NULL and mwd.StoreId ='+CAST(@STOREIDs AS VARCHAR)+' group by mwd.StoreId
)TEST
)c on c.StoreId=d.StoreId
'
--PRINT @SQL
END
insert into @tmpData(total_Unit,total_Hour,NetSales,RomaSalad,Net_Coke,InvoicesCredit,TotalUnitPrice,Is_24_Hour,storeId) EXEC (@Sql)
update @tmpData set WageRate =(select Sum((tpr.WedWage*tpr.WedReg)+(tpr.WedWage*tpr.WedOT*1.5)+ (tpr.WedWage*tpr.WedDT*2)+
(tpr.ThuWage*tpr.ThuReg)+(tpr.ThuWage*tpr.ThuOT*1.5)+ (tpr.ThuWage*tpr.ThuDT*2)+
(tpr.FriWage*tpr.FriReg)+(tpr.FriWage*tpr.FriOT*1.5)+ (tpr.FriWage*tpr.FriDT*2)+
(tpr.SatWage*tpr.SatReg)+(tpr.SatWage*tpr.SatOT*1.5)+ (tpr.SatWage*tpr.SatDT*2)+
(tpr.SunWage*tpr.SunReg)+(tpr.SunWage*tpr.SunOT*1.5)+ (tpr.SunWage*tpr.SunDT*2)+
(tpr.MonWage*tpr.MonReg)+(tpr.MonWage*tpr.MonOT*1.5)+ (tpr.MonWage*tpr.MonDT*2)+
(tpr.TueWage*tpr.TueReg)+(tpr.TueWage*tpr.TueOT*1.5)+ (tpr.TueWage*tpr.TueDT*2)
) as labor from @TempPayroll tpr) where storeId = @STOREIDs
delete from @TEMP_STORES where ID = @ID
delete from @TempPayroll
END
--select * from @tmpData
DECLARE @LaborVal INT
SET @LaborVal = (select b.LaborValue from Buffer b where b.BufferYear = datename(YY,getdate()))
IF(SELECT COUNT(*) FROM @tmpData) > 0
BEGIN
WHILE(SELECT COUNT(*) FROM @tmpData) > 0
BEGIN
DECLARE @IDs INT
DECLARE @TOTAL_HOURS DECIMAL(9,1)
DECLARE @TOTAL_UNIT DECIMAL(9,1)
DECLARE @PRODUCTIVITY DECIMAL(9,1)
DECLARE @NETSALES DECIMAL(18,1)
DECLARE @ROMASALAD DECIMAL(18,1)
DECLARE @NET_COKE DECIMAL(18,1)
DECLARE @INVOICE_CREDIT DECIMAL(18,1)
DECLARE @TOTAL_UNIT_PRICE DECIMAL(18,1)
DECLARE @FOOD_COST DECIMAL(18,2)
DECLARE @FOOD_COST_PERC DECIMAL(18,2)
DECLARE @WAGE_RATE DECIMAL(9,1)
DECLARE @LABOR_COST_PERC DECIMAL(9,1)
declare @Is_24_Hour bit
declare @StorId int
SELECT TOP 1 @IDs = ID,@TOTAL_HOURS = total_Hour,@TOTAL_UNIT = total_Unit,
@NETSALES = NetSales ,@ROMASALAD = ISNULL(RomaSalad,0),@NET_COKE = ISNULL(Net_Coke,0),@INVOICE_CREDIT=ISNULL(InvoicesCredit,0),
@TOTAL_UNIT_PRICE = TotalUnitPrice,@Is_24_Hour = Is_24_Hour,@storeId = storeId ,@WAGE_RATE=WageRate
FROM @tmpData
IF(@TOTAL_HOURS<>0)
BEGIN
SET @PRODUCTIVITY = ROUND((@TOTAL_UNIT/@TOTAL_HOURS),2)
END
ELSE
BEGIN
SET @PRODUCTIVITY =0.0
END
SET @FOOD_COST= ROUND((@ROMASALAD+@NET_COKE)- @INVOICE_CREDIT,2)
IF(@NETSALES<>0)
BEGIN
SET @FOOD_COST_PERC=ROUND((@FOOD_COST *100)/@NETSALES,2)
SET @LABOR_COST_PERC = ((@WAGE_RATE + ((@NETSALES * @LaborVal )/100))/@NETSALES) * 100
END
ELSE
BEGIN
SET @FOOD_COST_PERC=0.0
SET @LABOR_COST_PERC =0.0
END
INSERT INTO @TBLFPRA(PRODUCTIVITY,NETSALES,RomaSalad,Net_Coke,InvoicesCredit,TotalUnitPrice,FoodCost,FoodCostPerc,Is_24_Hour,STOREID,LaborCostPer)
SELECT @PRODUCTIVITY,@NETSALES,@ROMASALAD,@NET_COKE,@INVOICE_CREDIT,@TOTAL_UNIT_PRICE,@FOOD_COST,@FOOD_COST_PERC,@IS_24_HOUR,@STOREID,@LABOR_COST_PERC
DELETE FROM @TMPDATA WHERE ID = @IDS
END
END
END
--select * from @tblFPRA
DECLARE @SALES_AVG DECIMAL(18,1)
DECLARE @LABOR_AVG DECIMAL(9,1)
DECLARE @PROD_AVG DECIMAL(9,1)
DECLARE @FOODCOKE_AVG DECIMAL(9,1)
DECLARE @FOOD_AVG DECIMAL(9,1)
DECLARE @INVENT_AVG DECIMAL(9,1)
DECLARE @tmpAvgData TABLE(NetSales_Avg decimal(18,1),LaborCostPer_Avg DECIMAL(9,1),Productivity_Avg DECIMAL(9,1),FoodCost_Avg DECIMAL(9,1),FoodCostPerc_Avg DECIMAL(9,1),TotalUnit_Avg DECIMAL(9,1))
select @sales_Avg = ROUND(avg(NetSales),1),@Labor_Avg = ROUND(avg(LaborCostPer),1),@Prod_Avg = ROUND(avg(Productivity),1),@FoodCoke_Avg = ROUND(avg(FoodCost),1),@Food_Avg = ROUND(avg(FoodCostPerc),1),@Invent_Avg = ROUND(avg(TotalUnitPrice),1) from @TBLFPRA
insert into @tmpAvgData select @SALES_AVG,@LABOR_AVG,@PROD_AVG,@FOODCOKE_AVG,@FOOD_AVG,@INVENT_AVG
select tm.storeID,tm.StoreNumber,tm.DM,tm.RM,tm.Area,p.NetSales,p.RomaSalad,p.Net_Coke,p.InvoicesCredit,isnull(p.TotalUnitPrice,0) as TotalUnitPrice ,p.Productivity,p.FoodCost,p.FoodCostPerc,LaborCostPer,
case when p.Is_24_Hour = 0 then 'N' else 'Y' end as Is_24_Hour from @tblFPRA p inner join @TempName tm on p.storeId = tm.storeID
select * from @tmpAvgData
END

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Stuart Davies SSCoach Points: 19505 More actions · Answer 1

Where in the code listing does the error come from?

Normally the reason for this error is self explanatory - you tried to assign a single value to a parameter, but got two back. SQL only wants one value - so throws the error.

Unfortunately it can take a few attempts (from my experience) in resolving this.

You need to work out which query the error is coming from then examine the underlying data to work out why.

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

This isn't going to solve your problem, but it may get you in a position where it's easier to solve it yourself.

My advice is to format your code so that it's easy to read. Below are some suggestions. You don't have to adopt them all, but please draw up some coding standards and stick to them. It'll help you to read and understand your code, it'll help your colleagues, and it'll make it far more likely that somebody will stop and help when you post it on a forum.

* Remove all commented-out lines from your code before it goes into production

* Use upper case for keywords and lower case for variables

* Put comments in your code that explain what each section does

* Use indents and line breaks so that the code is easy on the eye

* Choose variable names wisely. For example, don't call a variable @start_Date if @StartDateSelectStatement describes it better

* When posting code on this forum, use the code="sql" IFCode Shortcut so that it's displayed roughly as you'd see it in SSMS

* Use a site such as poorsql.com to do an initial format of your code

John

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 3

Find out which line is giving you the error message. It looks like you are calling another procedure. There is a chance that you get the error in the proc also. Please let us know more details which line the error is throwing.

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

You have a few issues going on here. First as previously pointed out what you posted is basically unusable because we can't read it. I ran this through a formatter to at least get a feel for what you have going on.

ALTER PROCEDURE [dbo].[UDSP_CREATE_REPORT] @Role_Id INT, --99 for all stores, 100 regionid,

@User_Id INT, --2 Weekly, 4 Quarter

@Range INT,

@start_Date NVARCHAR(max),

@end_Date NVARCHAR(max),

@Day_Name VARCHAR(50),

@Report_Type INT,

@LoginUserID INT = 0

AS

BEGIN

SET NOCOUNT ON;

DECLARE @WhereClause NVARCHAR(max)

IF (@Range = 2)

BEGIN

--Print Weekly

SET @end_Date = 'Convert(datetime,''' + @start_Date + ''',101)';

SET @start_Date = 'select DATEADD(DD,-6,Convert(Datetime,''' + @start_Date + ''',101))';

END

ELSE

IF (@Range = 3)

BEGIN

SET @end_Date = 'Convert(datetime,''' + @end_Date + ''',101)';

SET @start_Date = 'select Convert(datetime,''' + @start_Date + ''',101)';

END

ELSE

IF (@Range = 4)

BEGIN

DECLARE @Quarter NVARCHAR(100)

SET @Quarter = @start_Date

SET @start_Date = 'select case when datepart(weekday,DATEADD(quarter,(YEAR(getdate())-1900)*4+' + @start_Date + '-1,0))!=4 then

DATEADD(DD,4-datepart(weekday, DATEADD(quarter,(YEAR(getdate())-1900)*4+' + @start_Date + '-1,0)),DATEADD(quarter,(YEAR(getdate())-1900)*4+' + @start_Date + '-1,0))

else DATEADD(quarter,(YEAR(getdate())-1900)*4+' + @start_Date + '-1,0) end';

SET @end_Date = ' convert(datetime,DATEADD(quarter,(YEAR(getdate())-1900)*4+''' + @Quarter + ''',0)-1,101)';

END

DECLARE @DAY VARCHAR(10)

DECLARE @storeId INT

SET @storeId = 0

DECLARE @TEMP_STORES TABLE (

ID INT IDENTITY(1, 1),

STOREID INT,

STORENUMBER VARCHAR(50)

)

INSERT INTO @TEMP_STORES

SELECT StoreID,

StoreNumber

FROM [dbo].[Fetch_Stores](@Role_Id, @User_Id, @LoginUserID)

--select * from @TEMP_STORES

--IF(@Role_Id=99)

-- BEGIN

-- INSERT INTO @TEMP_STORES SELECT StoreID,StoreNumber FROM Stores s WHERE s.IsDeleted=0

-- END

--ELSE IF(@Role_Id=-99)

-- BEGIN

-- INSERT INTO @TEMP_STORES SELECT StoreID,StoreNumber FROM Stores s WHERE s.IsDeleted=0 and s.StoreId=@User_Id

-- END

--ELSE IF(@Role_Id=100)

-- BEGIN

-- INSERT INTO @TEMP_STORES SELECT StoreID,StoreNumber FROM Stores s WHERE s.RegionId=@User_Id and s.IsDeleted=0

-- END

--ELSE

--BEGIN

-- INSERT INTO @TEMP_STORES

-- SELECT s.StoreID,StoreNumber FROM Stores s

-- INNER JOIN UserStores us on us.StoreID=s.StoreId

-- AND us.UserId=UserId AND s.IsDeleted=0

-- AND us.UserId=@User_Id

--END

--declare @tempWages table (rate float, sdate datetime,StoreId int) -- this table for store wageRate as per stores

DECLARE @TempPayroll TABLE (

empid INT,

WedWage FLOAT,

ThuWage FLOAT,

FriWage FLOAT,

SatWage FLOAT,

SunWage FLOAT,

MonWage FLOAT,

TueWage FLOAT,

WedReg FLOAT,

ThuReg FLOAT,

FriReg FLOAT,

SatReg FLOAT,

SunReg FLOAT,

MonReg FLOAT,

TueReg FLOAT,

WedOT FLOAT,

ThuOT FLOAT,

FriOT FLOAT,

SatOT FLOAT,

SunOT FLOAT,

MonOT FLOAT,

TueOT FLOAT,

WedDT FLOAT,

ThuDT FLOAT,

FriDT FLOAT,

SatDT FLOAT,

SunDT FLOAT,

MonDT FLOAT,

TueDT FLOAT

)

DECLARE @tmpData TABLE (

ID INT IDENTITY(1, 1),

total_Unit INT,

total_Hour DECIMAL(9, 1),

NetSales DECIMAL(18, 2),

WageRate DECIMAL(9, 1),

RomaSalad DECIMAL(18, 2),

Net_Coke DECIMAL(18, 2),

InvoicesCredit DECIMAL(18, 2),

TotalUnitPrice DECIMAL(9, 1),

Is_24_Hour BIT,

storeId INT

)

DECLARE @tblFPRA TABLE (

ID INT IDENTITY(1, 1),

Productivity DECIMAL(9, 1),

NetSales DECIMAL(18, 2),

RomaSalad DECIMAL(18, 2),

Net_Coke DECIMAL(18, 2),

InvoicesCredit DECIMAL(18, 2),

TotalUnitPrice DECIMAL(18, 2),

FoodCost DECIMAL(18, 2),

FoodCostPerc DECIMAL(18, 2),

Is_24_Hour BIT,

storeId INT,

LaborCostPer DECIMAL(9, 2)

)

DECLARE @tmpHourTest TABLE (

ID INT IDENTITY(1, 1),

tot_Hour DECIMAL(9, 1),

tot_Unit INT,

stId INT

)

CREATE TABLE #tempFPRA (

sdate DATETIME,

edate DATETIME

)

INSERT INTO #tempFPRA

EXEC (@start_Date + ',' + @end_date)

--select * from #tempFPRA

DECLARE @TempName TABLE (

StoreNumber NVARCHAR(1000),

storeID INT,

Area VARCHAR(100),

DM VARCHAR(100),

RM VARCHAR(100),

rowid INT IDENTITY(1, 1)

)

INSERT INTO @TempName

SELECT *

FROM (

SELECT DISTINCT isnull(u.FirstName, '') + ' ' + isnull(u.LastName, '') AS NAME,

r.RoleName,

s.StoreNumber,

s.StoreId,

Rs.RegionName

FROM stores s

LEFT JOIN UserStores us ON s.StoreId = us.StoreID

LEFT JOIN Regions Rs ON Rs.RegionId = s.RegionId

LEFT JOIN users u ON u.userId = us.UserId

LEFT JOIN Roles r ON u.RoleId = r.RoleId

AND r.RoleId IN (

2,

4

)

AND u.ISDeleted = 0

) AS p

pivot(max(NAME) FOR Rolename IN (

DM,

RM

)) AS pvt

--select * from @TempName

DECLARE @WeekStartDate DATETIME

SET @WeekStartDate = (

SELECT sdate

FROM #tempFPRA

)

SELECT sdate

FROM #tempFPRA

IF (

SELECT COUNT(*)

FROM @TEMP_STORES

) > 0

BEGIN

WHILE (

SELECT COUNT(*)

FROM @TEMP_STORES

) > 0

BEGIN

DECLARE @STOREIDs INT

DECLARE @ID INT

SELECT TOP 1 @ID = ID,

@STOREIDs = STOREID

FROM @TEMP_STORES

DECLARE @Sql NVARCHAR(max)

DECLARE @sql1 NVARCHAR(max)

-- select * from @TEMP_STORES where ID = @ID

IF (@Report_Type = 1)

BEGIN

INSERT INTO @TempPayroll

EXEC [dbo].[Sp_Fetch_Employee_Daily_Data_Hours_Rates] @WeekStartDate,

@STOREIDs

SET @Sql = 'select Total_Unit,TOTAL_HOUR,z.Net_Sales,a.RomaSalad as RomaSalad,b.Net_Coke,d.InvoicesCredit,c.TotalUnitPrice,y.Is_24_Hour,x.StoreId from

(

select Total_Unit,StoreId

from

(

select round(SUM((ISNULL(dmd.Monday,0))+(ISNULL(dmd.Tuesday,0))+(ISNULL(dmd.Wednesday,0))+(ISNULL(dmd.Thursday,0))+(ISNULL(dmd.Friday,0))+

(ISNULL(dmd.Saturday,0))+

(ISNULL(dmd.Sunday,0))

),2) As Unit,StoreId from DccsMetricData dmd

inner join #tempFPRA td on convert(datetime,dmd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,dmd.WeekStartDate,101)<=convert(datetime,td.edate,101)

where dmd.MetricId = 17 and dmd.StoreId = ' + CAST(@STOREIDs AS VARCHAR) +

' and (IsWrongData = ''No'' or IsWrongData is null )

GROUP BY STOREID

)tmpUnit

unpivot

(

Total_Unit for tot_unit in (Unit)

)temp1

)x

left outer join

(

SELECT TOTAL_HOUR,StoreId,Is_24_Hour

FROM

(

SELECT round(SUM((ISNULL(EDD.MondayHour,0))+(ISNULL(EDD.TuesdayHour,0))+(ISNULL(EDD.WednesdayHour,0))+ (ISNULL(EDD.ThursdayHour,0))+(ISNULL(EDD.FridayHour,0))+(ISNULL(EDD.Saturdayhour,0))+

(ISNULL(EDD.SundayHour,0))),2) as hour,e.StoreId,s.Is_24_Hour FROM EMPLOYEEDAILYDATA EDD INNER JOIN Employees E ON EDD.EmpId = E.EmpId

INNER JOIN Stores S ON S.StoreId = E.StoreId

inner join #tempFPRA td on convert(datetime,edd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,edd.WeekStartDate,101)<=convert(datetime,td.edate,101)

WHERE E.StoreId = ' + CAST(@STOREIDs AS VARCHAR) +

' group by e.StoreId,Is_24_Hour

)TEST

UNPIVOT

(

TOTAL_HOUR FOR TOT_HOURS IN (hour)

)temp

)y on x.StoreId = y.StoreId

left outer join

(

SELECT Net_Sales,StoreId

FROM

(

SELECT round(SUM((ISNULL(EDD.Monday,0))+(ISNULL(EDD.Tuesday,0))+(ISNULL(EDD.Wednesday,0))+ (ISNULL(EDD.Thursday,0))+(ISNULL(EDD.Friday,0))+(ISNULL(EDD.Saturday,0))+

(ISNULL(EDD.Sunday,0))),2) as NetSales,EDD.StoreId FROM EmployeeDailyDataBottom EDD

INNER JOIN Stores S ON S.StoreId = EDD.StoreId

inner join #tempFPRA td on convert(datetime,edd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,edd.WeekStartDate,101)<=convert(datetime,td.edate,101)

WHERE edd.Name=''netsales'' and EDD.StoreId = ' + CAST(@STOREIDs AS VARCHAR) +

' GROUP BY EDD.StoreID

)TEST

UNPIVOT

(

Net_Sales FOR Sales IN (NetSales)

)temp

)z on z.StoreId = y.StoreId

left outer join

(

SELECT RomaSalad,StoreId

FROM

(

SELECT round(SUM((ISNULL(EDD.Monday,0))+(ISNULL(EDD.Tuesday,0))+(ISNULL(EDD.Wednesday,0))+ (ISNULL(EDD.Thursday,0))+(ISNULL(EDD.Friday,0))+(ISNULL(EDD.Saturday,0))+

(ISNULL(EDD.Sunday,0))),2) as RomaSalaid,EDD.StoreId FROM EmployeeDailyDataBottom EDD

INNER JOIN Stores S ON S.StoreId = EDD.StoreId

inner join #tempFPRA td on convert(datetime,edd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,edd.WeekStartDate,101)<=convert(datetime,td.edate,101)

WHERE EDD.Name=''romasalad'' and EDD.StoreId =' + CAST(@STOREIDs AS VARCHAR) +

' GROUP BY EDD.StoreID

)TEST

UNPIVOT

(

RomaSalad FOR Salad IN (RomaSalaid)

)temp

) a on a.StoreID = z.StoreID

left outer join

(

SELECT Net_Coke,StoreId

FROM

(

SELECT round(SUM((ISNULL(EDD.Monday,0))+(ISNULL(EDD.Tuesday,0))+(ISNULL(EDD.Wednesday,0))+ (ISNULL(EDD.Thursday,0))+(ISNULL(EDD.Friday,0))+(ISNULL(EDD.Saturday,0))+

(ISNULL(EDD.Sunday,0))),2) as Coke,EDD.StoreId FROM EmployeeDailyDataBottom EDD

INNER JOIN Stores S ON S.StoreId = EDD.StoreId

inner join #tempFPRA td on convert(datetime,edd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,edd.WeekStartDate,101)<=convert(datetime,td.edate,101)

WHERE EDD.Name=''coke'' and EDD.StoreId =' + CAST(@STOREIDs AS VARCHAR) +

' GROUP BY EDD.StoreID

)TEST

UNPIVOT

(

Net_Coke FOR Cokes IN (Coke)

)temp

)b on b.storeId = a.StoreId

left outer join

(

SELECT TotalUnitPrice,StoreId

(

SELECT SUM(CONVERT(DECIMAL(18, 2), mw.UnitPrice*mwd.Variance )) AS TotalUnitPrice,mwd.StoreId from MasterWISRData mwd

inner JOIN MasterWISR mw ON mw.WisrId=mwd.MasterwiserId

inner join Stores S ON S.StoreId = mwd.StoreId

inner join Regions Rs ON Rs.RegionId=S.RegionId

inner join #tempFPRA td on convert(datetime,mwd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,mwd.WeekStartDate,101)<=convert(datetime,td.edate,101)

WHERE mw.UnitPrice IS NOT NULL and mwd.StoreId =' + CAST(@STOREIDs AS VARCHAR) +

' group by mwd.StoreId

)TEST

)c on c.StoreId=b.StoreId

left outer join

(

SELECT InvoicesCredit,StoreId

FROM

(

SELECT round(SUM((ISNULL(EDD.Monday,0))+(ISNULL(EDD.Tuesday,0))+(ISNULL(EDD.Wednesday,0))+ (ISNULL(EDD.Thursday,0))+(ISNULL(EDD.Friday,0))+(ISNULL(EDD.Saturday,0))+

(ISNULL(EDD.Sunday,0))),2) as Credit,EDD.StoreId FROM EmployeeDailyDataBottom EDD

INNER JOIN Stores S ON S.StoreId = EDD.StoreId

inner join #tempFPRA td on convert(datetime,edd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,edd.WeekStartDate,101)<=convert(datetime,td.edate,101)

WHERE EDD.Name=''romacokecredit'' and EDD.StoreId =' + CAST(@STOREIDs AS VARCHAR) + ' GROUP BY EDD.StoreID

)TEST

UNPIVOT

(

InvoicesCredit FOR InvoicesCredits IN (Credit)

)temp

)d on d.storeId = c.StoreId'

--print @sql

END

ELSE

BEGIN

INSERT INTO @TempPayroll

EXEC [dbo].[Sp_Fetch_Employee_Daily_Data_Hours_Rates] @WeekStartDate,

@STOREIDs

SET @Sql = 'select Total_Unit,TOTAL_HOUR,z.Net_Sales,a.RomaSalad as RomaSalad,b.Net_Coke,d.InvoicesCredit,c.TotalUnitPrice,y.Is_24_Hour,x.StoreId from

(

select Total_Unit,StoreId

from

(

select round(SUM(ISNULL(' + @Day_Name + ',0)),2) AS Unit,StoreId from DccsMetricData dmd

inner join #tempFPRA td on convert(datetime,dmd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,dmd.WeekStartDate,101)<=convert(datetime,td.edate,101)

where MetricId = 17 and StoreId = ' + CAST(@STOREIDs AS VARCHAR) + ' and (IsWrongData = ''No'' or IsWrongData is null ) GROUP BY STOREID

)tmpUnit

unpivot

(

Total_Unit for tot_unit in (Unit)

)temp1

)x

left outer join

(

SELECT TOTAL_HOUR,StoreId,Is_24_Hour

FROM

(

SELECT isnull(ROUND(SUM(EDD.' + @Day_Name + 'Hour' +

'),2),0.0) as hour,e.StoreId,s.Is_24_Hour FROM EMPLOYEEDAILYDATA EDD INNER JOIN Employees E ON EDD.EmpId = E.EmpId

INNER JOIN Stores S ON S.StoreId = E.StoreId

inner join #tempFPRA td on convert(datetime,edd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,edd.WeekStartDate,101)<=convert(datetime,td.edate,101)

WHERE E.StoreId = ' + CAST(@STOREIDs AS VARCHAR) + ' group by e.StoreId,Is_24_Hour

)TEST

UNPIVOT

(

TOTAL_HOUR FOR TOT_HOURS IN (hour)

)temp

)y on x.StoreId = y.StoreId

left outer join

(

SELECT Net_Sales,StoreId

FROM

(

SELECT isnull(ROUND(EDD.' + @Day_Name + ',2),0.0) as NetSales,EDD.StoreId FROM EmployeeDailyDataBottom EDD

INNER JOIN Stores S ON S.StoreId = EDD.StoreId

inner join #tempFPRA td on convert(datetime,edd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,edd.WeekStartDate,101)<=convert(datetime,td.edate,101)

WHERE edd.Name=''netsales'' and EDD.StoreId = ' + CAST(@STOREIDs AS VARCHAR) +

'

)TEST

UNPIVOT

(

Net_Sales FOR Sales IN (NetSales)

)temp

)z on z.StoreId = y.StoreId

left outer join

(

SELECT RomaSalad,StoreId

FROM

(

SELECT isnull(ROUND(EDD.' + @Day_Name + ',2),0.0) as RomaSalaid,EDD.StoreId FROM EmployeeDailyDataBottom EDD

INNER JOIN Stores S ON S.StoreId = EDD.StoreId

inner join #tempFPRA td on convert(datetime,edd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,edd.WeekStartDate,101)<=convert(datetime,td.edate,101)

WHERE edd.Name=''romasalad'' and EDD.StoreId = ' + CAST(@STOREIDs AS VARCHAR) + '

)TEST

UNPIVOT

(

RomaSalad FOR Salad IN (RomaSalaid)

)temp

) a on a.StoreId=z.StoreId

left outer join

(

SELECT Net_Coke,StoreId

FROM

(

SELECT isnull(ROUND(EDD.' + @Day_Name +

',2),0.0) as Coke,EDD.StoreId FROM EmployeeDailyDataBottom EDD

INNER JOIN Stores S ON S.StoreId = EDD.StoreId

inner join #tempFPRA td on convert(datetime,edd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,edd.WeekStartDate,101)<=convert(datetime,td.edate,101)

WHERE edd.Name=''coke'' and EDD.StoreId = ' + CAST(@STOREIDs AS VARCHAR) + '

)TEST

UNPIVOT

(

Net_Coke FOR Cokes IN (Coke)

)temp

)b on b.StoreId=a.StoreId

left outer join

(

SELECT InvoicesCredit,StoreId

FROM

(

SELECT isnull(ROUND(EDD.' + @Day_Name + ',2),0.0) as Credit,EDD.StoreId FROM EmployeeDailyDataBottom EDD

INNER JOIN Stores S ON S.StoreId = EDD.StoreId

inner join #tempFPRA td on convert(datetime,edd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,edd.WeekStartDate,101)<=convert(datetime,td.edate,101)

WHERE edd.Name=''romasaladcokecredit'' and EDD.StoreId = ' + CAST(@STOREIDs AS VARCHAR) +

'

)TEST

UNPIVOT

(

InvoicesCredit FOR InvoicesCredits IN (Credit)

)temp

)d on b.StoreId=d.StoreId

left outer join

( SELECT TotalUnitPrice,StoreId

FROM

(

SELECT SUM(CONVERT(DECIMAL(9, 1), mw.UnitPrice*mwd.Variance )) AS TotalUnitPrice,mwd.StoreId from MasterWISRData mwd

inner JOIN MasterWISR mw ON mw.WisrId=mwd.MasterwiserId

inner join Stores S ON S.StoreId = mwd.StoreId

inner join Regions Rs ON Rs.RegionId=S.RegionId

inner join #tempFPRA td on convert(datetime,mwd.WeekStartDate,101)>=convert(datetime,td.sdate,101) and convert(datetime,mwd.WeekStartDate,101)<=convert(datetime,td.edate,101)

WHERE mw.UnitPrice IS NOT NULL and mwd.StoreId =' + CAST(@STOREIDs AS VARCHAR) + ' group by mwd.StoreId

)TEST

)c on c.StoreId=d.StoreId

'

--PRINT @SQL

END

INSERT INTO @tmpData (

total_Unit,

total_Hour,

NetSales,

RomaSalad,

Net_Coke,

InvoicesCredit,

TotalUnitPrice,

Is_24_Hour,

storeId

)

EXEC (@Sql)

UPDATE @tmpData

SET WageRate = (

SELECT Sum((tpr.WedWage * tpr.WedReg) + (tpr.WedWage * tpr.WedOT * 1.5) + (tpr.WedWage * tpr.WedDT * 2) + (tpr.ThuWage * tpr.ThuReg) + (tpr.ThuWage * tpr.ThuOT * 1.5) + (tpr.ThuWage * tpr.ThuDT * 2) + (tpr.FriWage * tpr.FriReg) + (tpr.FriWage * tpr.FriOT * 1.5) + (tpr.FriWage * tpr.FriDT * 2) + (tpr.SatWage * tpr.SatReg) + (tpr.SatWage * tpr.SatOT * 1.5) + (tpr.SatWage * tpr.SatDT * 2) + (tpr.SunWage * tpr.SunReg) + (tpr.SunWage * tpr.SunOT * 1.5) + (tpr.SunWage * tpr.SunDT * 2) + (tpr.MonWage * tpr.MonReg) + (tpr.MonWage * tpr.MonOT * 1.5

) + (tpr.MonWage * tpr.MonDT * 2) + (tpr.TueWage * tpr.TueReg) + (tpr.TueWage * tpr.TueOT * 1.5) + (tpr.TueWage * tpr.TueDT * 2)) AS labor

FROM @TempPayroll tpr

)

WHERE storeId = @STOREIDs

DELETE

FROM @TEMP_STORES

WHERE ID = @ID

DELETE

FROM @TempPayroll

END

--select * from @tmpData

DECLARE @LaborVal INT

SET @LaborVal = (

SELECT b.LaborValue

FROM Buffer b

WHERE b.BufferYear = datename(YY, getdate())

)

IF (

SELECT COUNT(*)

FROM @tmpData

) > 0

BEGIN

WHILE (

SELECT COUNT(*)

FROM @tmpData

) > 0

BEGIN

DECLARE @IDs INT

DECLARE @TOTAL_HOURS DECIMAL(9, 1)

DECLARE @TOTAL_UNIT DECIMAL(9, 1)

DECLARE @PRODUCTIVITY DECIMAL(9, 1)

DECLARE @NETSALES DECIMAL(18, 1)

DECLARE @ROMASALAD DECIMAL(18, 1)

DECLARE @NET_COKE DECIMAL(18, 1)

DECLARE @INVOICE_CREDIT DECIMAL(18, 1)

DECLARE @TOTAL_UNIT_PRICE DECIMAL(18, 1)

DECLARE @FOOD_COST DECIMAL(18, 2)

DECLARE @FOOD_COST_PERC DECIMAL(18, 2)

DECLARE @WAGE_RATE DECIMAL(9, 1)

DECLARE @LABOR_COST_PERC DECIMAL(9, 1)

DECLARE @Is_24_Hour BIT

DECLARE @StorId INT

SELECT TOP 1 @IDs = ID,

@TOTAL_HOURS = total_Hour,

@TOTAL_UNIT = total_Unit,

@NETSALES = NetSales,

@ROMASALAD = ISNULL(RomaSalad, 0),

@NET_COKE = ISNULL(Net_Coke, 0),

@INVOICE_CREDIT = ISNULL(InvoicesCredit, 0),

@TOTAL_UNIT_PRICE = TotalUnitPrice,

@Is_24_Hour = Is_24_Hour,

@storeId = storeId,

@WAGE_RATE = WageRate

FROM @tmpData

IF (@TOTAL_HOURS <> 0)

BEGIN

SET @PRODUCTIVITY = ROUND((@TOTAL_UNIT / @TOTAL_HOURS), 2)

END

ELSE

BEGIN

SET @PRODUCTIVITY = 0.0

END

SET @FOOD_COST = ROUND((@ROMASALAD + @NET_COKE) - @INVOICE_CREDIT, 2)

IF (@NETSALES <> 0)

BEGIN

SET @FOOD_COST_PERC = ROUND((@FOOD_COST * 100) / @NETSALES, 2)

SET @LABOR_COST_PERC = ((@WAGE_RATE + ((@NETSALES * @LaborVal) / 100)) / @NETSALES) * 100

END

ELSE

BEGIN

SET @FOOD_COST_PERC = 0.0

SET @LABOR_COST_PERC = 0.0

END

INSERT INTO @TBLFPRA (

PRODUCTIVITY,

NETSALES,

RomaSalad,

Net_Coke,

InvoicesCredit,

TotalUnitPrice,

FoodCost,

FoodCostPerc,

Is_24_Hour,

STOREID,

LaborCostPer

)

SELECT @PRODUCTIVITY,

@NETSALES,

@ROMASALAD,

@NET_COKE,

@INVOICE_CREDIT,

@TOTAL_UNIT_PRICE,

@FOOD_COST,

@FOOD_COST_PERC,

@IS_24_HOUR,

@STOREID,

@LABOR_COST_PERC

DELETE

FROM @TMPDATA

WHERE ID = @IDS

END

--select * from @tblFPRA

DECLARE @SALES_AVG DECIMAL(18, 1)

DECLARE @LABOR_AVG DECIMAL(9, 1)

DECLARE @PROD_AVG DECIMAL(9, 1)

DECLARE @FOODCOKE_AVG DECIMAL(9, 1)

DECLARE @FOOD_AVG DECIMAL(9, 1)

DECLARE @INVENT_AVG DECIMAL(9, 1)

DECLARE @tmpAvgData TABLE (

NetSales_Avg DECIMAL(18, 1),

LaborCostPer_Avg DECIMAL(9, 1),

Productivity_Avg DECIMAL(9, 1),

FoodCost_Avg DECIMAL(9, 1),

FoodCostPerc_Avg DECIMAL(9, 1),

TotalUnit_Avg DECIMAL(9, 1)

)

SELECT @sales_Avg = ROUND(avg(NetSales), 1),

@Labor_Avg = ROUND(avg(LaborCostPer), 1),

@Prod_Avg = ROUND(avg(Productivity), 1),

@FoodCoke_Avg = ROUND(avg(FoodCost), 1),

@Food_Avg = ROUND(avg(FoodCostPerc), 1),

@Invent_Avg = ROUND(avg(TotalUnitPrice), 1)

FROM @TBLFPRA

INSERT INTO @tmpAvgData

SELECT @SALES_AVG,

@LABOR_AVG,

@PROD_AVG,

@FOODCOKE_AVG,

@FOOD_AVG,

@INVENT_AVG

SELECT tm.storeID,

tm.StoreNumber,

tm.DM,

tm.RM,

tm.Area,

p.NetSales,

p.RomaSalad,

p.Net_Coke,

p.InvoicesCredit,

isnull(p.TotalUnitPrice, 0) AS TotalUnitPrice,

p.Productivity,

p.FoodCost,

p.FoodCostPerc,

LaborCostPer,

CASE

WHEN p.Is_24_Hour = 0

THEN 'N'

ELSE 'Y'

END AS Is_24_Hour

FROM @tblFPRA p

INNER JOIN @TempName tm ON p.storeId = tm.storeID

SELECT *

FROM @tmpAvgData

END

Now we have a 645 line stored proc with a subquery somewhere. Maybe this isn't too bad to find. However, you have dozens of subselects buried in dynamic sql. You even call other stored procedures in the middle of this.

Finding the problem that you are looking for is challenging. The bigger issue is that you have a time bomb on your hands. This proc is going to come to a grinding halt at some point. You have 2 while loops in here, I can't tell if they are nested or not but I think the first one completes before the second one starts. You also have multiple execution paths and very likely you are going to get killed with parameter sniffing at some point.

http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths

http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]

Last but not least, you are using the float datatype for money. The float type is an approximate datatype. It should be avoided when dealing with money. You should instead use a numeric datatype.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

MMartin1 One Orange Chip Points: 27795 More actions · Answer 5

I think if this is your code you may have a clue with isolating where the problem may be rather than providing a entire code dump. But I did find -->

insert into #tempFPRA exec (@start_Date+','+@end_date)

...

SET @WeekStartDate = (SELECT sdate FROM #tempFPRA)

If #tempFPRA has more than one row at this point, this will cause the exception you speak of. I don't know if this helps.

----------------------------------------------------

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

I spotted that one, as well as "SET @LaborVal = (select b.LaborValue from Buffer b where b.BufferYear = datename(YY,getdate()))" later.

These could be rewritten several ways. They all will execute without error, but the first two are equivalent to picking a random date. The last two give you control over which date is picked.

SELECT @WeekStartDate = sdate FROM #tempFPRA

SELECT TOP 1 @WeekStartDate = sdate FROM #tempFPRA

SELECT @WeekStartDate = MIN(sdate) FROM #tempFPRA

SELECT TOP 1 @WeekStartDate = sdate FROM #tempFPRA ORDER BY sdate