Help with Case Statement

Since I'm fairly new to SQL - I need help making the following into a case
statement:
-- Set Reporting date if the policy is a changed policy
UPDATE STAGE_PHX_FACT_POLICY
SET REPORTING_DATE = CHG_EFF_DATE
FROM STAGE_PHX_FACT_POLICY
WHERE CHG_EFF_DATE IS NOT NULL
-- Set Reporting date if the policy is a termination
UPDATE STAGE_PHX_FACT_POLICY
SET REPORTING_DATE = POL_TERM_DATE
FROM STAGE_PHX_FACT_POLICY
WHERE POL_TERM_DATE IS NOT NULL
-- Set Reporting date if the policy is not changed and not terminated
UPDATE STAGE_PHX_FACT_POLICY
SET REPORTING_DATE = POL_EFF_DATE
FROM STAGE_PHX_FACT_POLICY
WHERE CHG_EFF_DATE IS NULL
AND POL_TERM_DATE IS NULL
-- Set Reporting date if Policy date and time is greater than all of the
above
UPDATE STAGE_PHX_FACT_POLICY
SET REPORTING_DATE = convert(varchar(50),policy_date_time,101
)
FROM STAGE_PHX_FACT_POLICY
WHERE
EXISTS (SELECT NULL FROM STAGE_PHX_FACT_POLICY b
WHERE b.POLICY_NUMBER = STAGE_PHX_FACT_POLICY.POLICY_NUMBER
GROUP BY b.POLICY_NUMBER
HAVING STAGE_PHX_FACT_POLICY.POLICY_DATE_TIME >
STAGE_PHX_FACT_POLICY.REPORTING_DATE)
Thanks in advance for any help!Without table structures & sample data, the following expression is
untested:
CASE WHEN chg_eff_date IS NOT NULL
THEN chg_eff_date
WHEN pol_term_date IS NOT NULL
THEN pol_term_date
WHEN chg_eff_date IS NULL
AND pol_term_date IS NULL
THEN pol_eff_date
WHEN policy_date_time > reporting_date
THEN CONVERT( VARCHAR(50), policy_date_time, 101 )
END
I would also suggest you spend some time on the topic CASE in SQL Server
Books Online and go through the examples.
Anith|||On Fri, 28 Oct 2005 08:35:03 -0700, Patrice wrote:

>Since I'm fairly new to SQL - I need help making the following into a case
>statement:
(snip)
Hi Patrice,
Most of it can be caught in a COALESCE. Only the last requirement calls
for a CASE.
UPDATE Stage_PHX_Fact_Policy
SET Reporting_Date =
CASE
WHEN COALESCE (Chg_Eff_Date, Pol_Term_Date, Pol_Eff_Date)
< Policy_Date_Time
THEN CONVERT(char(8), Policiy_Date_Time, 112)
ELSE COALESCE (Chg_Eff_Date, Pol_Term_Date, Pol_Eff_Date)
The above is untested, and assumes that Policy_Number is the primary key
of your table. If it isn't, or if the query doesn't do what you want,
then see www.aspfaq.com/5006 for the recommended way to explain your
problem here.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)