Help Masters Of This Forum!!!

[sipi41]

I'm sorry to bother you but I have a query that is breaking my head

I have a table like this... (which say the name of a location, the product return and which week, especified as 01.02 which means that is year1, week 2... etc... so... for each week i have a record for the location)

LOCATION NAME --- WEEK RETURNS
---------------------------------------------------
Location #1 ----- 01.22 ----- 3
Location #2 ----- 01.22 ----- 1
Location #3 ----- 01.22 ----- 5
---------------------------------------------------
Location #1 ----- 01.23 ----- 8
Location #2 ----- 01.23 ----- 0 *
Location #3 ----- 01.23 ----- 3
---------------------------------------------------
Location #1 ----- 01.24 ----- 0
Location #2 ----- 01.24 ----- 0 *
Location #3 ----- 01.24 ----- 3
---------------------------------------------------
Location #1 ----- 01.25 ----- 1
Location #2 ----- 01.25 ----- 0 *
Location #3 ----- 01.25 ----- 4

WHAT I WANT is to have the posibility to list from this table ONLY the locations that in the past 3 weeks have 0 return (which I marked with *), in the example I have the location #2... because that's the only one that for the last 3 weeks have 0 returns. IS THIS POSSIBLE?

THANKS FOR YOUR HELP!!!

 

[WayneRyan]

sipi,

I'd handle this with a set of queries:

Query1 <-- Get your three latest dates

Select Top 3 Week
From   YourTable
Order By Week Desc

Query2 <-- Join your table with the three latest dates and return only the 0 Sums

Select YourTable.LocationName, Sum(YourTable.Returns)
From   YourTable Inner Join Query1 On
       YourTable.Week = Query1.Week
Group By YourTable.LocationName
Having Sum(YourTable.Returns) = 0

hth,
Wayne