Gather cumulative weekly sales using SQLServer management studio

Question

My query gather a technician daily sales data.

select
  SUM(O.SUB_TOTAL) AS TOTALSALES,
  COUNT(DISTINCT O.ORDER_NO) AS BILLABLEORDERS
FROM ordhdr o
INNER JOIN schedule s ON s.ID_VAL = o.ORDER_NO
WHERE
  s.DATE = Convert(varchar(10), GETDATE()-1,121)
  AND O.[TYPE] = 'SVC'

However, I also want to get weekly cumulative sales to know whether he is on track or not for his weekly numbers but I struggling transforming the query.

This has to reset for each Sunday or Monday so I cannot use a CurrentDate-7 function.
I don't know how to only look at a CURRENT weeks data using SQL-Server Management Studio.

You will struggle if you cannot define your requirements precisely. Today, for me, is Wednesday Jul 10. For a "weekly" total as of today, what is the earliest date included in the weekly sum? While you think about that, consider what happens if any day within this weekly period is a holiday (or other non-work day). — SMor, Jul 10 '19 at 17:57
i would be find with just saying work week starts Sunday or Monday, not sure if that is a setting in SQL code. Also i don't care about holidays. — Mary Mahoney, Jul 10 '19 at 18:42
Are you looking to understand how to get the start of your week? If so, check out this post: https://stackoverflow.com/questions/7168874/get-first-day-of-week-in-sql-server — critical_error, Jul 10 '19 at 19:47

score 0 · Answer 1 · answered Jul 10 '19 at 19:46

Look into the 'DatePart' function. You can use it to identify which week out of the year a given date resides in, and it seems to turn over on Sunday. For instance:

datepart(week, '2019-07-06') -- Saturday, returns 27
datepart(week, '2019-07-07') -- Sunday, returns 28

That alone should get you going. However, you can throw in a few more techniques to get all the information in one resultset.

Consider the following ordhdr table:

declare @ordhdr table (
    order_no int,
    sub_total decimal(8,2),
    type varchar(15)
);

insert @ordhdr values
    (1, 23.25, 'svc'),
    (2, 324.23, 'svc'),
    (3, 423.89, 'svc'),
    (4, 324.80, 'svc'),
    (5, 234.23, 'svc'),
    (6, 923.23, 'svc');

... and the following schedule table:

declare @schedule table (id_val int, date date);

insert @schedule values 
    (1, '2019-07-04'),
    (2, '2019-07-04'),
    (3, '2019-07-08'),
    (4, '2019-07-09'), 
    (5, '2019-07-09'), 
    (6, '2019-07-10');

Well, using datepart, datename, cross apply, and grouping sets, you can do this:

select      ap.year,
            ap.weekOfYear,

            dayOfWeek = 
                case 
                when ap.weekOfYear is null then '<entire year>'
                when ap.dayOfWeek is null then '<entire week>'
                else ap.dayOfWeek
                end,

            s.date,
            totalsales = sum(o.sub_total),
            billableorders = count(distinct o.order_no)
from        @ordhdr o
join        @schedule s on s.id_val = o.order_no 
cross apply (select 
                year = datepart(year, s.date),
                weekOfYear = datepart(week, s.date),
                dayOfWeek = datename(weekday, s.date)
            ) ap
where       o.type = 'svc'
group by    grouping sets (
                (ap.year, ap.weekOfYear, ap.dayOfWeek, s.date),
                (ap.year, ap.weekOfYear),
                (ap.year)
            )
order by    weekOfYear, date

Which will give you daily, weekly, and yearly totals.

Gather cumulative weekly sales using SQLServer management studio

1 Answers1