asked on

Count orders sold in particular week

Hi,

I have an order table where I like to count the orders sold for a week based on a chosen date. The orders table look like this

Orders
oID int, price decimal, orderDate datetime

I tried the below but then I get several weeks, not the one based on @fromDate.

SELECT COUNT(o.oID) AS q, DATEPART(WEEK, WeekStart) AS WeekNo
FROM 
(
 SELECT *, 
  dateadd(week, datediff(day,@fromDate,OrderDate) / 7, @fromDate) AS WeekStart  
 FROM Orders
) o
WHERE  CAST(o.OrderDate AS DATE) >= o.WeekStart AND CAST(o.OrderDate AS DATE) <= DATEADD(DAY, 7, o.WeekStart) AND o.InvoiceID > 0 AND o.OrderStatusID = 2 AND o.OrderTypeID = 2
GROUP BY WeekStart
ORDER BY o.WeekStart

Open in new window

Thanks for help!

Peter

Vitor Montalvão

GROUP BY the DATEPART:

SELECT COUNT(o.oID) AS q, DATEPART(WEEK, WeekStart) AS WeekNo
FROM 
(
 SELECT *, 
  dateadd(week, datediff(day,@fromDate,OrderDate) / 7, @fromDate) AS WeekStart  
 FROM Orders
) o
WHERE  CAST(o.OrderDate AS DATE) >= o.WeekStart AND CAST(o.OrderDate AS DATE) <= DATEADD(DAY, 7, o.WeekStart) AND o.InvoiceID > 0 AND o.OrderStatusID = 2 AND o.OrderTypeID = 2
GROUP BY DATEPART(WEEK, WeekStart) 
ORDER BY o.WeekStart

Open in new window

Peter Nordberg

ASKER

Hi and thanks for answer,

I get this error when I try.

Column "o.WeekStart" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Nitin Sontakke

Put IDENTICAL value in ORDER BY that you put in GROUP BY as in:

GROUP BY DATEPART(WEEK, WeekStart) 
ORDER BY DATEPART(WEEK, WeekStart)

Open in new window

Optionally, you can put column position from the original select query in ORDER BY clause, as in:

GROUP BY DATEPART(WEEK, WeekStart) 
ORDER BY 2

Open in new window

Peter Nordberg

ASKER

Hi, in the examples you mentioned I get several weeks as a response. Is it possible to get only the current week?

Mark Wills

Well, let's take a step back and just do

SELECT COUNT(o.oID) AS q, WeekStart AS WeekNo
FROM 
(
 SELECT *, 
  dateadd(week, datediff(day,@fromDate,OrderDate) / 7, @fromDate) AS WeekStart  
 FROM Orders
) o
WHERE  CAST(o.OrderDate AS DATE) >= o.WeekStart AND CAST(o.OrderDate AS DATE) <= DATEADD(DAY, 7, o.WeekStart) AND o.InvoiceID > 0 AND o.OrderStatusID = 2 AND o.OrderTypeID = 2
GROUP BY WeekStart
ORDER BY o.WeekStart

Open in new window

I think you will find the calculation "dateadd(week, datediff(day,@fromDate,OrderDate) / 7, @fromDate) AS WeekStart " is based on orderdate, so it will generate a lot of weeks....

And yes, you could get current week (or last week) by using getdate() instead of orderdate

Peter Nordberg

ASKER

Hm, can you give me an example of that last suggestion?!

SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

This doesnt make sense.

Could someone please explain why that "answer" works efficiently

I did run it up (above) and proved exactly what the Asker didnt want - multiple weeks....

Simply adding: HAVING DATEPART(WEEK, WeekStart) = DATEPART(WEEK, GETDATE()) is a bit of a crock and would expect a lot more from experts in this place.

Peter Nordberg

ASKER

Do you mean the comment from Vitor?

Mark Wills

Thanks for replying peter,

Was talking about Vitor's solution - he Masked the underlying problem.

It will provide the answer you want, but your query is hugely expensive on resources because of the way it tries to use orderdate to get weekno.

I was more than a bit surprised, but it is entirely your call and your prerogative .

Cheers,
Mark Wills

Peter Nordberg

ASKER

Well, maybe I did it wrong, but his answer and your answer led me to a conclusion on how to solve the issue. Maybe I should have added that in the comment. Normally when I post these qustions I get an exact solution, but this time I used the answers to a solution that blend sql and other code. Sorry if it created confusion...

Mark Wills

Nope, you did nothing wrong, and I should have simply bit my lip. I reacted poorly, I am the one in the wrong.

I want you to get the best advice we (as experts) can provide, along with reasons and explanations,

Simply adding: HAVING DATEPART(WEEK, WeekStart) = DATEPART(WEEK, GETDATE()) isnt really the best advice in my opinion.

Again, it is me who has done the wrong thing. My altruism interferes with real life...

Cheers,
Mark