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?

enter image description here

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