4 Replies - 796 Views - Last Post: 28 June 2019 - 05:20 AM

#1 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,218
  • Joined: 12-January 10

get sum of an entire shift and also return the hours production

Posted 20 June 2019 - 12:12 PM

so i need to be able to return the sum of an entire shift and return the product usage for the hour

the shift can run multiple products

here is the current output for the hour

txtproductname	basepoundsperhour	productid	poundsperhour	shiftid	hourlytransactionsid
NUGGETS	29000	1	20000	6	8
BUFFINGS	20300	2	0	6	9


its sql:

declare	 @TransTime int = 7,
	 @PlantID int = 5,
	 @ShiftID int = 6,
	 @Trans_Date date = '2019-06-20',
	 @LineID int = 1
	-- (SELECT dbo.aaa_fnProductionShiftPlantReturn.SHIFTPOUNDS FROM dbo.aaa_fnProductionShiftPlantReturn(6,'2019-06-20',0))AS  SHIFTPOUNDS

SELECT tproduct.txtproductname, 
       tplantproducts.basepoundsperhour, 
       tplantproducts.productid, 
       Isnull(thourlytransactions.poundsperhour, 0) AS poundsperhour, 
       thourlytransactions.shiftid, 
       thourlytransactions.hourlytransactionsid
FROM   tproduct 
       INNER JOIN tplantproducts 
               ON tproduct.productid = tplantproducts.productid 
                  AND tplantproducts.plantid = @PlantID 
       INNER JOIN tplantlineproducts 
               ON tplantlineproducts.productid = tplantproducts.productid 
                  AND tplantlineproducts.lineid = @LineID 
                  AND tplantlineproducts.plantid = @PlantID 
       LEFT OUTER JOIN thourlytransactions 
                    ON thourlytransactions.productid = tplantproducts.productid 
                       AND thourlytransactions.transstarttime = @TransTime 
                       AND thourlytransactions.trans_date = @Trans_Date 
                       AND thourlytransactions.shiftid = @ShiftID 


i made a function:

(	
	-- Add the parameters for the function here
	@shiftID int,
	@transDate date,
	@asActive int
)
RETURNS @ShiftProductionFN TABLE 
 ( 
       [ShiftPounds] int NULL, 
	   [ProductID] int null
      		   
    ) 
AS
begin 
INSERT INTO @ShiftProductionFN


	SELECT sum(PoundsPerHour) as ShiftPounds, tHourlyTransactions.ProductID from tHourlyTransactions
	inner join tShiftDetails
	on tShiftDetails.ShiftIDetailID = tHourlyTransactions.ShiftID
	INNER JOIN tProduct
	on tProduct.ProductID = tHourlyTransactions.ProductID 
	where tShiftDetails.ShiftIDetailID = @shiftID and tHourlyTransactions.Trans_Date = @transDate and tHourlyTransactions.asActive = @asActive
	GROUP BY tHourlyTransactions.ProductID


	return
end


its return:

ShiftPounds	ProductID
20007	1
7	2


i need to place that shift pounds into the query above

i have tried adding this:
SELECT tproduct.txtproductname, 
       tplantproducts.basepoundsperhour, 
       tplantproducts.productid, 
       Isnull(thourlytransactions.poundsperhour, 0) AS poundsperhour, 
       thourlytransactions.shiftid, 
       thourlytransactions.hourlytransactionsid, (SELECT dbo.aaa_fnProductionShiftPlantReturn.SHIFTPOUNDS FROM dbo.aaa_fnProductionShiftPlantReturn(@ShiftID, @Trans_Date,0) INNER JOIN thourlytransactions ON  thourlytransactions.productid = dbo.aaa_fnProductionShiftPlantReturn.productid )AS  SHIFTPOUNDS
FROM   tproduct


i receive 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.



this is the table output where the info for pounds is coming from:
5	5	2	2019-06-20	8	6	False
6	6	1	2019-06-20	6	7	False
7	6	2	2019-06-20	6	7	False
8	6	1	2019-06-20	7	20000	False
9	6	2	2019-06-20	7	0	False



any thoughts?

Is This A Good Question/Topic? 0
  • +

Replies To: get sum of an entire shift and also return the hours production

#2 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,218
  • Joined: 12-January 10

Re: get sum of an entire shift and also return the hours production

Posted 21 June 2019 - 05:16 AM

bumping myself
Was This Post Helpful? 0
  • +
  • -

#3 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,218
  • Joined: 12-January 10

Re: get sum of an entire shift and also return the hours production

Posted 21 June 2019 - 05:44 AM

this is how i fixed it

SELECT tproduct.txtproductname, 
       tplantproducts.basepoundsperhour, 
       tplantproducts.productid, 
       Isnull(thourlytransactions.poundsperhour, 0) AS poundsperhour, 
       thourlytransactions.shiftid, 
       thourlytransactions.hourlytransactionsid--,-- isnull(sum(thourlytransactions.poundsperhour),0) as shiftpounds
	   ,isnull((SELECT dbo.aaa_fnProductionShiftPlantReturn.SHIFTPOUNDS FROM dbo.aaa_fnProductionShiftPlantReturn(@ShiftID, @Trans_Date,0, thourlytransactions.productid) ),0) AS  SHIFTPOUNDS

Was This Post Helpful? 0
  • +
  • -

#4 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,218
  • Joined: 12-January 10

Re: get sum of an entire shift and also return the hours production

Posted 27 June 2019 - 11:51 AM

so i thought i had it fixed however it isnt

any thoughts on how i can fix it--

currently it only returns the sum for a specific hour and not a running sum
Was This Post Helpful? 0
  • +
  • -

#5 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,218
  • Joined: 12-January 10

Re: get sum of an entire shift and also return the hours production

Posted 28 June 2019 - 05:20 AM

fixed

	SELECT tproduct.txtproductname,

       tplantproducts.basepoundsperhour,

       tplantproducts.productid,

       Isnull(thourlytransactions.poundsperhour, 0) AS poundsperhour,

       thourlytransactions.shiftid,

       thourlytransactions.hourlytransactionsid--,-- isnull(sum(thourlytransactions.poundsperhour),0) as shiftpounds

       ,isnull((SELECT dbo.aaa_fnProductionShiftPlantReturn.SHIFTPOUNDS FROM dbo.aaa_fnProductionShiftPlantReturn(@ShiftID, @Trans_Date,0, tplantproducts.productid) ),0) AS  SHIFTPOUNDS

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1