Hi I need to write code to add another column to the right subtracting 1 from the column 'policy suffix'. I'm stumped on this one. Any ideas?
CodePudding user response:
You can do that using a select statement.
SELECT policyID,
policyNum,
PolicyPrefix,
PolicySuffix,
PolicySuffix - 1 AS PolicySuffixLess1
FROM yourtable;
To get your concatenated field, you can use common table expression for the initial query then select your desired fields from the cte.
WITH #CTEPolicies AS (
SELECT *, ap.PolicySuffix - 1 AS priorpolicysuffix
FROM #ActivePolicies
)
SELECT ap.policyID, ap.policyNum, ap.PolicyPrefix,
ap.PolicySuffix, ap.priorpolicysuffix,
CONCAT(ap.PolicyPrefix, '-', ap.priorpolicysuffix) AS priorpolicynumber
FROM #CTEPolicies ap