February 13, 2017 at 7:54 am
I have tried the code below to flag certain customers upon the 'case whens' being validated.
I am having trouble with the 'datepart' case whens I keep getting 'incorrect syntax near THEN
please help
thanks in advance
select
a.account_number,
CurrentBalance,
DateIdentified,
lastpaymentdate,
BlockCode01,
BlockCode02,
BlockCode1Date,
usercode01,
usercode02,
CASE WHEN (DATEDIFF(DD,MAX(b.EffectiveDate), getdate()) > 35 OR MAX(b.EffectiveDate) IS NULL) THEN 1 ---payments
WHEN (DATEDIFF(DD,MAX(c.CaActionDate), getdate())) > 35 THEN 1 ---- promises
WHEN (datepart(MM,d.authmessagetimestamp) = datepart(MM,(cast(getdate() as date)) THEN 1 ---approved payments not applied
WHEN (DATEPART(MM,e.CareviewDate) = datepart(MM,(cast (getdate() as date)) THEN 1 ---pends applied
WHEN (DATEPART(MM,f.loadeddate) = datepart(MM,(cast(getdate()as date)) THEN 1
ELSE 0
END AS NORPC
from
Refinedlist a
left join WorkLSC.[dbo].[vw_FreshStart_Core_Payments] b
ON a.account_number = b.accountnumber
Left join WorkLSC.dbo.t_FreshStart_Core_Promises c
ON a.account_number = c.accountnumber AND caactiondate > GETDATE() AND [current] = 1
left join Paymentservice.dbo.vw_paymentrequest d ON
a.account_number = d.vanquisaccountnumber AND authmessagetimestamp > GETDATE() - 35 AND authoutcome = 'Approved'
left join worklsc.dbo.t_currentpends e
ON a.account_number = e.accountnumber
LEFT JOIN crm2.[dbo].[vw_CallOutcome] f
ON a.account_number = f.accountnumber AND f.loadeddate > CAST(GETDATE() AS DATE) AND outcome IN ('DCP','IPAV','PDP','Pend','PTP','PTPE','RPCB','SET','SETS') -- outcome agreed today
WHERE BlockCode01 = 'p'
AND BlockCode1Date < GETDATE() - 35
AND c.AccountNumber IS NULL
AND authmessagetimestamp IS NULL
AND e.accountnumber IS NULL
AND f.accountnumber IS NULL
GROUP BY a.Account_Number, BlockCode01, UserCode01, LastPaymentDate, BlockCode02,UserCode02, CurrentBalance
HAVING (DATEDIFF(DD,MAX(EffectiveDate), GETDATE()) > 35 OR MAX(EffectiveDate) IS NULL)
February 13, 2017 at 8:03 am
Your parentheses did not match up.
SELECT
a.account_number
, CurrentBalance
, DateIdentified
, lastpaymentdate
, BlockCode01
, BlockCode02
, BlockCode1Date
, usercode01
, usercode02
, NORPC = CASE
WHEN
(
DATEDIFF(DD, MAX(b.EffectiveDate), GETDATE()) > 35 OR
MAX(b.EffectiveDate) IS NULL
) THEN
1 ---payments
WHEN (DATEDIFF(DD, MAX(c.CaActionDate), GETDATE())) > 35 THEN
1 ---- promises
WHEN (DATEPART(MM, d.authmessagetimestamp) = DATEPART(MM, (CAST(GETDATE() AS DATE)))) THEN
1 ---approved payments not applied
WHEN (DATEPART(MM, e.CareviewDate) = DATEPART(MM, (CAST(GETDATE() AS DATE)))) THEN
1 ---pends applied
WHEN (DATEPART(MM, f.loadeddate) = DATEPART(MM, (CAST(GETDATE() AS DATE)))) THEN
1
ELSE
0
END
FROM
Refinedlist a
LEFT JOIN WorkLSC.dbo.vw_FreshStart_Core_Payments b ON a.account_number = b.accountnumber
LEFT JOIN WorkLSC.dbo.t_FreshStart_Core_Promises c ON a.account_number = c.accountnumber AND
caactiondate > GETDATE() AND
[current] = 1
LEFT JOIN Paymentservice.dbo.vw_paymentrequest d ON a.account_number = d.vanquisaccountnumber AND
authmessagetimestamp > GETDATE() - 35 AND
authoutcome = 'Approved'
LEFT JOIN worklsc.dbo.t_currentpends e ON a.account_number = e.accountnumber
LEFT JOIN crm2.dbo.vw_CallOutcome f ON a.account_number = f.accountnumber AND
f.loadeddate > CAST(GETDATE() AS DATE) AND
outcome IN ('DCP', 'IPAV', 'PDP', 'Pend', 'PTP', 'PTPE', 'RPCB', 'SET', 'SETS') -- outcome agreed today
WHERE
BlockCode01 = 'p' AND
BlockCode1Date < GETDATE() - 35 AND
c.AccountNumber IS NULL AND
authmessagetimestamp IS NULL AND
e.accountnumber IS NULL AND
f.accountnumber IS NULL
GROUP BY
a.Account_Number
, BlockCode01
, UserCode01
, LastPaymentDate
, BlockCode02
, UserCode02
, CurrentBalance
HAVING
(
DATEDIFF(DD, MAX(EffectiveDate), GETDATE()) > 35 OR
MAX(EffectiveDate) IS NULL
);
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 13, 2017 at 2:22 pm
ExhibitA - Monday, February 13, 2017 7:54 AMI have tried the code below to flag certain customers upon the 'case whens' being validated.
I am having trouble with the 'datepart' case whens I keep getting 'incorrect syntax near THEN
please help
thanks in advance
select
a.account_number,
CurrentBalance,
DateIdentified,
lastpaymentdate,
BlockCode01,
BlockCode02,
BlockCode1Date,
usercode01,
usercode02,
CASE WHEN (DATEDIFF(DD,MAX(b.EffectiveDate), getdate()) > 35 OR MAX(b.EffectiveDate) IS NULL) THEN 1 ---payments
WHEN (DATEDIFF(DD,MAX(c.CaActionDate), getdate())) > 35 THEN 1 ---- promises
WHEN (datepart(MM,d.authmessagetimestamp) = datepart(MM,(cast(getdate() as date)) THEN 1 ---approved payments not applied
WHEN (DATEPART(MM,e.CareviewDate) = datepart(MM,(cast (getdate() as date)) THEN 1 ---pends applied
WHEN (DATEPART(MM,f.loadeddate) = datepart(MM,(cast(getdate()as date)) THEN 1
ELSE 0
END AS NORPC
from
Refinedlist a
left join WorkLSC.[dbo].[vw_FreshStart_Core_Payments] b
ON a.account_number = b.accountnumber
Left join WorkLSC.dbo.t_FreshStart_Core_Promises c
ON a.account_number = c.accountnumber AND caactiondate > GETDATE() AND [current] = 1
left join Paymentservice.dbo.vw_paymentrequest d ON
a.account_number = d.vanquisaccountnumber AND authmessagetimestamp > GETDATE() - 35 AND authoutcome = 'Approved'
left join worklsc.dbo.t_currentpends e
ON a.account_number = e.accountnumber
LEFT JOIN crm2.[dbo].[vw_CallOutcome] f
ON a.account_number = f.accountnumber AND f.loadeddate > CAST(GETDATE() AS DATE) AND outcome IN ('DCP','IPAV','PDP','Pend','PTP','PTPE','RPCB','SET','SETS') -- outcome agreed today
WHERE BlockCode01 = 'p'
AND BlockCode1Date < GETDATE() - 35
AND c.AccountNumber IS NULL
AND authmessagetimestamp IS NULL
AND e.accountnumber IS NULL
AND f.accountnumber IS NULL
GROUP BY a.Account_Number, BlockCode01, UserCode01, LastPaymentDate, BlockCode02,UserCode02, CurrentBalance
HAVING (DATEDIFF(DD,MAX(EffectiveDate), GETDATE()) > 35 OR MAX(EffectiveDate) IS NULL)
Kinda messy coding, which is what tends to lead to mismatched parentheses. Here's a much "prettier" version:
SELECT
a.account_number,
CurrentBalance,
DateIdentified,
lastpaymentdate,
BlockCode01,
BlockCode02,
BlockCode1Date,
usercode01,
usercode02,
CASE
WHEN (DATEDIFF(DD, MAX(b.EffectiveDate), getdate()) > 35 OR MAX(b.EffectiveDate) IS NULL) THEN 1 ---payments
WHEN DATEDIFF(DD, MAX(c.CaActionDate), getdate())) > 35 THEN 1 ---- promises
WHEN DATEPART(MM, d.authmessagetimestamp) = DATEPART(MM, getdate()) THEN 1 ---approved payments not applied
WHEN DATEPART(MM, e.CareviewDate) = DATEPART(MM, getdate()) THEN 1 ---pends applied
WHEN DATEPART(MM, f.loadeddate) = DATEPART(MM, getdate()) THEN 1
ELSE 0
END AS NORPC
FROM Refinedlist AS a
LEFT JOIN WorkLSC.[dbo].[vw_FreshStart_Core_Payments] AS b
ON a.account_number = b.accountnumber
LEFT JOIN WorkLSC.dbo.t_FreshStart_Core_Promises AS c
ON a.account_number = c.accountnumber
AND caactiondate > GETDATE()
AND [current] = 1
LEFT JOIN Paymentservice.dbo.vw_paymentrequest AS d
ON a.account_number = d.vanquisaccountnumber
AND authmessagetimestamp > GETDATE() - 35
AND authoutcome = 'Approved'
LEFT JOIN worklsc.dbo.t_currentpends AS e
ON a.account_number = e.accountnumber
LEFT JOIN crm2.[dbo].[vw_CallOutcome] AS f
ON a.account_number = f.accountnumber
AND f.loadeddate > CAST(GETDATE() AS DATE)
AND outcome IN ('DCP','IPAV','PDP','Pend','PTP','PTPE','RPCB','SET','SETS') -- outcome agreed today
WHERE BlockCode01 = 'p'
AND BlockCode1Date < GETDATE() - 35
AND c.AccountNumber IS NULL
AND authmessagetimestamp IS NULL
AND e.accountnumber IS NULL
AND f.accountnumber IS NULL
GROUP BY a.Account_Number, BlockCode01, UserCode01, LastPaymentDate, BlockCode02, UserCode02, CurrentBalance
HAVING (DATEDIFF(DD,MAX(EffectiveDate), GETDATE()) > 35
OR MAX(EffectiveDate) IS NULL)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply