我在SSNID列中得到重复项。需要帮助。 [英] I'm getting duplicates in the SSNID column. Need assistance.

问题描述

  SELECT   distinct  
 
 dsp .SSNID，
 案例 当 fsa.Assigned_CNT> 0 和 
 dsp.rcms_Grade_CD  in （'  E4'，'  E5'​​，'  E6'，'  E7'，'  E8'）和 
 fsp.Promotable_CNT =  1  和 dpc.InPositionOfHigherGrade_YN =  1  
 和 dsp.rcms_ComponentCategory_CD<> '  AGR' 
 和 dsp.APFT_Result_CD<> '  F' 
 和 qcsp.SFPA_CNT =  0  
 和 qcsp.SFPA_Adverse_CNT = 0 
 然后 fsa.Assigned_CNT 其他  0  结束  as  '  EnlistedPromotionEligible' 
，案例 何时 fsa.Assigned_CNT> 0 和 
 dsp.rcms_Grade_CD  in （' < span class =code-string> O1'，'  O2'，'  O3'，'  O4'）和 
 fsp.Promotable_CNT =  1  和 dpc.InPositionOfHigherGrade_YN =  1  和 
（fps.fr_Packet_CNT<  1  或 fps.fr_Packet_CNT    null ）
 和 dsp.rcms_ComponentCategory_CD<> '  AGR' 
 和 dsp.APFT_Result_CD<> '  F' 
 和 qcsp.SFPA_CNT =  0  
 和 qcsp.SFPA_Adverse_CNT = 0 
 然后 fsa.Assigned_CNT 其他  0  结束  as  '  OfficerPromotionEligible' 
，dsp.rcms_Grade_CD 
，dp.rcms_PositionGrade_CD 
，'  InHigherPos' =案例当 dp.rcms_PositionGrade_CD    null  然后  0  何时 dp.rcms_PositionGrade_CD> fsa.rcms_Grade_CD 然后  1  其他  0  结束 
，fps.fr_Packet_CNT 
，dsp.APFT_Result_CD 
，dsp .rcms_ComponentCategory_CD 
，dpc.InPositionOfHigherGrade_YN  as  '  PromoReqInPositionOfHigherGrade_YN' 
，dpc.ReqCollegeDegree_YN 
，fsp.MeetsCollegeReq_CNT 
，dpc.ReqMilitaryEducation 
，mil.Code  as  '  MilitaryEducation_CD' 
，rcms_MilitaryEducation_Desc 
，fsp.MeetsMILEDReq_CNT 
，dpc.ReqMonthsInGrade 
，' 成绩月份 = floor（（datediff（ dd，dsp.rcms_Rank_DT，fsp.Run_DT）+1）/ 30.  42 ）
，fsp.MeetsMoInGradeReq_CNT 
，dpc.ReqMonthsInService 
，' 服务月数 = floor（（ datediff（dd，dsp.rcms_PEBD_DT，fsp.Run_DT）+1）/ 30.  42 ）
，fsp.MeetsMoInServiceReq_CNT 
 
  FROM  [G1Lifecycle_DW]。[dbo] .FactStrengthAssessment fsa 
  JOIN  G1LifeCycle_DW..DimSoldierPersonnel DSP < span class =code-keyword> on  DSP.ID = FSA.SoldierID 和 FSA.Run_DT> = dsp.Start_DT 和（fsa.Run_DT< dsp.End_DT 或 dsp.End_DT    null ）
  JOIN  [G1Lifecycle_DW]。[dbo]。[FactSoldierPromotability] fsp  on  fsp。 SoldierID = fsa.SoldierID 
  JOIN  [G1Lifecycle_DW]。[dbo]。[DimPromotionCriteria] dpc  on  dpc.ID = fsp.PromotionCriteriaID 
  JOIN  [G1Lifecycle_DW]。[dbo]。[DimPosition] DP  on  DP.ID = fsa.PositionID 
  LEFT   JOIN  [G1Lifecycle_DW]。[ dbo]。[FactFedRecPacketStatus] fps  on  fps.ssnid = DSP.ssnid 
  left   join  G1Lifecycle_LOOKUPS.dbo.Lkp_MilitaryEducation_CD mil  on  DSP.MilitaryEducation_CD = mil.Code 和 mil.End_DT    null  
  JOIN  [G1Lifecycle_DW]。[dbo]。[QQRCurrentSoldierPASS] QCSP   qcsp.SSNID = dsp.SSNID 
  WHERE  
 
 dsp.rcms_Grade_CD  in （ '  E4'，'  E5'​​，'  E6'，'  E7'，'  E8'，'  O1'，'  O2'，'  O3 '，'  O4'）和 
 
 dsp.End_DT    null  

我尝试过的事情：



我试过看案例。

