June 14, 2016 at 8:11 am
Good afternoon all,
Hoping someone might be able to help me out with this puzzle that I've been wrestling with today!
I have some code that selects customers for a promotional mailing.
The customers are grouped into segments depending on propensity and a code is assign to identify each segment.
Each segment code ends in a 2 character number (string) starting '01'
So the code is a varchar that looks something like abc01, abc02 etc
There are a total of about 50 segments in the mailing and if the segmentation changes (which it is likely to do) it's a case of updating the number suffix manually amnd mistakes get made.
So, Is it possible in TSQL to increase the segment numbers based on the previous segment?
Here's a very simple version of the code I have so far:
declare @mailing varchar(5) = 'ABC'
if object_id('tempdb..#mailing') is not null drop table #mailing
;
create table #mailing
(segment varchar(9)
, contact_number int)
;
insert into #mailing
(segment
,contact_number)
select distinct @mailing+'01'
,AccountNumber
from sales.Customer
where CustomerType = 'I'
;
insert into #mailing
(segment
,contact_number)
select distinct @mailing+'02'
,AccountNumber
from sales.Customer
where CustomerType = 'S'
In pseudo code what I'd like to be able to do with segment '02 above is to do something like:
insert into #mailing
(segment
,contact_number)
select distinct @mailing+(select Max(right(segment,2))+1
from #mailing)
,AccountNumber
from sales.Customer
where CustomerType = 'S'
My aim is that if segments are removed, or moved up or down the hierarchy the code can create the numbers automatically based on the previous segment code.
Any ideas or thoughts are greatly appreciated.
Cheers!
Lins
June 14, 2016 at 8:31 am
Maybe this:
insert into #mailing
(segment
,contact_number)
select @mailing + RIGHT('0' + CAST(base_value + row_num AS varchar(2)), 2)
,AccountNumber
from (
select distinct AccountNumber, ROW_NUMBER() over(order by AccountNumber) as row_num
from sales.Customer
where CustomerType = 'S'
) as c
cross apply (select cast(Max(right(segment,2)) AS tinyint) + 1 as base_value from #mailing) ca1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 14, 2016 at 8:58 am
I'm not sure I understand exactly what you're doing, but it sounds like you could use a Sequence object and just Concat() it onto the end of the string and you'd have what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 14, 2016 at 2:47 pm
Hi,
Thanks both for your replies and help.
Scott, That's brilliant, and has solved the puzzle. I applied it to my work example and removed this bit:
ROW_NUMBER() over(order by AccountNumber) as row_num
and it works perfectly. I would never have got to this solution on my own, and am new to using CROSS APPLY so this was great learning for me. I really like the way RIGHT('0' + CAST(base_value + row_num AS varchar(2)), 2) handles the segments past '09' - I wondered how to do that. Really elegant.
GSquared, I need to learn more about Sequence Objects as I'd never heard of them before your suggestion. They're new to 2012? I'll explore and see if they can also solve the puzzle.
Cheers both for helping me out and for giving me cool new things to learn.
Lins
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply