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
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
dateadd(week, datediff(day,@fromDate,OrderDate) / 7, @fromDate) AS WeekStart
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
Thanks for help!