Mardus Davel Mardus Davel - 6 months ago 6
SQL Question

Sub Query Error, how to solve

When running the below query, I get an error:


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


I know what it means, but am unsure how to resolve it, I was thinking of creating a For Each Loop, that will compare the sites in my budget table and return the value based on the different sites.

Your input will be greatly appreciated.

Declare @FromDate DateTime
DEclare @ToDate DateTime
Declare @WACost Numeric (18,2)
Declare @WABill Numeric (18,2)
Declare @FBill Numeric (18, 2)
Declare @FCost Numeric (18,2)

Set @FromDate = '20160501'
Set @ToDate = '20160531'

Set @WACost = (
Select
sum(dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH) + Sum((dbo.FleetData.OwnCost + dbo.FleetData.OpCost)
* dbo.DataInput.Hrs)
From dbo.DataInput INNER JOIN
dbo.HRData ON dbo.DataInput.HRId = dbo.HRData.HRId INNER JOIN
dbo.FleetData ON dbo.FleetData.FleetId = dbo.DataInput.FleetId INNER JOIN
dbo.WorkCodes ON dbo.DataInput.WorkCodeId = dbo.WorkCodes.WorkCodeID
WHERE ProdDate >= @FromDate AND ProdDate <= @ToDate and dbo.workcodes.WorkCode = 101
)

Set @WABill = (
Select
sum(dbo.DataInput.ShiftHrs * dbo.HRDATA.RateBill) + Sum((dbo.FleetData.OwnBill + dbo.FleetData.OpBill)
* dbo.DataInput.Hrs)
From dbo.Datainput Inner Join
dbo.FleetData ON dbo.FleetData.FleetId = dbo.DataInput.FleetId INNER JOIN
dbo.HRData ON dbo.DataInput.HRId = dbo.HRData.HRId INNER JOIN
dbo.WorkCodes ON dbo.DataInput.WorkCodeId = dbo.WorkCodes.WorkCodeID

WHERE ProdDate >= @FromDate AND ProdDate <= @ToDate and dbo.WorkCodes.WorkCode = 101
)

Set @FBill = (
Select
((dbo.Budget.SalWage * (1 + dbo.Budget.SalWagePerc)) + dbo.budget.PG0 +
(dbo.budget.PG * (1 + PGPerc))) / (DAY(EOMONTH(@ToDate))) * Day(@ToDate)

From dbo.budget

WHERE Period >= Month(@FromDate) and Period <= Month(@ToDate)
)

Set @FCost = (
Select (dbo.Budget.SalWage + dbo.budget.PG0 + dbo.budget.PG) /
(DAY(EOMONTH(@ToDate))) * Day(@ToDate)

From dbo.budget

WHERE Period >= Month(@FromDate) and Period <= Month(@ToDate)
)

SELECT

@WACost as WACostTotal, @WABill as WABillTotal, @FBill as FBillCalc, @FCost as FCostCalc,

dbo.DataInput.DataInputID as DataInpID, dbo.DataInput.ProdDate as ProdDate, dbo.WorkCodes.WorkCode as WCode, dbo.DataInput.Hrs as ProdHrs, dbo.DataInput.Loads as ProdLoads,
dbo.DataInput.Fuel as ProdFuel, dbo.FleetData.FleetNo as ProdFltNo, dbo.JobCodes.JobCode,
dbo.FleetType.EquipmentType as EqType, dbo.FleetModel.ModelName as Model, dbo.Sites.SiteName as ProdSiteName,
dbo.JobCodes.JobDetails as JCDet, dbo.JobCodes.BillMeth as BillM,
dbo.FleetData.OwnCost as FltOwnCost, dbo.fleetdata.OpCost as FltOpCost, dbo.fleetdata.OwnBill as FltOwnBill,
dbo.FleetData.OpBill as FltOpBill,

(dbo.FleetModel.ModelSize * dbo.DataInput.Loads) as ProdBCM,
dbo.HRData.EmpNo as HREmp, dbo.HRData.RatePH as EmpCost, dbo.HRData.RateBill as EmpBill,
dbo.DataInput.ShiftHrs as ProdShHrs, dbo.FleetModel.ModelSize as MSize,
(dbo.DataInput.Fuel * dbo.JobCodes.FuelRate) as ProdFuelCost,
dbo.budget.BudDate as BdgDate, dbo.Budget.RevBCM as BdgRevBCM, dbo.Budget.PricePerBCM as BdgBCMPrice,
dbo.budget.BCMValue as BdgBCMVal, dbo.budget.SalWage as BdgSalWage, dbo.budget.Fuel as FuelBdg,
dbo.budget.PG0 as PGZero, dbo.budget.PG as PGCP,
dbo.budget.siteId as BudgSite,

--LAST DAY BCM
IIF(dbo.DataInput.ProdDate = @ToDate, dbo.DataInput.Loads * dbo.FleetModel.ModelSize, 0) as LDBCM,

--BILLING CALCS

--PG Billing
IIF(dbo.Workcodes.WorkCode = 101,
(dbo.Budget.SalWage * (1 + dbo.Budget.SalWagePerc)) + dbo.budget.PG0 +
(dbo.budget.PG * (1 + PGPerc)),0) as PGBill,

--FleetBill
IIF(dbo.Workcodes.WorkCode = 101,
(FleetData.OwnBill + FleetData.OpBill) * DataInput.Hrs,0) as FleetBill,

--LabourCost
iif(dbo.WorkCodes.WorkCode = 101,
(dbo.DataInput.ShiftHrs * dbo.HRDATA.RateBill),0) as LabourBill,

--Total Variable Bill
iif(dbo.Workcodes.WorkCode = 101,
(dbo.DataInput.ShiftHrs * dbo.HRDATA.RateBill) +
(FleetData.OwnBill + FleetData.OpBill) * DataInput.Hrs,0) as TotalVarBill,

--WABill
iif(dbo.Workcodes.WorkCode = 101,
((dbo.DataInput.ShiftHrs * dbo.HRDATA.RateBill) +
(FleetData.OwnBill + FleetData.OpBill) * DataInput.Hrs) /
@WABill * @FBill +
((dbo.DataInput.ShiftHrs * dbo.HRDATA.RateBill) +
(FleetData.OwnBill + FleetData.OpBill) * DataInput.Hrs), 0) as WABill,

--COST CALCS

--PG Cost

IIF(dbo.WorkCodes.WorkCode = 101,
(dbo.Budget.SalWage + dbo.budget.PG0 + dbo.budget.PG),0) as PGCost,

--FLEETCOST
IIF(dbo.WorkCodes.WorkCode = 101,
(FleetData.OwnCost + FleetData.OpCost) * DataInput.Hrs,0) as FleetCost,

--LabourCost
iif(dbo.WorkCodes.WorkCode = 101,
(dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH),0) as LabourCost,

--Total Variables Cost
iif(dbo.WorkCodes.WorkCode = 101,
(dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH) +
(FleetData.OwnCost + FleetData.OpCost) * DataInput.Hrs,0) as TotalVarCost,

--WAOnly
iif(dbo.WorkCodes.WorkCode = 101,
((dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH) +
(FleetData.OwnCost + FleetData.OpCost) * DataInput.Hrs) /
@WACost * @FCost,0)
as WACalc,

--WA
iif(dbo.WorkCodes.WorkCode = 101,
((dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH) +
(FleetData.OwnCost + FleetData.OpCost) * DataInput.Hrs) /
@WACost * @FCost +
((dbo.DataInput.ShiftHrs * dbo.HRDATA.RatePH) +
(FleetData.OwnCost + FleetData.OpCost) * DataInput.Hrs) , 0) as WACost


FROM dbo.DataInput INNER JOIN
dbo.FleetData ON dbo.FleetData.FleetId = dbo.DataInput.FleetId INNER JOIN
dbo.FleetMake ON dbo.FleetData.FleetMakeId = dbo.FleetMake.FleetMakeId INNER JOIN
dbo.FleetModel ON dbo.FleetData.FleetModelId = dbo.FleetModel.ModelId INNER JOIN
dbo.FleetType ON dbo.FleetData.FleetTypeId = dbo.FleetType.FleetTypeId INNER JOIN
dbo.HRData ON dbo.DataInput.HRId = dbo.HRData.HRId INNER JOIN
dbo.Sites ON dbo.DataInput.SiteId = dbo.Sites.SiteId INNER JOIN
dbo.Budget ON dbo.Sites.SiteId = dbo.Budget.SiteId AND dbo.DataInput.Period = dbo.Budget.Period INNER JOIN
dbo.WorkCodes ON dbo.DataInput.WorkCodeId = dbo.WorkCodes.WorkCodeID INNER JOIN
dbo.JobCodes ON dbo.DataInput.JobCodeId = dbo.JobCodes.JobCodeId

WHERE ProdDate >= @FromDate AND ProdDate <= @ToDate

Group By dbo.DataInput.DataInputID, dbo.DataInput.DataInputID, dbo.DataInput.ProdDate, dbo.WorkCodes.WorkCode, dbo.DataInput.Hrs,
dbo.DataInput.Loads, dbo.DataInput.Fuel, dbo.FleetData.FleetNo, dbo.JobCodes.JobCode,
dbo.FleetType.EquipmentType, dbo.FleetModel.ModelName, dbo.Sites.SiteName,
dbo.JobCodes.JobDetails, dbo.JobCodes.BillMeth, dbo.FleetData.OwnCost, dbo.fleetdata.OpCost,
dbo.fleetdata.OwnBill, dbo.FleetData.OpBill,dbo.HRData.EmpNo, dbo.HRData.RatePH, dbo.HRData.RateBill,
dbo.DataInput.ShiftHrs, dbo.FleetModel.ModelSize, dbo.budget.BudDate, dbo.Budget.RevBCM, dbo.Budget.PricePerBCM,
dbo.budget.BCMValue, dbo.budget.SalWage, dbo.budget.Fuel, dbo.budget.PG0, dbo.budget.PG,
dbo.budget.siteId, dbo.jobcodes.FuelRate, dbo.datainput.workcodeid, dbo.budget.SalWagePerc, dbo.Budget.PGPerc,
dbo.WorkCodes.WorkCodeID

Order By dbo.DataInput.ProdDate

Answer

Below is the place where you are prone to get error..

Set @FBill = (
             Select
                ((dbo.Budget.SalWage * (1 + dbo.Budget.SalWagePerc)) + dbo.budget.PG0 + 
                (dbo.budget.PG * (1 + PGPerc))) / (DAY(EOMONTH(@ToDate))) * Day(@ToDate)

             From dbo.budget 

             WHERE Period >= Month(@FromDate) and Period <= Month(@ToDate)
              )

Set @FCost = (
                Select (dbo.Budget.SalWage + dbo.budget.PG0 + dbo.budget.PG) / 
                (DAY(EOMONTH(@ToDate))) * Day(@ToDate)

                From dbo.budget 

                WHERE Period >= Month(@FromDate) and Period <= Month(@ToDate)
)

You can use top and order by to return only one record like below

set @fbill=select top 1 from table order by somevalue
Comments