how to do this "between dates" query?

Hi,

I have a query as follows:

SELECT SUM(Total) AS WeekRetailTotal, COUNT(*) AS MonthRetailOrderNo, DATEPART(wk, OrderDate) AS SalesWeek, YEAR(OrderDate) AS SalesYear
FROM dbo.Orders_Retail
WHERE (account = @.Account) AND (OrderStatus <> 'Deleted') AND (PayStatus <> 'Pending') AND (OrderStatus <> 'Refunded')
GROUP BY YEAR(OrderDate), DATEPART(wk, OrderDate)
ORDER BY YEAR(OrderDate), DATEPART(wk, OrderDate)

the results look like this

WeekRetailTotal

MonthRetailOrderNo

SalesWeek

SalesYear

￡397.55

8

3

2002

etc etc for each week in a year and then it goes onto the next year.

What I would like to do, is feed the query a variable as the start week and year and then also for the endweek and year.

I've tried to do a WHERE DATEPART(wk, OrderDate) > @.StartDate AND DATEPART(wk, OrderDate)
< @.EndDate AND YEar(OrderDate) > @.StartYear AND YEAR(OrderDate) < @.EndYear

But that's not correct, it only bring in the weeks in both years that are in between those two week range variables.

I need the startweek and year to be "one" starting point and the endweek and endyear be the ending point.

Any ideas?

Thanks

It is common for Date query questions to receive the suggestion of using a Calendar table to assist. In this case it would help as you could turn your Year/Week pairs into dates and then do a simple date range comparison.

Try this approach, using only date functions:

Where DateDiff( wk, DateAdd(yy, @.StartYear - 1900 , '1/1/1900' ), OrderDate ) >= @.StartWeek

And DateDiff(wk, DateAdd(yy, @.EndYear - 1900, '1/1/1900' ), OrderDate ) < @.EndWeek

The first comparison needs to be >= because the first day of the year is in the first week of the year -- the comparison gets off by one. The end should be okay. I used DateAdd to get the first day of each year, you could use another approach like string concatenation.

|||

There is native BETWEEN-AND syntax support in SQL Server. The bigger issue here is you need to consider day of week, i.e. you want the date range to be between the first day of the start year/week and the last day of the end year/week (inclusive on both ends):

Here is my approach (working sample against Northwind database):

DECLARE @.StartWeek INT, @.EndWeek INT, @.StartYear INT, @.EndYear INT

SET @.StartWeek = 50;

SET @.EndWeek = 2;

SET @.StartYear = 1996;

SET @.EndYear = 1997;

DECLARE @.StartDate DATETIME, @.EndDate DATETIME;

SET @.StartDate = DATEADD(week, @.StartWeek-1, '1/1/' + CAST(@.StartYear AS VARCHAR(4))) -- get into the @.StartWeek-th week

SET @.StartDate = DATEADD(day, 1-DATEPART(weekday, @.StartDate), @.StartDate) -- get the First Day (Sunday by default) of that week

SET @.EndDate = DATEADD(week, @.EndWeek-1, '1/1/' + CAST(@.EndYear AS VARCHAR(4))) -- get into the @.EndWeek-th week

SET @.EndDate = DATEADD(day, 7-DATEPART(weekday, @.EndDate), @.EndDate) -- get the Last Day (Saturday by default) of that week

--SELECT @.StartDate, @.EndDate

SELECT orderdate, DATEPART(year, orderdate) AS Year, DATEPART(week, orderdate) AS Week, DATEPART(weekday, orderdate) AS DayOfWeek FROM orders

WHERE OrderDate BETWEEN @.StartDate AND @.EndDate

|||thanks guys! - I'll give it a go!!