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
Peter NordbergIT ManagerAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
If you want only the current week then no need for the ORDER BY clause:
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) 
HAVING DATEPART(WEEK, WeekStart) = DATEPART(WEEK, GETDATE())

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
 
Peter NordbergIT ManagerAuthor Commented:
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.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Nitin SontakkeDeveloperCommented:
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

0
 
Peter NordbergIT ManagerAuthor Commented:
Hi, in the examples you mentioned I get several weeks as a response. Is it possible to get only the current week?
0
 
Mark WillsTopic AdvisorCommented:
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
0
 
Peter NordbergIT ManagerAuthor Commented:
Hm, can you give me an example of that last suggestion?!
0
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Where  are you getting @fromdate ? Is this a stored procedure ?

If so, calculate the @weekstart and @weekend before the select, and use datetime when doing so, then your select is MUCH easier

SELECT COUNT(*) AS q, DATEPART(WEEK, orderdate) AS WeekNo
FROM orders
WHERE  OrderDate >= @WeekStart AND OrderDate <= @WeekEnd AND InvoiceID > 0 AND OrderStatusID = 2 AND OrderTypeID = 2
GROUP BY DATEPART(WEEK, orderdate)

Open in new window

And if it was only ever a week, then could use DATEPART(WEEK, @weekstart) AS WeekNo and no group by....
0
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
@Vitor,

I think the problem is calculating weekstart. It is being derived (in part) from orderdate. There is nothing to limit the range of orderdate other than weekstart (in the "where" clause). So, why wouldnt the query run and select all orderdates - giving rise to multiple weeks.

By way of example
declare @fromdate datetime = '20180204'

SELECT dateadd(week, datediff(day,@fromDate,orderdate) / 7, @fromDate) AS WeekStart
FROM (values (1,getdate()-90),(2,getdate()-60),(2,getdate()-30),(2,getdate()-5)) orders(id,orderdate)  

Open in new window

I end up with a range of weekstart values :
WeekStart
2017-11-19 00:00:00.000
2017-12-17 00:00:00.000
2018-01-14 00:00:00.000
2018-02-04 00:00:00.000

Open in new window

Now look at the "where" clause - every orderdate will be included
0
 
Mark WillsTopic AdvisorCommented:
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.
0
 
Peter NordbergIT ManagerAuthor Commented:
Do you mean the comment from Vitor?
0
 
Mark WillsTopic AdvisorCommented:
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
0
 
Peter NordbergIT ManagerAuthor Commented:
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...
0
 
Mark WillsTopic AdvisorCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.