Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an... Expand / Collapse
Author
Message
Posted Sunday, July 14, 2013 11:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 20, 2014 9:22 PM
Points: 5, Visits: 32
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
(
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
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




Post #1473455
Posted Monday, July 15, 2013 2:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:40 AM
Points: 2,990, Visits: 3,119
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
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
Post #1473518
Posted Monday, July 15, 2013 2:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:55 AM
Points: 5,208, Visits: 9,365
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
Post #1473525
Posted Monday, July 15, 2013 7:38 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:03 AM
Points: 731, Visits: 1,992
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.
Post #1473648
Posted Monday, July 15, 2013 8:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:51 PM
Points: 13,028, Visits: 11,838
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
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

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/

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1473663
Posted Wednesday, July 31, 2013 9:59 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 5:56 PM
Points: 289, Visits: 783
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.
Post #1479564
Posted Thursday, August 1, 2013 10:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 10:10 AM
Points: 2,837, Visits: 1,133
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




Post #1480029
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse