question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked EchoLiu-msft answered

How to count Leadfinish where have values and not null ?

I work on SQL server 2012 I Face issue I can't count values on Lead finish in case of no null
so How to do that Please ?
so suppose I have values on LeadFinishPlatingID as :

 companyID    LeadFinishPlatingID 
 345               12333
 345                45678
 345               23323
 345                   NULL

Then count for leadfinishPlatingID will be 3 for company id 345

so i need to modify sql script below to count only not null on LeadFinishPlatingId based on company id


  SELECT 
     Companies.DisplayName CompanyName,
     ISNULL(Data.CNt, 0) AS TotalPartsCount,
     ISNULL(Data.CNt - ISNULL(df.ManCount, 0), 0) AS [Parts Difference],
     ISNULL(df.ManCount, 0) ManufacturingPartsCount,
     ISNULL(df.[LeadFinishPlating], 0) [LeadFinishPlating]
        
        
     FROM
     (
     SELECT CompanyID, COUNT(PartID) CNt 
     FROM Parts.Nop_Part
     GROUP  BY CompanyID
     )AS Data
     LEFT JOIN 
     (
     SELECT 
     p.CompanyID,
     COUNT(m.PartID) ManCount,
     COUNT(m.LeadFinishPlatingID) as [LeadFinishPlating]
        
     from Parts.ManufacturingData m
     LEFT JOIN Parts.Nop_Part p
     ON p.PartID = m.PartID
     GROUP  BY p.CompanyID
     )AS DF ON Df.CompanyID = Data.CompanyID
     LEFT JOIN 
     (
     SELECT c.CompanyComID, C.DisplayName 
     FROM dbo.core_company c 
     )AS Companies ON Companies.CompanyComID = Data.CompanyID 





sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

Hi @ahmedsalah-1628

Please refer to:

 ;with cte
 as(select *,row_number() over(partition by LeadFinishPlatingID order by LeadFinishPlatingID) rn
 from Parts.ManufacturingData)
    
    
 SELECT 
      Companies.DisplayName CompanyName,
      ISNULL(Data.CNt, 0) AS TotalPartsCount,
      ISNULL(Data.CNt - ISNULL(df.ManCount, 0), 0) AS [Parts Difference],
      ISNULL(df.ManCount, 0) ManufacturingPartsCount,
      ISNULL(df.[LeadFinishPlating], 0) [LeadFinishPlating]
            
            
      FROM
      (
      SELECT CompanyID, COUNT(PartID) CNt 
      FROM Parts.Nop_Part
      GROUP  BY CompanyID
      )AS Data
      LEFT JOIN 
      (
      SELECT 
      p.CompanyID,
      COUNT(m.PartID) ManCount,
      COUNT(m.rn) as [LeadFinishPlating]
            
      from cte m
      LEFT JOIN Parts.Nop_Part p
      ON p.PartID = m.PartID
      GROUP  BY p.CompanyID
      )AS DF ON Df.CompanyID = Data.CompanyID
      LEFT JOIN 
      (
      SELECT c.CompanyComID, C.DisplayName 
      FROM dbo.core_company c 
      )AS Companies ON Companies.CompanyComID = Data.CompanyID 

Best Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ShamvilKazmi-2178 avatar image
0 Votes"
ShamvilKazmi-2178 answered ShamvilKazmi-2178 edited

have you tried adding where LeadFinishPlatingID is not null?

 SELECT 
  Companies.DisplayName CompanyName,
  ISNULL(Data.CNt, 0) AS TotalPartsCount,
  ISNULL(Data.CNt - ISNULL(df.ManCount, 0), 0) AS [Parts Difference],
  ISNULL(df.ManCount, 0) ManufacturingPartsCount,
  ISNULL(df.[LeadFinishPlating], 0) [LeadFinishPlating]
        
        
  FROM
  (
  SELECT CompanyID, COUNT(PartID) CNt 
  FROM Parts.Nop_Part
  GROUP  BY CompanyID
  )AS Data
  LEFT JOIN 
  (
  SELECT 
  p.CompanyID,
  COUNT(m.PartID) ManCount,
  COUNT(m.LeadFinishPlatingID) as [LeadFinishPlating]
        
  from Parts.ManufacturingData m
  LEFT JOIN Parts.Nop_Part p
  ON p.PartID = m.PartID and m.LeadFinishPlatingID is not null
  GROUP  BY p.CompanyID
  )AS DF ON Df.CompanyID = Data.CompanyID
  LEFT JOIN 
  (
  SELECT c.CompanyComID, C.DisplayName 
  FROM dbo.core_company c 
  )AS Companies ON Companies.CompanyComID = Data.CompanyID 


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 answered ahmedsalah-1628 published

thanks for reply
but i have 5 column need to apply not null
as query below

 SELECT 
 Companies.DisplayName CompanyName,
 ISNULL(Data.CNt, 0) AS TotalPartsCount,
 ISNULL(Data.CNt - ISNULL(df.ManCount, 0), 0) AS [Parts Difference],
 ISNULL(df.ManCount, 0) ManufacturingPartsCount,
 ISNULL(df.[LeadFinishPlating], 0) [LeadFinishPlating],
 ISNULL(df.[MSL], 0) [MSL],
 ISNULL(df.[MaximumReflowTemperature], 0) [MaximumReflowTemperature],
 ISNULL(df.LeadFreeProcessCapability,0) LeadFreeProcessCapability,
 isnull(df.BaseMaterial,0) BaseMaterial,
 isnull(df.ReflowSolderTime,0) ReflowSolderTime, 
 isnull(df.NumberOfReflowCycle,0) NumberOfReflowCycle,
 isnull(df.MaximumWaveTemperature,0) MaximumWaveTemperature,
 isnull(df.WaveSolderTime,0) WaveSolderTime,
 isnull(df.ShelfLifeMonths,0) ShelfLifeMonths,
 isnull(df.ShelfLifeStart,0) ShelfLifeStart
    
 FROM
 (
 SELECT CompanyID, COUNT(PartID) CNt 
 FROM Parts.Nop_Part
 GROUP  BY CompanyID
 )AS Data
 LEFT JOIN 
 (
 SELECT 
 p.CompanyID,
 COUNT(m.PartID) ManCount,
 COUNT(m.LeadFinishPlatingID) as [LeadFinishPlating],
 COUNT(m.MSLID) as [MSL],
 COUNT(m.MaximumReflowTemperatureID) as [MaximumReflowTemperature],
 COUNT(m.LeadFreeProcessCapabilityID) as LeadFreeProcessCapability,
 COUNT(m.BaseMaterialID) as BaseMaterial,
 COUNT(m.ReflowSolderTimeID) as ReflowSolderTime,
 COUNT(m.NumberOfReflowCycleID) as NumberOfReflowCycle,
 COUNT(m.MaximumWaveTemperatureID) as MaximumWaveTemperature,
 COUNT(m.WaveSolderTimeID) as WaveSolderTime,
 COUNT(m.ShelfLifeMonths) as ShelfLifeMonths,
 COUNT(m.ShelfLifeStart) as ShelfLifeStart
    
 from Parts.ManufacturingData m
 LEFT JOIN Parts.Nop_Part p
 ON p.PartID = m.PartID
 GROUP  BY p.CompanyID
 )AS DF ON Df.CompanyID = Data.CompanyID
 LEFT JOIN 
 (
 SELECT c.CompanyComID, C.DisplayName 
 FROM dbo.core_company c 
 )AS Companies ON Companies.CompanyComID = Data.CompanyID 

MSLID,LeadFreeProcessCapabilityID,ShelfLifeMonths,ShelfLifeStart,ReflowSolderTimeID,BaseMaterialID
all id above must be not null so How i do that

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

What number did you get? Maybe you should use ‘COUNT(DISTINCT m.LeadFinishPlatingID)’.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 answered

ok and if i use distinct will remove null
also I need to do it use case when
so How to do with case when
can you apply to one column from query above and i will do another

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.