Day and week

  
Q: My manager has asked me if I could make some reports with order information per day and per week. Can you give me an example how you can get totals by date and by week? (In the order table I have order-date and total-price) Is it also possible to have a range between 2 dates?
A: Have you tried an advanced query like:

-- total orders per day
SELECT {ORDER}.Date, sum( {ORDER}.TotalPrice )
FROM {ORDER} WHERE
{ORDER}.Date >= @DateSince
AND {ORDER}.Date <= @DateUntil
GROUP BY {ORDER}.Date
ORDER BY {ORDER}.Date

-- total orders per week
SELECT datepart( week, {ORDER}.Date ), sum( {ORDER}.TotalPrice )
FROM {ORDER} WHERE
{ORDER}.Date >= @DateSince
AND {ORDER}.Date <= @DateUntil
GROUP BY datepart( week, {ORDER}.Date )
ORDER BY datepart( week, {ORDER}.Date )

In reports it usually also comes in handy the use of WITH ROLLUP modifier associated to the GROUP BY. Something like

GROUP BY {ORDER}.Date WITH ROLLUP

It provides an extra row with the total of the grouped rows. Try it!