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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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 WillsTopic 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
Vitor MontalvãoMSSQL 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
T-SQL

From novice to tech pro — start learning today.