automatic recoding - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

automatic recoding

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2012-07-01 : 17:52:24

hi,

did anybody ever read anything about the automatic value recoding in transact sql?

One might have several different data and data need to be recoded into smaller groups

for example:

customerID | emails send
------------------------
1252 | 1
3252 | 3
3253 | 11
3254 | 2
3255 | 7
3256 | 56
3257 | 44
3258 | 31
3259 | 6
3260 | 18
3261 | 21
3262 | 0
3263 | 24

recoded woud look:

customerID | emails send | Recoded
-----------------------------------
1252 | 1 | 1 (1-10 emails)
3252 | 3 | 1 (1-10 emails)
3253 | 11 | 2 (11-20 emails)
3254 | 2 | 1 (1-10 emails)
3255 | 7 | 1 (1-10 emails)
3256 | 56 | 6 (51-60 emails)
3257 | 44 | 5 (41-50 emails)
3258 | 31 | 4 (31-40 emails)
3259 | 6 | 1 (1-10 emails)
3260 | 18 | 2 (11-20 emails)
3261 | 21 | 3 (21-30 emails)
3262 | 4 | 1 (1-10 emails)
3263 | 24 | 3 (21-30 emails)

but with automatic this would also mean that at least new recoded groups are designed automatically based on distribution. so you would have groups of 10 emails instead of groups of 5 emails (due to several empty values in new groupings), etc.

If you have any suggestions where to look (book, forum, algorithms), i would appreciate it.

thanks

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-07-01 : 18:02:29

Looks like you're just looking for conditional logic...You could use a Case Expression like this:

SELECT CustomerId, [Emails Send],
CASE WHEN [Emails Send] between 1 and 10 then '1 (1-10 emails)'
WHEN [Emails Send] between 11 and 20 then '2 (11-20 emails)'
WHEN [Emails Send] between 21 and 30 then '3 (21-30 emails)'
WHEN [Emails Send] between 31 and 40 then '4 (31-40 emails)'
WHEN [Emails Send] between 41 and 50 then '5 (41-50 emails)'
WHEN [Emails Send] between 51 and 60 then '6 (51-60 emails)'
ELSE 'More than 60' END AS Recoded
FROM YourTable

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2012-07-01 : 18:07:24

thanks flamblaster.
but i'm not looking for this particular distribution of values. if there is a different case, i would be forced to change "hard-coded" rules. What I want is, to have this done automatically. Imagine, i have values between 0 and 11 and i want to create 5 groups. If I use above logic, it would not give me a good value distribution.

thanks

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-07-01 : 18:37:06

Ok, but can you explain hour your rules would work? For example, what would drive your new values to "auto-calculate"?

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-07-01 : 18:50:34

how about:


select customerid
      ,case when [emails send]=0 then '0 (no emails)'
            else (([emails send]/10+sign([emails send]%10))
                 +' ('
                 +(([emails send]/10+sign([emails send]%10))*10-9)
                 +'-'
                 +(([emails send]/10+sign([emails send]%10))*10)
                 +')'
                 )
       end as recoded
  from yourtable

edit:
I misunderstod the question, sorry.
Maybe you can use the above sql in combination with:
- function min([emails send]) and max([emails send]) to find the range and divide by number of groups you want, so you'll get a "step" value
- then calculate a ranking value
- and finally use the function dense_rank()

This would probably result in almost unreadable sql

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-07-01 : 19:27:13

NTile will distribute your rows into groups based on whatever criteria you like.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2012-07-03 : 10:27:05

thank you for hints.

best

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources