I have a Basic SQL Query which looks at the SalesOrders table of our database, and shows all sales "per day" for a specific Sales Rep.
;with date_cur_month as(
select convert(datetime, convert(varchar(6), getdate(), 112) + '01', 112) as dt
union all
select dateadd(day, 1, dt) as dt
from date_cur_month
where datepart(month, dateadd(day, 1, dt)) = datepart(month, getdate()))
select convert(nvarchar(8), d.dt, 3) as date
, sum(sum(ci.C_PRETAXTOTAL)) OVER (Order by CAST(d.dt AS date)) AS RunningTotal
from [T_SALESORDER] ci
right outer join date_cur_month d on cast(ci.C_DATE as date) = d.dt
where C_SALESREP = '8602832427308'
group by d.dt
This works fine.
However, what I need it do is to look at the days of the week being returned, and if the day is a Saturday or Sunday, roll that data into the next working day (Mon-Fri).
So, if for example the data returned was :
date RunningTotal
01/04/23 123
03/04/23 256
04/04/23 192
05/04/23 168
06/04/23 5
Because the 1st is a Saturday, the Monday Data (3rd April) should return £379 (123 + 256).
I have tried a few variations, but can't seem to make this work correctly.
3条答案
按热度按时间zf9nrax11#
One option might be to simply add a custom column for ReportingDay and then aggregate by that in a subsequent step.
42fyovps2#
You can create a second date column (i call it dtWeekend) that you use for grouping only, something like this:
dtWeekend is shifted 0, 1 or 2 days from regular date depending on if it's weekday, saturday or sunday.
To avoid unpredictable server language and date settings, i'm using the @@datefirst formula
9bfwbjaz3#
I think one of the approach is you could perform another roll up on top of your first RunningTotal result by utilizing the identity trick.
Please note here I am using the US
DATE
format.For combining to your query, could you try: