Hi All,

I need to create a query to be used in a report. I need to order the query
using a purchased date, but I am actually needing to order it by a range. So
all items purchased within the last 30 days are in group A, items purchased
between 31 and 60 days ago are group B and anything over 60 days is in group
C. I want to order it by this group field which doesn't actually exist in
the table. Any ideas how to create this group field using the PurchaseDate ?

Using the following data :

Item 1, 5 May 2007
Item 2, 7 January 2007
Item 3, 5 April 2007
Item 4, 9 May 2007
Item 5, 9 February 2007
Item 6, 9 April 2007
Item 7, 5 March 2007
Item 8, 7 May 2007
Item 9, 7 April 2007

Resulting dataset will be :

Group A, Item 1, 5 May 2007
Group A, Item 4, 9 May 2007
Group A, Item 8, 7 May 2007
Group B, Item 3, 5 April 2007
Group B, Item 6, 9 April 2007
Group B, Item 9, 7 April 2007
Group C, Item 2, 7 January 2007
Group C, Item 5, 9 February 2007
Group C, Item 7, 5 March 2007

Any ideas ?

Cheers,
Paul

"Paul" <paul@pacsoftware.com.au> wrote in message
news:3DB4CD14-E8E5-4692-9D94-4353EAFC0DD4@news.elevatesoft.com...
> Hi All,
>
> I need to create a query to be used in a report. I need to order the query
> using a purchased date, but I am actually needing to order it by a range.
> So all items purchased within the last 30 days are in group A, items
> purchased between 31 and 60 days ago are group B and anything over 60 days
> is in group C. I want to order it by this group field which doesn't
> actually exist in the table. Any ideas how to create this group field
> using the PurchaseDate ?
>

select fields, if(current_date - datefield < 10 then 'A'
else if(current_date - datefield < 20 then 'B' else 'C')) from table

Robert

Hi Robert,

thanks very much for the help. It's working perfectly.

Cheers,
Paul

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:8DD0FCCB-D6E0-43B5-9CD8-86C120DF12C8@news.elevatesoft.com...
>
> "Paul" <paul@pacsoftware.com.au> wrote in message
> news:3DB4CD14-E8E5-4692-9D94-4353EAFC0DD4@news.elevatesoft.com...
>> Hi All,
>>
>> I need to create a query to be used in a report. I need to order the
>> query using a purchased date, but I am actually needing to order it by a
>> range. So all items purchased within the last 30 days are in group A,
>> items purchased between 31 and 60 days ago are group B and anything over
>> 60 days is in group C. I want to order it by this group field which
>> doesn't actually exist in the table. Any ideas how to create this group
>> field using the PurchaseDate ?
>>
>
> select fields, if(current_date - datefield < 10 then 'A'
> else if(current_date - datefield < 20 then 'B' else 'C')) from table
>
> Robert
>
>
>