Software

Ask something
Question Locked

Puzzle - Selecting Multiple Groups by Varying TimesUusing T-SQL

0 helpful
2 replies

by dev

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

My company has come up with the start of an interesting list cleaning excercise.

Marketers and accountants being the way they are would not like to lose anyone from the list - except the people who never purchase or did anything.

Ok Sure! :|

What we aggreed on is to use groups...

We're breaking the list up into a safe list (new subscribers) and then grouping 6 month groups for 5+ years. Everyone else are the members we want to segment for the cleaning.

We've this query very manually using the UNION operator. I'm looking for a more elegant way to do this. It would be nice if I could control the time groups (perhaps select 3 month groups for paid members) and it would be nice to easily change the safe list from 1 year to 6 months if I wanted to play around a bit.

Before coding we broke it down into steps (groups) and then just union'd them together and connected excel to it.

1st group
- anyone on the list, clicked or confirmed inside of one year AND never purchased.

2nd group
- Purchased 0-6 months

3rd group
- Purchased 6-12 months

4th group
- Purchased 12-18 months

5th group
- Purchased 18-24 months

6th group
- Purchased 24-30 months

7th group
- Purchased 30-36 months

8th group
- Purchased 36-42 months

9th group
- Purchased 42-48 months

10th group
- Purchased 48-54 months

11th group
- Purchased 54-60 months

11th group
- Purchased 60+ months

12th group (aka - the 'crap' list)
- never purchased, never clicked, never confirmed, older than 1 year

---------------
Here's the code

select
list_
, ADV_ctm_nbr
, '01.ActiveInLastYearUnpaid' as [Segment]
from
members_
where
membertype_ = 'normal'
and
(
DateJoined_ > getdate() - 365 or
FSP_Last_click > getdate() - 365 or
(ConfirmDat_ > getdate() - 14 and confirmDat_ is not null) --issue here
)
and FSP_dt_lastbuy is null

union all

select list_, ADV_ctm_nbr, '02.Bought 0-6months' as [Segment] from members_ where membertype_ = 'normal' and

FSP_dt_lastbuy > getdate() - 365.0/2

union all

select list_, ADV_ctm_nbr, '03.Bought 6-12months' as [Segment] from members_ where membertype_ = 'normal' and

FSP_dt_lastbuy > getdate() - 365 and FSP_dt_lastbuy <= getdate() - 365.0/2

union all

select list_, ADV_ctm_nbr, '04.Bought 12-18months' as [Segment] from members_ where membertype_ = 'normal' and

FSP_dt_lastbuy > getdate() - 365*(3.0/2.0) and FSP_dt_lastbuy <= getdate() - 365.0

union all

select list_, ADV_ctm_nbr, '05.Bought 18-24months' as [Segment] from members_ where membertype_ = 'normal' and

FSP_dt_lastbuy > getdate() - 365*2 and FSP_dt_lastbuy <= getdate() - 365.0 *(3.0/2.0)

union all

select list_, ADV_ctm_nbr, '06.Bought 24-30months' as [Segment] from members_ where membertype_ = 'normal' and

FSP_dt_lastbuy > getdate() - 365*(5.0/2.0) and FSP_dt_lastbuy <= getdate() - 365.0 *2.0

union all

select list_, ADV_ctm_nbr, '07.Bought 30-36months' as [Segment] from members_ where membertype_ = 'normal' and

FSP_dt_lastbuy > getdate() - 365*3 and FSP_dt_lastbuy <= getdate() - 365.0 *(5.0/2.0)

union all

select list_, ADV_ctm_nbr, '08.Bought 36-42months' as [Segment] from members_ where membertype_ = 'normal' and

FSP_dt_lastbuy > getdate() - 365*(7.0/2.0) and FSP_dt_lastbuy <= getdate() - 365.0 *3.0

union all

select list_, ADV_ctm_nbr, '09.Bought 42-48months' as [Segment] from members_ where membertype_ = 'normal' and

FSP_dt_lastbuy > getdate() - 365*4 and FSP_dt_lastbuy <= getdate() - 365.0 *(7.0/2.0)

union all

select list_, ADV_ctm_nbr, '10.Bought 48-54months' as [Segment] from members_ where membertype_ = 'normal' and

FSP_dt_lastbuy > getdate() - 365*(9.0/2.0) and FSP_dt_lastbuy <= getdate() - 365.0 *4.0

union all

select list_, ADV_ctm_nbr, '11.Bought 54-60months' as [Segment] from members_ where membertype_ = 'normal' and

FSP_dt_lastbuy > getdate() - 365*5 and FSP_dt_lastbuy <= getdate() - 365.0 *(9.0/2.0)

union all

select list_, ADV_ctm_nbr, '12.Bought 60months+' as [Segment] from members_ where membertype_ = 'normal' and

FSP_dt_lastbuy <= getdate() - 365.0 *5.0

union all

select list_, ADV_ctm_nbr, '13.NeverConfirmedNeverBought' as [Segment] from members_ where membertype_ = 'normal' and

FSP_dt_lastbuy is null and

(DateJoined_ < getdate() - 365 or DateJoined_ IS NULL) AND

(FSP_Last_click < getdate() - 365 OR FSP_Last_click IS NULL ) AND

(ConfirmDat_ < getdate() - 14 OR confirmDat_ is null)

---------------

(It comes out in a nice bar chart in excel - import data:piviot table graph wizard - showing the groups in the order given in the groups. The last bar is the list to be segmented for list cleaning.)

Any insightful help would be most welcome! I'm looking for a real SQL Guru or anyone who enjoys a really good T-SQL puzzle.

Thanks VERY much for your thoughts!

dg

2 replies

by tony hopkinson

DateDiff with returns an integer of the number of whole months between two dates.
If you divide that by 6 and cast back to an integer.

2nd group would be 0, 11th group would be 9, anything bigger than 9 would be in the 60 months + group

The first and last, probably best left as they are.

So then all you need is a case statement to put out segment based on months / 6.

Going to chop it down to three unions anyway, should be a good bit more efficient as well.

select list_, ADV_ctm_nbr,
case DateDiff(month, getDate(),FSP_dt_lastbuy)
When 0 Then '02.Bought 0-6months',
When 1 Then '03......',
.....
When 11 Then '11.....
Else 12.Bougth 60 Months+'
end,
as [Segment]
from members_
where membertype_ = 'normal'
order by 3,1,2

Given you are using getdate() you are going to get some funny results using datediff when it isn't the 1st. Personally I'd consider always using the 1st of the month (datePart function will return this) of getDate().

Or ask your client what they really mean by six month divisions. Given you aren't dealing with leap years, I guess they aren't all that concerned if stuff at the edges ends up in the wrong segment.

Hths

This discussion is closed to new replies.