SQL Server Rolling up Values into Weekdays

pexxcrt2  于 2023-04-28  发布在  其他
关注(0)|答案(3)|浏览(106)

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.

zf9nrax1

zf9nrax11#

One option might be to simply add a custom column for ReportingDay and then aggregate by that in a subsequent step.

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())
),
prior_result AS ( 
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
)
select 
CASE 
  WHEN DATEPART(WEEKDAY, date) IN('Saturday','Sunday') 
  THEN 'Monday' 
  ELSE DATEPART(WEEKDAY, date) 
END AS ReportingDay, SUM(RunningTotal) AS SUM_RunningTotal
FROM prior_result
GROUP BY 
CASE 
  WHEN DATEPART(WEEKDAY, date) IN('Saturday','Sunday') 
  THEN 'Monday' 
  ELSE DATEPART(WEEKDAY, date) 
END
42fyovps

42fyovps2#

You can create a second date column (i call it dtWeekend) that you use for grouping only, something like this:

;WITH date_cur_month AS(
    SELECT  dt
    ,   dateadd(day, case (datepart(weekday, dt) + @@DATEFIRST + 5) % 7 + 1 when 6 then 2 when 7 then 1 else 0 end, dt) AS dtWeekend
    FROM    (
        SELECT  CONVERT(datetime, CONVERT(varchar(6), getdate(), 112) + '01', 112) AS dt
        ) x
    UNION ALL
    SELECT  dateadd(day, 1, dt) AS dt
    ,   dateadd(day, 1 + case (datepart(weekday, dt) + @@DATEFIRST + 5) % 7 + 1  when 6 then 2 when 7 then 1 else 0 end, dt) AS dtWeekend
    FROM    date_cur_month
    WHERE   datepart(month, dateadd(day, 1, dt)) = datepart(month, getdate())
)
    SELECT  CONVERT(nvarchar(8), d.dtWeekend, 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 CONVERT(nvarchar(8), d.dtWeekend, 3)

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

9bfwbjaz

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.

CREATE TABLE #TEST
(
  [DATE] DATE PRIMARY KEY,
  RunningTotal INT,
  sCount INT IDENTITY(1,1)
)

INSERT INTO #TEST 
VALUES
('20230401',123),
('20230403',256),   
('20230404',192),
('20230405',168),
('20230406',5)

SELECT [DATE],
       RunningTotal as Old_RunningTotal,
       SUM(RunningTotal)OVER(
                                ORDER BY  CASE WHEN(DATEPART(DW,[DATE]) = 7) THEN DATEADD(DD,2,[DATE]) --Saturday, add 2 days
                                               WHEN(DATEPART(DW,[DATE]) = 1) THEN DATEADD(DD,1,[DATE]) --Sunday, add 1 day
                                          ELSE [DATE]
                                          END, sCount
                              ) as New_RunningTotal
FROM #TEST
ORDER BY [DATE]

For combining to your query, could you try:

CREATE TABLE #TEST
(
          [DATE] DATE PRIMARY KEY,
          RunningTotal INT,
          sCount INT IDENTITY(1,1)
)

;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()))

INSERT INTO #TEST
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

SELECT * FROM 
(    
    SELECT [DATE],
                   RunningTotal as Old_RunningTotal,
                   SUM(RunningTotal)OVER(
                                            ORDER BY  CASE WHEN(DATEPART(DW,[DATE]) = 7) THEN DATEADD(DD,2,[DATE]) --Saturday, add 2 days
                                                           WHEN(DATEPART(DW,[DATE]) = 1) THEN DATEADD(DD,1,[DATE]) --Sunday, add 1 day
                                                      ELSE [DATE]
                                                      END, sCount
                                          ) as New_RunningTotal
     FROM #TEST
) as A
WHERE DATEPART(DW,[DATE]) NOT IN (1,7) --For excluding Weekend
     ORDER BY [DATE]

相关问题