CODEWITHSUNDEEP
sqlsubtraction
user17620314
user17620314

Reputation: 1

Subtract 1 from all data in an column in sql

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

Upvotes: 0

Views: 1988

Answers (1)

Okechukwu Ossai
Okechukwu Ossai

Reputation: 604

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

Upvotes: 1

Related Questions