How to calculate this situation SQL?

Question

I need to help query situation for calculate situation I don't know how to query situation.

Please any help me for continue this project and you can guide me and example for me

This is a sample of the data I have:

Date       Usage  Plan
------------------------
2020-04-30 NULL   94928
2020-05-01 NULL   NULL
2020-05-02 NULL   NULL
2020-05-03 12269  NULL
2020-05-04 3253   NULL
2020-05-05 NULL   NULL
2020-05-06 NULL   NULL
2020-05-07 NULL   NULL
2020-05-08 500    1000
2020-05-09 NULL   NULL
2020-05-10 NULL   NULL
2020-05-11 NULL   NULL

Desired output looks like this:

Date       Usage  Plan
-----------------------
2020-04-30 NULL   94928
2020-05-01 NULL   94928
2020-05-02 NULL   94928
2020-05-03 12269  82659
2020-05-04 3253   79406
2020-05-05 NULL   79406
2020-05-06 NULL   79406
2020-05-07 NULL   79406
2020-05-08 500    1000
2020-05-09 NULL   500
2020-05-10 NULL   500
2020-05-11 NULL   500

Create table script for the sample data:

CREATE TABLE [dbo].[Table_1]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Date] [date] NULL,
    [ITEM_NUMBER] [varchar](max) NULL,
    [Plan_Matusage] [int] NULL,
    [St_plan] [int] NULL,
    [St_revise] [int] NULL,
    [St_actual] [int] NULL,
)

What condition causes plan to get the value `79406`? – vvvv4d Aug 22 '20 at 06:35 — vvvv4d, Aug 22 '20 at 06:35
@vvvv4d condition this plan - Usage – New Claffly Aug 22 '20 at 07:18 — New Claffly, Aug 22 '20 at 07:18

score 1 · Answer 1 · answered Aug 22 '20 at 07:37

You can use window functions. The idea is to build groups of adjacent records with a window sum that increments for every non null plan:

select 
    date,
    usage,
    coalesce(
        plan,
        max(plan) over(partition by grp)
            - coalesce(sum(usage) over(partition by grp order by date), 0)
   ) plan
from (
    select
        t.*,
        sum(case when plan is not null then 1 else 0 end) over(order by date) grp
    from mytable t
) t
order by date

SteveC · Answer 2 · 2020-08-22T13:20:26.187

0

This works

with plan_group_cte as (
    select  t.*, sum(iif([Plan] is not null, 1, 0)) over(order by [Date]) plan_group
    from dbo.Table_1 t)
select [Date], [Usage], 
       coalesce([Plan],  (max([Plan]) over (partition by plan_group)-
       sum(cast([Usage] as int)) over (partition by plan_group order by [Date]))) [Plan]
from plan_group_cte
order by 1;

edited Aug 22 '20 at 13:20

answered Aug 22 '20 at 13:08

SteveC

5,955
2
11
24

How to calculate this situation SQL?

2 Answers2