I'm trying to use an SQL query to find parts that have three or more orders in a week's timeframe. The weeks would be as follows: 9/2/13-9/8/13, 9/9/13-9/15/13, 9/16/13-9/22/13, and so on. I want the query to display the results according to the weekly timeframe. This is what I have so far.

select partid=i.partid, partdescription, count(partorderid) as NumberOfPartOrders

from part i

join partorder j

on i.partid = j.partid

where ordersubmitted between '09/02/13' and '09/08/13''

or ordersubmitted between '09/09/13' and '09/15/13'

group by i.partid, partdescription

having count(partorderid) > 2

It works fine for a single ordersubmitted range, but when I use multiple ranges it looks at 3 or more orders between 09/02 and 09/15. I want 3 or more orders listed between 09/02-09/08 and 3 or more orders listed between 09/09 and 09/15.

Try modifying your query to also group by week (based on the ordersubmitted date). Be sure to SET DATEFIRST 1 since your date ranges appear to be Monday through Sunday (which differs from the default Sunday through Saturday), e.g.:

SET DATEFIRST 1;

select partid=i.partid, partdescription, count(partorderid) as NumberOfPartOrders

from part i

join partorder j

on i.partid = j.partid

where ordersubmitted between '09/02/13' and '09/08/13''

or ordersubmitted between '09/09/13' and '09/15/13'

group by i.partid, partdescription, DATEPART(week, ordersubmitted)

having count(partorderid) > 2;

...except that I just noticed you are doing an assignment in this query, which will fail when multiple rows are retu

Why don't you build a separate table with week ranges ( w.r.to your requirement) and join it with partorder table?

That would be easy and can cover multiple week ranges.

USE case when clause in select part.

the way you need will be given by using casing.