I need to take cumulative based on condition. If it is holiday I dont want to take cumulative.

If the row is the first row in date order and if that day is holiday then it should take daywiseplan value. For all subsequent rows, if IsHolday equals zero, accumulate DaywisePlan into the total. If IsHolday equals one, accumulate the value of DaywisePlan on the next row in date order where IsHolday equals zero

CodePudding user response：

in one Query id´ can't think of, but use a CTE , it is quite easy using The window Function SUM and FIRST_VALUE

if you have more month and want to have a SUM for all month sereately, you need to PARTITION both window function mothwise

WITH CTE AS
(SELECT
    [Date], [DaywisePlan], [IsHoliday],
    FIRST_VALUE([DaywisePlan]) OVER(PARTITION BY [IsHoliday] ORDER By [Date]) [First],
    SUM(CASE WHEN [IsHoliday] = 0 THEN [DaywisePlan] ELSe 0 END) OVER(ORDER By [Date]) as [Sum]
FROM tab1)
SELECT [Date], [DaywisePlan], [IsHoliday]
,CASE WHEN [Sum] = 0 AND [IsHoliday] = 1 THEN [Sum]  [first] ELSe [Sum] END as [Sum] FROM CTE

Date                    | DaywisePlan | IsHoliday | Sum
:---------------------- | ----------: | --------: | --:
2022-07-01 02:00:00.000 |          34 |         1 |  34
2022-07-02 02:00:00.000 |          34 |         1 |  34
2022-07-03 02:00:00.000 |          34 |         0 |  34
2022-07-04 02:00:00.000 |          34 |         0 |  68
2022-07-05 02:00:00.000 |          34 |         0 | 102
2022-07-06 02:00:00.000 |          34 |         0 | 136
2022-07-07 02:00:00.000 |          34 |         1 | 136
2022-07-08 02:00:00.000 |          34 |         1 | 136
2022-07-09 02:00:00.000 |          34 |         0 | 170
2022-07-10 02:00:00.000 |          34 |         0 | 204
2022-07-11 02:00:00.000 |          34 |         1 | 204
2022-07-12 02:00:00.000 |          34 |         0 | 238

db<>fiddle here

CodePudding user response：

In the comments I proposed logic based on the first version of your expected results.

The expected results in the question as currently posed do not match that logic. Instead they seem to match this logic:

Do not accumulate DaywisePlan until arriving at the first row in order of date ascending where IsHolday equals zero. For that row and all subsequent rows, if IsHolday equals zero, accumulate DaywisePlan into the total.

You have also used an ambiguous date format which I infer (given the nature of your question) to be 'month/day/year', but could also be valid 'day/month/year' values. Here it happens to be the caes that the interpretation makes no difference to the ordering, but you should make it a habit of using non-ambiguous date formats like 'yyyyMMdd'.

In any case, here is a query which will produce the original expected results, and another query which will produce the new expected results. I have used similar CTE's for both to make the logic (and the difference between them) a little easier to read.

create table #mytable
(
   [Date]         date primary key,
   DaywisePlan    int,
   IsHoliday      bit,
   ExpectedOutput int
);

set dateformat mdy;

-- original dataset

insert #mytable values
('7/1/2022',   34,   1,  34 ),
('7/2/2022',   34,   1,  34 ),
('7/3/2022',   34,   0,  68 ),
('7/4/2022',   34,   0,  102 ),
('7/5/2022',   34,   0,  136 ),
('7/6/2022',   34,   0,  170 ),
('7/7/2022',   34,   1,  170 ),
('7/8/2022',   34,   1,  170 ),
('7/9/2022',   34,   0,  204 ),
('7/10/2022',  34,   0,  238 ),
('7/11/2022',  34,   1,  238 ),
('7/12/2022',  34,   0,  272 );

-- logic producing original dataset

with working as
(
   select      [date],
               DaywisePlan,
               IsHoliday,
               ExpectedOutput,
               FullAccum      =  sum(DayWisePlan) 
                                 over (order by [date] rows unbounded preceding),
               HoldayAccum    =  sum
                                 (
                                    iif(isHoliday = 1 and [date] != t.mindate, DayWisePlan, 0)
                                 ) over (order by [date] rows unbounded preceding)
   from        #mytable
   cross join (select min([date]) from #myTable) t(mindate)
)
select   [date], 
         daywiseplan, 
         isholiday, 
         expectedoutput,  
         CalculatedOutput = FullAccum - HoldayAccum
from     working;

-- edited dataset

delete from #mytable;

insert #mytable values
('7/1/2022',   34,   1,   34  ),
('7/2/2022',   34,   1,   34  ),
('7/3/2022',   34,   0,   34  ),
('7/4/2022',   34,   0,   68  ),
('7/5/2022',   34,   0,   102 ),
('7/6/2022',   34,   0,   136 ),
('7/7/2022',   34,   1,   136 ),
('7/8/2022',   34,   1,   136 ),
('7/9/2022',   34,   0,   170 ),
('7/10/2022',  34,   0,   204 ),
('7/11/2022',  34,   1,   204 ),
('7/12/2022',  34,   0,   238 );

-- logic to produce edited dataset
with working as
(
   select      [date],
               DaywisePlan,
               IsHoliday,
               ExpectedOutput,
               firstNonHoliday   = (select min([date]) from #myTable where IsHoliday = 0),
               FullAccum         =  sum(DayWisePlan) 
                                    over (order by [date] rows unbounded preceding),
               HoldayAccum       =  sum
                                    (
                                       iif(isHoliday = 1, DayWisePlan, 0)
                                    ) over (order by [date] rows unbounded preceding)
   from        #mytable
)
select   [date], 
         daywiseplan, 
         isholiday, 
         expectedoutput,  
         CalculatedOutput = iif([date] < firstNonHoliday, daywiseplan, FullAccum - HoldayAccum)
from     working;

If you just mean to say "ignore any holidays after the first non-holiday" then the logic can be significantly simplified (keeping the CTE for comparative purposes):

with working as
(
   select      [date],
               DaywisePlan,
               IsHoliday,
               ExpectedOutput,
               firstNonHoliday   = (select min([date]) from #myTable where IsHoliday = 0),
               FullAccum         = sum(iif(isHoliday = 0, DayWisePlan, 0)) 
                                   over(order by date rows unbounded preceding)
   from        #mytable  
   
)
select   [date], 
         daywiseplan, 
         isholiday, 
         expectedoutput,  
         CalculatedOutput = iif([date] <= firstNonHoliday, dayWisePlan, fullaccum)
from     working;