February 1, 2016 at 12:16 pm
Right now I have this and it is not working.
CASE CAS.TERM_YYYYMMDD
WHEN '' Then convert(varchar,CONVERT(DATETIME,IND.TERM_YYYYMMDD),101)
WHEN '' Then convert(varchar,CONVERT(DATETIME,CLM.TERM_YYYYMMDD_02),101)
WHEN '' Then convert(varchar,CONVERT(DATETIME,IBE.TERM_YYYYMMDD),101)
ELSE convert(varchar,CONVERT(DATETIME,CAS.TERM_YYYYMMDD),101)
end as TERM_YYYYMMDD
What I intend to do is to check the different term dates to see if there are any term date in either field. I have tried different nested case statements but nothing I have tried is working 🙁
February 1, 2016 at 12:25 pm
Hello and welcome to SSC.
If your intent is to query the first populated column, then you could just use a COALESCE function and skip the CASE. Example:
SELECT COALESCE(IND.TERM_YYYYMMDD, CLM.TERM_YYYYMMDD_02, IBE.TERM_YYYYMMDD, TERM_YYYYMMDD)
FROM ...
February 1, 2016 at 12:35 pm
Thanks!
It's part of a stored proc so I need to check each of the term dates to see if it is a date and if not I need to use the CAS.TERM_YYYYMMDD value as TERM_YYYYMMDD
I have also tried this but the only value it seems to use is the first value if there is one in the IND.term_yyyymmdd.
CASE
WHEN CAS.TERM_YYYYMMDD = ''
THEN CASE [IND].TERM_YYYYMMDD
WHEN '' THEN ''
ELSE convert(varchar,CONVERT(DATETIME,[ind].TERM_YYYYMMDD),101)
END
WHEN CAS.TERM_YYYYMMDD = ''
THEN CASE [CLM].TERM_YYYYMMDD_02
WHEN '' THEN ''
ELSE convert(varchar,CONVERT(DATETIME,[clm].TERM_YYYYMMDD_02),101)
END
WHEN CAS.TERM_YYYYMMDD = ''
THEN CASE [1_tbl_PEOPLE W/ DENTAL].TERM_YYYYMMDD
WHEN 'Jan 1 1900 12:00AM' THEN ''
ELSE convert(varchar,CONVERT(DATETIME,[1_tbl_PEOPLE W/ DENTAL].TERM_YYYYMMDD),101)
END
ELSE convert(varchar,CONVERT(DATETIME,CAS.TERM_YYYYMMDD),101)
END AS TERM_YYYYMMDD
February 1, 2016 at 12:41 pm
suggest you give us sample data and expected results.....we will all be on the same page then 🙂
http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 1, 2016 at 12:54 pm
I didn't mean to be rude, I just have a hard time explaining!
IND.TERM_YYYYMMDD = 5/1/2015 WORKS
CLM.TERM_YYYYMMDD_02 = 9/1/2015 DOES NOT WORK
IBE.TERM_YYYYMMDD = 3/1/2016 DOES NOT WORK
With does not work I mean that the case is not picking it up. The only one it picks up is the first option, IND.TERM_YYYYMMDD.
Whet I would like it to happen is if the first one is empty, then check the next one and if that one is empty too then check the third term date. I know how to do it in VB and access but this one has me stumped 🙁
DECLARE @TARGETDATE AS DATETIME
SET @TARGETDATE=DATEADD(DAY,-30,GETDATE())
TRUNCATE TABLE [2_tbl_Base]
SELECT DISTINCT ind.CASE_NUM
, ind.CERT_NUM
, ind.DIV_NUM
, 0 AS
, '' AS [VENDOR GROUP NUM]
, RTrim(IND.MESSAGE) AS [COBRA CODE]
, ind.LAST_NAME
, ind.FIRST_NAME
, ind.TIN_NUM
, ind.STATUS
, ind.MESSAGE
, ind.HOME_PHONE
, ind.ADDR_LINE_1
, ind.ADDR_LINE_2
, ind.CITY
, ind.STATE
, ind.ZIP_CODE
, CASE IND.APP_SIGNED_YYYYMMDD
WHEN '1/1/1900 12:00:00 AM' THEN convert(varchar,CONVERT(DATETIME,[1_tbl_PEOPLE W/ DENTAL].EFF_YYYYMMDD),101)
ELSE convert(varchar,CONVERT(DATETIME,IND.APP_SIGNED_YYYYMMDD),101)
END AS EFF_YYYYMMDD
,
CASE
WHEN CAS.TERM_YYYYMMDD = ''
THEN CASE [IND].TERM_YYYYMMDD
WHEN '' THEN ''
ELSE convert(varchar,CONVERT(DATETIME,[ind].TERM_YYYYMMDD),101)
END
WHEN CAS.TERM_YYYYMMDD = ''
THEN CASE [CLM].TERM_YYYYMMDD_02
WHEN '' THEN ''
ELSE convert(varchar,CONVERT(DATETIME,[clm].TERM_YYYYMMDD_02),101)
END
WHEN CAS.TERM_YYYYMMDD = ''
THEN CASE [1_tbl_PEOPLE W/ DENTAL].TERM_YYYYMMDD
WHEN 'Jan 1 1900 12:00AM' THEN ''
ELSE convert(varchar,CONVERT(DATETIME,[1_tbl_PEOPLE W/ DENTAL].TERM_YYYYMMDD),101)
END
ELSE convert(varchar,CONVERT(DATETIME,CAS.TERM_YYYYMMDD),101)
END AS TERM_YYYYMMDD
, CAS.TERM_YYYYMMDD
, [IND].TERM_YYYYMMDD
, [CLM].TERM_YYYYMMDD_02
,[1_tbl_PEOPLE W/ DENTAL].TERM_YYYYMMDD
, convert(varchar,CONVERT(DATETIME,ind.BIRTH_YYYYMMDD),101) as BIRTH_YYYYMMDD
, ind.SEX
, [1_tbl_PEOPLE W/ DENTAL].BENEFIT_CODE
, Right([1_tbl_PEOPLE W/ DENTAL].BENEFIT_CODE,1) AS [ECODE COVERAGE]
, [1_tbl_PEOPLE W/ DENTAL].COVERED_CLAIMANTS
, ind.ALTERNATE_KEY
, CASE IND.APP_SIGNED_YYYYMMDD
WHEN '1/1/1900 12:00:00 AM' THEN convert(varchar,CONVERT(DATETIME,ind.EMPLOYMENT_YYYYMMDD),101)
ELSE convert(varchar,CONVERT(DATETIME,IND.APP_SIGNED_YYYYMMDD),101)
END AS EMPLOYMENT_YYYYMMDD
, ind.MARITAL_STATUS
, convert(varchar,CONVERT(DATETIME,ind.EFF_YYYYMMDD),101) AS X
FROM [1_tbl_PEOPLE W/ DENTAL]
INNER JOIN
ind ON ([1_tbl_PEOPLE W/ DENTAL].CERT_NUM = ind.CERT_NUM)
AND
([1_tbl_PEOPLE W/ DENTAL].CASE_NUM = ind.CASE_NUM)
INNER JOIN
cas ON ind.DIV_NUM = cas.DIV_NUM AND ind.CASE_NUM = cas.CASE_NUM
INNER JOIN
clm ON clm.CASE_NUM = IND.CASE_NUM AND CLM.CERT_NUM = IND.CERT_NUM
INNER JOIN
IBE ON IBE.CASE_NUM = IND.CASE_NUM AND IBE.CERT_NUM = IND.CERT_NUM
INNER JOIN
Div_Conversion ON (Div_Conversion.GroupNumber = ind.CASE_NUM )
AND
(cas.DIV_NUM = Div_Conversion.DIV_NUM or cas.claim_policy_num = div_conversion.gbas_div)
WHERE
(IND.TERM_YYYYMMDD='' OR IND.TERM_YYYYMMDD<GETDATE() or IND.TERM_YYYYMMDD is null
or
CONVERT(varchar,IND.TERM_YYYYMMDD,101) >convert(varchar,@TargetDate,101))
ORDER BY ind.CASE_NUM, ind.CERT_NUM
February 1, 2016 at 1:31 pm
You were not rude, just incomplete.
And you still are. Please check out the link J Livingstone posted. Then post sample data (CREATE TABLE and INSERT statements) and expected results.
Make sure your test data covers all possible cases. Do not include more test data than that.
February 1, 2016 at 1:50 pm
I finally got it. This works the way I need it to:
CASE
WHEN CAS.TERM_YYYYMMDD != '' Then convert(varchar,CONVERT(DATETIME,CAS.TERM_YYYYMMDD),101)
WHEN IND.TERM_YYYYMMDD != '' Then convert(varchar,CONVERT(DATETIME,IND.TERM_YYYYMMDD),101)
WHEN CLM.TERM_YYYYMMDD_02 != '' Then convert(varchar,CONVERT(DATETIME,CLM.TERM_YYYYMMDD_02),101)
WHEN convert(varchar,CONVERT(DATETIME,[1_tbl_PEOPLE W/ DENTAL].TERM_YYYYMMDD),101) != '1/1/1900' Then convert(varchar,CONVERT(DATETIME,[1_tbl_PEOPLE W/ DENTAL].TERM_YYYYMMDD),101)
ELSE convert(varchar,CONVERT(DATETIME,CAS.TERM_YYYYMMDD),101)
end as TERM_YYYYMMDD
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply