add week ending date sql server 1

add week ending date sql server

SELECT  
    DATEADD(DAY, 7 - DATEPART(WEEKDAY, orderDate), CAST(orderDate AS DATE)) AS ordersPerWeek, 
    sum(orderAmount) AS orderSumForGrouping, 
    count(1) AS numberOfOrdersWithinGrouping
FROM    @table o
GROUP   BY DATEADD(DAY, 7 - DATEPART(WEEKDAY, orderDate), CAST(orderDate AS DATE))

Here is what the above code is Doing:
1. We’re using the DATEADD function to add the number of days to the orderDate column to get the first day of the week.
2. We’re using the DATEPART function to get the weekday number of the orderDate column.
3. We’re using the CAST function to convert the orderDate column to a date.
4. We’re using the GROUP BY clause to group the data by the first day of the week.
5. We’re using the SUM function to get the sum of the orderAmount column.
6. We’re using the COUNT function to get the number of orders within the grouping.

Similar Posts