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

  • 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

  • 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

  • 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

  • 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.

  • 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/[/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/

  • 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.

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

  • 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

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

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