Re: FN-FORUM: sql question

date posted 23rd October 2006 19:44

Ah - that looks just the job. I have never come across "case" in a select
statement before.

Brilliant - Pam



----- Original Message -----
From: "Andy Macnaughton-Jones" [EMAIL REMOVED]
To: [EMAIL REMOVED]
Sent: Monday, October 23, 2006 7:52 PM
Subject: RE: FN-FORUM: sql question


>
> So for each field you'd have;
>
>
> Declare @varMonth varchar(50)
> Select @varMonth= 'February'
>
> SELECT
> case when @varMonth = 'January' then
> case January_band when = 1 then Price_Band_1
> when = 2 then
> Price_Band_2
> when = 3 then
> Price_Band_3
> when = 4 then
> Price_Band_4
> else 0
> end,
> when @varMonth = 'February' then
> case February_band when = 1 then Price_Band_1
> when = 2 then
> Price_Band_2
> when = 3 then
> Price_Band_3
> when = 4 then
> Price_Band_4
> else 0
> end,
>
> etc.
>
>
> Order by 1 desc
>
>
> If my understanding is correct anyway.
>
> To be quite honest though you'd actually need less fields by just
> putting the price_band in each of the month fields and it's make it
> easier in the query.
>
> Cheers
> Andy
>
>
>
>
> -----Original Message-----
> From: [EMAIL REMOVED] [EMAIL REMOVED] On Behalf Of
> [EMAIL REMOVED]
> Sent: Monday, October 23, 2006 5:35 PM
> To: Andy Macnaughton-Jones
> Subject: Re: FN-FORUM: sql question
>
>
> The relevant part of the structure is
>
> Price_Band_1` varchar(20) NOT NULL default '0', Price_Band_2`
> varchar(20) NOT NULL default '0', Price_Band_3` varchar(20) NOT NULL
> default '0', Price_Band_4` varchar(20) NOT NULL default '0',
> January_Band` tinyint(4) NOT NULL default '0', February_Band`
> tinyint(4) NOT NULL default '0', March_Band` tinyint(4) NOT NULL
> default '0', April_Band` tinyint(4) NOT NULL default '0',
> May_Band` tinyint(4) NOT NULL default '0', June_Band` tinyint(4) NOT
> NULL default '0', July_Band` tinyint(4) NOT NULL default '0',
> August_Band` tinyint(4) NOT NULL default '0', September_Band`
> tinyint(4) NOT NULL default '0', October_Band` tinyint(4) NOT NULL
> default '0',
> November_Band` tinyint(4) NOT NULL default '0', December_Band`
> tinyint(4) NOT NULL default '0',
>
>
> Each of the month bands point to one of Price_Band_1, Price_Band_2,
> Price_Band_3, or Price_Band_4 which contain the Price for the month.
> For February some records will point to Price_Band_1 and some to
> Price_Band_2 etc.
>
>
> When some one searches for February I want it sorted in Price order but
> for some records that is taken from Price_Band_1 while other from
> Price_Band_3 etc
>
>
>
>
> Pam
>
>
>
>
>
>
>
> ----- Original Message -----
> From: "David Long" [EMAIL REMOVED]
> To: [EMAIL REMOVED]
> Sent: Monday, October 23, 2006 5:12 PM
> Subject: Re: FN-FORUM: sql question
>
>
>>
>> [EMAIL REMOVED] wrote:
>>> I have a table where you have a list of bands - January_Band,
>>> February_Band etc each one points to one of four Price band fields. I
>
>>> want to sort the output on the Price for a particular month that has
> been
>>> input but I can't work out how. For some records it will Price Band 1
> and
>>> for some it will be Price Band 2 etc. I can't work out how it can be
>>> done. It appears to be impossible. Any ideas welcome
>>>
>>
>> Can you show us your table structures, some sample data, and your
> expected
>> result set? It's difficult to visualise exactly what you're doing
> without
>> this.
>>
>> Dave
>>
>> --
>> [EMAIL REMOVED]
>> http://www.longwaveconsulting.com/
>>
>>
>> --
>> Freelancers, contractors earn more with Prosperity4
>> Call 0870 870 4414 or visit www.prosperity4.com
>> and benefit from Inland Revenue approved expenses today.
>>
>> To advertise here: http://www.freelancers.net/advertising.html
>>
>>
> --
> Freelancers, contractors earn more with Prosperity4
> Call 0870 870 4414 or visit www.prosperity4.com
> and benefit from Inland Revenue approved expenses today.
>
> To advertise here: http://www.freelancers.net/advertising.html
>
>
> --
> Freelancers, contractors earn more with Prosperity4
> Call 0870 870 4414 or visit www.prosperity4.com
> and benefit from Inland Revenue approved expenses today.
>
> To advertise here: http://www.freelancers.net/advertising.html
>
>