Choose between two date fileds

• patrick.palmer

  SSC Enthusiast

  Points: 108

  More actions

  March 4, 2013 at 12:39 pm

  #271747

  I am trying to finish a report in BIDS that will let the user choose between two fields of the choosing, not one I specified. I am having trouble with this. The field I am using is DD.DateDesc to pull the date. PLEASE HELP?

  select

  dimUser.EmpNo AS [User Number],

  dimUser.EmpFullName1 [User Name],

  activityname [Activity Name],

  CONVERT(VARCHAR(10), DD.DateDesc, 101) [Completion Date],

  MAX(AL.CECredits) [Contributing Credits],

  CER.CERegion_Name License,

  dimUser.OptEmp_Txt1 [License Type],

  dimUser.OptEmp_Txt2 [License Number],

  CASE RTRIM(LTRIM(V.VenName))

  WHEN 'BayCare Education Services' THEN '50-201'

  WHEN 'Morton Plant Mease Health Care' THEN '50-1378'

  WHEN 'St. Josephs Hospital' THEN '50-1867'

  END

  ProviderId,

  AL.ApprovalCode [Approval Code],

  AO.txt1 [Credit Category],

  dimUser.PrimaryJobName [Primary Job],

  dimUser.PrimaryOrgName

  From factAttempt fact

  INNER JOIN dimActivity A ON A.ID = fact.ActivityID

  INNER JOIN TBL_TMX_ActLinks AL ON AL.ActivityFK = A.ActivityFK AND AL.LinkType = 3

  INNER JOIN dimUser ON dimUser.ID = fact.UserID

  INNER JOIN TBL_TMX_ActOpt AO ON AO.ActivityFK = A.ActivityFK

  INNER JOIN dimDate DD ON DD.DateID = fact.EndDtID

  INNER JOIN tblEmpCERegion ER ON ER.EmpCERegion_EmpFK = dimUser.EmpFK

  INNER JOIN CERegion CER ON CER.CERegion_PK = ER.EmpCERegion_CERegionFK AND CER.CERegion_Name LIKE '%-%'

  INNER JOIN factResourceVen AV ON AV.ActivityID = A.ID

  INNER JOIN dimVen AS V ON V.ID = AV.VenID

  left outer join TBL_TMX_ActCECC CECC on CECC.ActivityFK = A.ID

  left outer join CreditCatType cct on cct.CreditCatType_PK = CECC.CreditCatTypeFK

  where CER.CERegion_PK = 5 and RegistrationStatusID = '4'and fact.EndDtID >= '-1'

  group by

  dimUser.EmpFullName1,

  activityname,

  CER.CERegion_Name,

  V.VenName,

  AL.ApprovalCode,

  cct.CreditCatType_Name,

  dimUser.EmpNo,

  AO.txt1,

  dimUser.PrimaryJobName,

  dimUser.PrimaryOrgName,

  dimUser.EmpStartDt,

  dimUser.OptEmp_Txt1,

  dimUser.OptEmp_Txt2,

  dd.datedesc,

  RegistrationStatusID