Determine multiple fields from a single field based on condition

[techvarun]

Hello All

         I have a table with 3 fields

Value	Week1	Week2
-21141.5	10/27/2014	10/27/2014
-19016.3	10/27/2014	10/20/2014
-16038.1	10/27/2014	10/13/2014
-15103	10/27/2014	10/6/2014
-12471.5	10/27/2014	9/29/2014
-11572	10/27/2014	10/27/2014
-11454.9	10/27/2014	10/20/2014
-10400.5	10/27/2014	10/13/2014
-10331.9	10/27/2014	10/6/2014
-10136.9	10/27/2014	9/29/2014
-9894.09	10/27/2014	10/27/2014
-9787.82	10/27/2014	10/20/2014
-9787.82	10/27/2014	10/13/2014
-9148.38	10/27/2014	10/6/2014
-8858.29	10/27/2014	10/20/2014
-8178.99	10/27/2014	10/27/2014
-7805.58	10/27/2014	10/20/2014
-7507.92	10/27/2014	10/13/2014
-7301.05	10/27/2014	10/6/2014
-6702.96	10/27/2014	9/29/2014
-6566.11	10/27/2014	10/27/2014
-5860.31	10/27/2014	10/20/2014

Now i want to determine the field value in to 5 different fields based on below condition

Field1 = Select where WEEK1 = Current Week -1 &  WEEK2 = Current Week - 1

Field2 = Select where WEEK1 = Current Week -1 &  WEEK2 = Current Week - 2

Field3 = Select where WEEK1 = Current Week -1 &  WEEK2 = Current Week - 3

Field4 = Select where WEEK1 = Current Week -1 &  WEEK2 = Current Week - 4

Field5 = Select where WEEK1 = Current Week -1 &  WEEK2 = Current Week - 5 And my current week is 11/3/2014 but i have to maintain dynamic.

Please help me to create the script

Thanks in advance

Varun    

[MK_QSL]

Date(Date#(Week1,'YYYYMM'),'MM/DD/YYYY') AS Week1Date,

Date(Date#(Week2,'YYYYMM'),'MM/DD/YYYY') AS Week2Date,

Week(Date(Date#(Week1,'YYYYMM'),'MM/DD/YYYY')) AS Week1,

Week(Date(Date#(Week2,'YYYYMM'),'MM/DD/YYYY')) AS Week2;