0

I have this select query

   SELECT 
  info.[OFNumber] 
  --Get sum of item quantity from the same category (all quantity before production) PS_ProjectInfo table
  ,SUM(Case WHEN info.[FK_PSCategory]=2 then ((info.Length * info.[Quantity] )/1000)Else 0 end ) as 'm'
  
 -- Get sum of item quantity from the same category (Only quantity produced) PS_DailyProduction table
 ,SUM(Case WHEN info.[FK_PSCategory]=2 then PS_DailyProduction.Quantity ELSE 0 END) as 'qty'

FROM [dbo].[PS_DailyProduction] 
Left JOIN PS_ProjectInfo info on info.id=PS_DailyProduction.[FK_OF]

GROUP BY info.[OFNumber]

Now if each items has produced at once I have one row in my PS_DailyProduction table ,there is no problem, But whene any item has produced in two or more times (for example half quantity on one day and the rest onther day)her the problem show, in my PS_DailyProduction table i have more than one row and I get wrong result on

--Get sum of item quantity from the same category (all quantity before production) PS_ProjectInfo table
  SUM(Case WHEN info.[FK_PSCategory]=2 then ((info.Length * info.[Quantity] )/1000)Else 0 end ) as 'm'

The quantity is multiplied by the number of rows from PS_DailyProduction
I try this but did not work (dividing on number of rows)

--Count(PS_DailyProduction.FK_OF)
SUM(Case WHEN info.[FK_PSCategory]=2 then ((info.Length * info.[Quantity] )/1000)Else 0 end ) /Count(PS_DailyProduction.FK_OF)as 'm'

I use LEFT JOIN to get all rows from PS_ProjectInfo table even when they not produced yet

0
0
SELECT
info.[OFNumber] 
--Get sum of item quantity from the same category (all quantity before production) PS_ProjectInfo table
,SUM(Case WHEN info.[FK_PSCategory]=2 then ((info.Length * info.[Quantity] )/1000)Else 0 end ) as 'm'
  
-- Get sum of item quantity from the same category (Only quantity produced) PS_DailyProduction table
,SUM(Case WHEN info.[FK_PSCategory]=2 then p.Quantity ELSE 0 END) as 'qty'

FROM 
(
   SELECT [FK_OF], Sum(Quantity)
   FROM [dbo].[PS_DailyProduction]
   GROUP BY [FK_OF] 
) p
Left JOIN PS_ProjectInfo info on info.id=p.[FK_OF]

GROUP BY info.[OFNumber]

BTW, not quite understand why you use left join, since PS_ProjectInfo seems a must

0
0

This is how I did it

   SELECT 
   info.[OFNumber] 
   --Get sum of item quantity from the same category (all quantity before production) PS_ProjectInfo table
   ,(SELECT SUM(CASE WHEN (PS_ProjectInfo.[FK_PSCategory]=2 )THEN CAST(((CAST(PS_ProjectInfo.Length AS float) * PS_ProjectInfo.[Quantity])/1000)AS decimal(18,2)) ELSE 0 END) 
  FROM PS_ProjectInfo WHERE PS_ProjectInfo.OFNumber=info.[OFNumber] GROUP BY PS_ProjectInfo.OFNumber) as 'm'

   -- Get sum of item quantity from the same category (Only quantity produced) 
   ,SUM(Case WHEN info.[FK_PSCategory]=2 then PS_DailyProduction.Quantity ELSE 0 END) as 'qty'

   FROM [dbo].[PS_DailyProduction] 
   Left JOIN PS_ProjectInfo info on info.id=PS_DailyProduction.[FK_OF]

   GROUP BY info.[OFNumber]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.