解决方案

< blockquote>你得到了重复项，因为你的联接正在识别符合条件的多个记录。

除了你自己，没有人能解决它 - 你需要检查你的数据。



这样做最简单的方法就是注释掉联接&他们的相关列，直到重复消失&然后确定为什么你的一个表有多个记录而你没有预期呢



亲切的问候

SELECT distinct 

dsp.SSNID,
Case When fsa.Assigned_CNT>0 and 
    dsp.rcms_Grade_CD in('E4','E5','E6','E7','E8') and
    fsp.Promotable_CNT = 1 and dpc.InPositionOfHigherGrade_YN = 1 
    and dsp.rcms_ComponentCategory_CD <> 'AGR'
    and dsp.APFT_Result_CD <> 'F'
    and qcsp.SFPA_CNT = 0 
    and qcsp.SFPA_Adverse_CNT =0
    Then fsa.Assigned_CNT Else 0 End as 'EnlistedPromotionEligible'
, Case When fsa.Assigned_CNT>0 and 
    dsp.rcms_Grade_CD in ('O1','O2','O3','O4') and
    fsp.Promotable_CNT = 1 and dpc.InPositionOfHigherGrade_YN = 1 and 
    (fps.fr_Packet_CNT < 1 or fps.fr_Packet_CNT is null)
    and dsp.rcms_ComponentCategory_CD <> 'AGR'
    and dsp.APFT_Result_CD <> 'F'
    and qcsp.SFPA_CNT = 0 
    and qcsp.SFPA_Adverse_CNT =0
    Then fsa.Assigned_CNT Else 0 End as 'OfficerPromotionEligible'
,dsp.rcms_Grade_CD
,dp.rcms_PositionGrade_CD
, 'InHigherPos' =Case When dp.rcms_PositionGrade_CD is null Then 0 When dp.rcms_PositionGrade_CD > fsa.rcms_Grade_CD Then 1 Else 0 End
,fps.fr_Packet_CNT
,dsp.APFT_Result_CD
,dsp.rcms_ComponentCategory_CD
,dpc.InPositionOfHigherGrade_YN as 'PromoReqInPositionOfHigherGrade_YN'
,dpc.ReqCollegeDegree_YN
,fsp.MeetsCollegeReq_CNT
,dpc.ReqMilitaryEducation
,mil.Code as 'MilitaryEducation_CD'
,rcms_MilitaryEducation_Desc
,fsp.MeetsMILEDReq_CNT
,dpc.ReqMonthsInGrade
,'Months in grade' = floor((datediff(dd,dsp.rcms_Rank_DT, fsp.Run_DT)+1)/30.42)
,fsp.MeetsMoInGradeReq_CNT
,dpc.ReqMonthsInService
,'Months in service' = floor((datediff(dd,dsp.rcms_PEBD_DT, fsp.Run_DT)+1)/30.42)
,fsp.MeetsMoInServiceReq_CNT

FROM [G1Lifecycle_DW].[dbo].FactStrengthAssessment fsa
JOIN G1LifeCycle_DW..DimSoldierPersonnel DSP on DSP.ID = FSA.SoldierID and FSA.Run_DT >= dsp.Start_DT and (fsa.Run_DT < dsp.End_DT or dsp.End_DT is null)
JOIN [G1Lifecycle_DW].[dbo].[FactSoldierPromotability] fsp on fsp.SoldierID = fsa.SoldierID
JOIN [G1Lifecycle_DW].[dbo].[DimPromotionCriteria] dpc on dpc.ID= fsp.PromotionCriteriaID
JOIN [G1Lifecycle_DW].[dbo].[DimPosition] DP on DP.ID = fsa.PositionID
LEFT JOIN [G1Lifecycle_DW].[dbo].[FactFedRecPacketStatus] fps on fps.ssnid = DSP.ssnid
left join G1Lifecycle_LOOKUPS.dbo.Lkp_MilitaryEducation_CD mil on DSP.MilitaryEducation_CD=mil.Code and mil.End_DT is null
JOIN [G1Lifecycle_DW].[dbo].[QQRCurrentSoldierPASS] QCSP on qcsp.SSNID=dsp.SSNID
WHERE 

dsp.rcms_Grade_CD in('E4','E5','E6','E7','E8', 'O1','O2','O3','O4') and

dsp.End_DT is null

What I have tried:

I've tried looking at the cases.

解决方案

You are getting duplicates because your joins are identifying multiple records that meet the criteria.
There is no way anyone can resolve it but yourself - you need to review your data.

The easiest way of doing this is to comment out joins & their associated columns until the duplicates disappear & then determine why one of your tables has multiple records where you are not expecting it to

Kind Regards

这篇关于我在SSNID列中得到重复项。需要帮助。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！