Click here to Skip to main content
15,898,984 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SQL
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.
Posted
Updated 7-Jun-18 0:54am
v2

1 solution

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
 
Share this answer
 
Comments
Maciej Los 7-Jun-18 6:53am    
5ed!
an0ther1 7-Jun-18 17:08pm    
Thanks Maciej!!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900