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.
| Author |
Topic |
|
daisy
Starting Member
13 Posts |
 Posted - 2006-05-15 : 10:59:46
|
| I have a table with different attributese.g. 0-6 Months13-18 Months13-24 Months7-12 Months1 Gift2 Gifts2+ Gifts$.01-$9.99$10-$49.00$100-$499.00$15-$24.00$50-$99.99I'm having a time figuring out how to place these in correctorder in the column. |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-05-15 : 11:03:43
|
| 1. The tables doesn't seems to be Normalized2. What is ur "Correct Order" ?Srinika |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-15 : 11:27:29
|
| Dollars come before gifts before months?Or is that just being mercenary?Maybe as time is money..... how many months to the dollar?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
daisy
Starting Member
13 Posts |
Posted - 2006-05-15 : 12:24:58
|
| Really my question is in ordering these Segment Attributes thatare already grouped within the table through by an Audience Type ID. Recency -> 0-6 MonthsFrequency -> 1 GiftMonetary -> $.01-$9.99My question is in ordering e.g.0-6 Months13-18 Months13-24 Months7-12 Months$.01-$9.99$10-$49.00$100-$499.00$15-$24.00$50-$99.99Sorted like this e.g.0-6 Months7-12 Months13-18 Months13-24 Months$.01-$9.99$10-$49.00$15-$24.00$50-$99.99$100-$499.00Hopefully this is more clear. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-15 : 12:44:59
|
| maybe order bycase when fld like '%Months%' then 1 when fld like '%Gift%' then 2 when fld like '%$%' then 3 end ,case when fld like '%Months%' then right('000' + left(fld, charindex('-',fld)-1), 3) end ,case when fld like '%Gift%' then right('000' + left(fld, charindex(' ',fld)-1), 3) end ,case when fld like '%$%' then convert(decimal(18,6),replace(left(fld, charindex(' ',fld)-1),'$','')) endProbably missing brackets and doesn't take into account the upper range but I'm sure you get the idea.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-05-15 : 12:54:06
|
I see Nigel beat me to it, but here's something more to give you the idea (although what I've got does look pretty similar to what Nigel has).I've added a type column to your data (because I thought you might have something like that (if you don't, it's probably a good idea to create it), but if not, you'll have to derive it from what the value contains as Nigel has shown you).--datadeclare @t table (v varchar(20), type varchar(10))insert @t select '0-6 Months', 'Recency'union all select '13-18 Months', 'Recency'union all select '13-24 Months', 'Recency'union all select '7-12 Months', 'Recency'union all select '1 Gift', 'Frequency'union all select '2 Gifts', 'Frequency'union all select '2+ Gifts', 'Frequency'union all select '$.01-$9.99', 'Monetary'union all select '$10-$49.00', 'Monetary'union all select '$100-$499.00', 'Monetary'union all select '$15-$24.00', 'Monetary'union all select '$50-$99.99', 'Monetary'--calculationselect *from @torder by case type when 'Recency' then 1 when 'Frequency' then 2 when 'Monetary' then 3 end, case type when 'Recency' then cast(left(v, charindex('-', v)-1) as decimal(10, 2)) when 'Frequency' then left(v, patindex(v, '[^0-9]')+1) when 'Monetary' then substring(v, 2, charindex('-', v)-2) endRyan Randallwww.monsoonmalabar.com London-based IT consultancy Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
|
|
|
|
|
Order by