Query To Get OrderCategory For OrderDate Of [Date -2]

[The Brown Growler]

Hi,

I would be grateful if anyone could please assist me in the creation of a query that checks the order date of a record and then retrieves the ordercategory for the record with [orderdate -2] with respect to the original record.

To illustrate the result I have pasted below the fields and data from the table named T_Orders:


[Customer] [Site] [OrderDate] [OrderCategory]
Joes Bakery, Rennes, 01/09/2011, C008
Joes Bakery, Rennes, 05/09/2011, C005
Joes Bakery, Rennes, 07/09/2011, C019
Joes Bakery, Rennes, 03/10/2011, C002
Joes Bakery, Rennes, 23/10/2011, C007
Joes Bakery, London, 04/09/2011, C010
Joes Bakery, London, 05/09/2011, C004
Joes Bakery, London, 17/09/2011, C056
Joes Bakery, London, 23/10/2011, C001
Joes Bakery, London, 28/10/2011, C083


The query result I would like to get is as below:


[Customer] [Site] [OrderDate] [OrderCategory] [OrderCat-2]
Joes Bakery, Rennes, 01/09/2011, C008
Joes Bakery, Rennes, 05/09/2011, C005
Joes Bakery, Rennes, 07/09/2011, C019, C008
Joes Bakery, Rennes, 03/10/2011, C002, C005
Joes Bakery, Rennes, 23/10/2011, C007, C019
Joes Bakery, London, 04/09/2011, C010, C002
Joes Bakery, London, 05/09/2011, C004, C007
Joes Bakery, London, 17/09/2011, C056, C010
Joes Bakery, London, 23/10/2011, C001, C004
Joes Bakery, London, 28/10/2011, C083, C056


A new column is created in the output query which lists the OrderCategory for the order placed 2 records before the current records in terms of date descending


Any help most appreciated.

Rgds

 

[Taruz]

Hi.

I assume that the ID field in your table..:

select 
          Customer
         ,Site
         ,OrderDate
         ,OrderCategory
         ,[OrderCat-2]
from (
                    select  
                                 OrderCategory as [OrderCat-2]
                               ,(select count(*) from T_Orders where [COLOR="red"]ID[/COLOR]<=trz.[COLOR="Red"]ID [/COLOR])+2 as OrderID
                    from T_Orders as trz
           ) as tt 
right join (
                   select 
                              Customer
                             ,Site
                             ,OrderDate
                             ,OrderCategory
                             ,(select count(*) from T_Orders where [COLOR="red"]ID[/COLOR]<=trz.[COLOR="Red"]ID [/COLOR]) as OrderID
                   from T_Orders as trz
            ) as tr
on tt.OrderID = tr.OrderID