none
if Values is Null then previous date availble availble RRS feed

  • Question

  • Hi Team,

    If Amount column values is NULL i need previous Date values based ISID(same ISID) but continue NULL particular ISID then Zero

    my input values(here 03/04 date Amount Zero here display o5/ date value 700 but(ZA144 06 and 07 date shoud be zero because same ISID last date value didnt availble )

    ISID DATE Amount
    ZA132 01/08/2019  1000
    ZA132 02/08/2019  2000
    ZA132 03/08/2019  NULL
    ZA132 04/08/2019  NULL
    ZA132 05/08/2019  700
    ZA133 01/08/2019  null
    ZA133 02/08/2019  400
    ZA133 03/08/2019  600
    ZA133 04/08/2019  400
    ZA133 05/08/2019  5000
    ZA144 05/08/2019  700
    ZA144 06/08/2019  NULL
    ZA144 07/08/2019  NULL

    Expected Out put:

    ISID DATE Amount
    ZA132 01/08/2019   1000
    ZA132 02/08/2019   2000
    ZA132 03/08/2019   700
    ZA132 04/08/2019   700
    ZA132 05/08/2019   700
    ZA133 01/08/2019  400
    ZA133 02/08/2019  400
    ZA133 03/08/2019  600
    ZA133 04/08/2019  400
    ZA133 05/08/2019  5000
    ZA144 05/08/2019  700
    ZA144 06/08/2019 0
    ZA144 07/08/2019 0

    Thanks

    Shanvitha

    Thursday, August 8, 2019 12:58 PM

Answers

  • CREATE TABLE mytable(
       ISID   VARCHAR(5) NOT NULL  
      ,DATE   DATE  NOT NULL
      ,Amount INTEGER 
    );
    INSERT INTO mytable(ISID,DATE,Amount) VALUES
     ('ZA132','01/08/2019',1000)
    ,('ZA132','02/08/2019',2000)
    ,('ZA132','03/08/2019',NULL)
    ,('ZA132','04/08/2019',NULL)
    ,('ZA132','05/08/2019',700)
    ,('ZA133','01/08/2019',NULL)
    ,('ZA133','02/08/2019',400)
    ,('ZA133','03/08/2019',600)
    ,('ZA133','04/08/2019',400)
    ,('ZA133','05/08/2019',5000)
    ,('ZA144','05/08/2019',700)
    ,('ZA144','06/08/2019',NULL)
    ,('ZA144','07/08/2019',NULL);
    
     ;with mycte as 
     (
     Select ISID,Date, Amount 
     ,row_number() Over(partition by ISID order by  Date desc) rn 
     from mytable m
     )
    
    Select ISID,Date,ISNULL(d.Amount,0) Amount from mycte m
    outer apply (select top 1 Amount from mycte m2 
    WHERE m.ISID=m2.ISID and m2.rn<=m.rn and Amount is not null  
    Order by Date ) d(Amount) 
    order by ISID,Date  
     
    
    
    DROP TABLE mytable;

    Thursday, August 8, 2019 1:41 PM
    Moderator
  • Expected Out put:
    ISID DATE Amount
    ZA132 01/08/2019   1000
    ZA132 02/08/2019   2000
    ZA132 03/08/2019   700
    ZA132 04/08/2019   700
    ZA132 05/08/2019   700
    ZA133 01/08/2019  400
    ZA133 02/08/2019  400
    ZA133 03/08/2019  600
    ZA133 04/08/2019  400
    ZA133 05/08/2019  5000
    ZA144 05/08/2019  700
    ZA144 06/08/2019 0
    ZA144 07/08/2019 0

    Try

    -- code #1
    ;with 
    tbIS_seq as (
    SELECT ISID, [DATE], [Amount],
           seq= row_number() over (partition by ISID order by [DATE] desc)
      from tbIS  
    ),
    cte_tbIS as (
    SELECT ISID, [DATE], coalesce (Amount, 0) as Amount, seq
      from tbIS_seq
      where seq = 1
    union all
    SELECT T2.ISID, T2.[DATE], coalesce (T2.Amount, T1.Amount), T2.seq
      from cte_tbIS as T1
           inner join tbIS_seq as T2 on T2.ISID = T1.ISID
                                        and T2.seq = (T1.seq +1)
    )
    SELECT ISID, [DATE], Amount
      from cte_tbIS
      order by ISID, [DATE];                                    

    ---

     

    If this answer was helpful, remember to mark it.


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    Thursday, August 8, 2019 1:56 PM
  • Hi ArunaMSBI,

     

    Also , please try following script .

     
    ;with cte as(
    select *
    from (select *, 
    lag(Amount)over(partition by ISID order by DATE) lag_value,
    row_number()over(partition by ISID order by DATE) rn
    from mytable ) t
    where Amount is not null and lag_value is null and rn<>1
    ),cte1 as (
    select a.ISID,a.DATE,
    case when a.Amount is null then case when b.Amount is null then 0 else b.Amount end  else a.Amount end Amount,
    row_number()over(partition by a.ISID,a.DATE order by b.DATE) rn 
    from mytable a 
    left join cte b on a.ISID=b.ISID and a.DATE<b.DATE)
    select ISID,DATE,Amount from cte1 where rn=1
    /*
    ISID  DATE       Amount
    ----- ---------- -----------
    ZA132 2019-01-08 1000
    ZA132 2019-02-08 2000
    ZA132 2019-03-08 700
    ZA132 2019-04-08 700
    ZA132 2019-05-08 700
    ZA133 2019-01-08 400
    ZA133 2019-02-08 400
    ZA133 2019-03-08 600
    ZA133 2019-04-08 400
    ZA133 2019-05-08 5000
    ZA144 2019-05-08 700
    ZA144 2019-06-08 0
    ZA144 2019-07-08 0
    */
    

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 9, 2019 6:41 AM

All replies

  • Why the amounts are 0, not 700:

    ZA144 06/08/2019     0
    ZA144 07/08/2019     0


    A Fan of SSIS, SSRS and SSAS

    Thursday, August 8, 2019 1:27 PM
  • CREATE TABLE mytable(
       ISID   VARCHAR(5) NOT NULL  
      ,DATE   DATE  NOT NULL
      ,Amount INTEGER 
    );
    INSERT INTO mytable(ISID,DATE,Amount) VALUES
     ('ZA132','01/08/2019',1000)
    ,('ZA132','02/08/2019',2000)
    ,('ZA132','03/08/2019',NULL)
    ,('ZA132','04/08/2019',NULL)
    ,('ZA132','05/08/2019',700)
    ,('ZA133','01/08/2019',NULL)
    ,('ZA133','02/08/2019',400)
    ,('ZA133','03/08/2019',600)
    ,('ZA133','04/08/2019',400)
    ,('ZA133','05/08/2019',5000)
    ,('ZA144','05/08/2019',700)
    ,('ZA144','06/08/2019',NULL)
    ,('ZA144','07/08/2019',NULL);
    
     ;with mycte as 
     (
     Select ISID,Date, Amount 
     ,row_number() Over(partition by ISID order by  Date desc) rn 
     from mytable m
     )
    
    Select ISID,Date,ISNULL(d.Amount,0) Amount from mycte m
    outer apply (select top 1 Amount from mycte m2 
    WHERE m.ISID=m2.ISID and m2.rn<=m.rn and Amount is not null  
    Order by Date ) d(Amount) 
    order by ISID,Date  
     
    
    
    DROP TABLE mytable;

    Thursday, August 8, 2019 1:41 PM
    Moderator
  • Expected Out put:
    ISID DATE Amount
    ZA132 01/08/2019   1000
    ZA132 02/08/2019   2000
    ZA132 03/08/2019   700
    ZA132 04/08/2019   700
    ZA132 05/08/2019   700
    ZA133 01/08/2019  400
    ZA133 02/08/2019  400
    ZA133 03/08/2019  600
    ZA133 04/08/2019  400
    ZA133 05/08/2019  5000
    ZA144 05/08/2019  700
    ZA144 06/08/2019 0
    ZA144 07/08/2019 0

    Try

    -- code #1
    ;with 
    tbIS_seq as (
    SELECT ISID, [DATE], [Amount],
           seq= row_number() over (partition by ISID order by [DATE] desc)
      from tbIS  
    ),
    cte_tbIS as (
    SELECT ISID, [DATE], coalesce (Amount, 0) as Amount, seq
      from tbIS_seq
      where seq = 1
    union all
    SELECT T2.ISID, T2.[DATE], coalesce (T2.Amount, T1.Amount), T2.seq
      from cte_tbIS as T1
           inner join tbIS_seq as T2 on T2.ISID = T1.ISID
                                        and T2.seq = (T1.seq +1)
    )
    SELECT ISID, [DATE], Amount
      from cte_tbIS
      order by ISID, [DATE];                                    

    ---

     

    If this answer was helpful, remember to mark it.


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    Thursday, August 8, 2019 1:56 PM
  • Hi ArunaMSBI

    I hope following query help you 

    SELECT ISID, 
           DATE, 
           ISNULL(amount, ISNULL(
    (
        SELECT TOP 1 amount
        FROM mytable
        WHERE a.ISID = ISID
              AND a.DATE < Date
              AND amount IS NOT NULL
    ), 0)) AS amount
    FROM mytable a;

    Best Regards,

    Natig


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. 

    Thursday, August 8, 2019 2:11 PM
  • In the future would you mind posting DDL, as required by basic netiquette? Next, you might want to learn the proper date format instead of using the most ambiguous one that you can find. You do know that the word DATE is reserved in SQL? Did you know that a table must have a key by definition?

    CREATE TABLE Foobars
    (isid CHAR(5) NOT NULL,
     foobar_date DATE NOT NULL,
    PRIMARY KEY (isid, foobar_date-- my guess without help from you
     something_amt INTEGER);

    INSERT INTO Foobars
    VALUES
    ('ZA132', '2019-08-01', 1000),
    ('ZA132', '2019-08-02', 2000),
    ('ZA132', '2019-03-08', NULL),
    ('ZA132', '2019-08-04', NULL),
    ('ZA132', '2019-08-05', 700),
    ('ZA133', '2019-08-01', NULL),
    ('ZA133', '2019-08-02', 400),
    ('ZA133', '2019-08-03', 600),
    ('ZA133', '2019-08-04', 400),
    ('ZA133', '2019-08-05', 500),
    ('ZA144', '2019-08-05', 700),
    ('ZA144', '2019-08-06', NULL),
    ('ZA144', '2019-08-07', NULL);

    You have several choices here. The best way to do it would be to make the foobar amount NOT NULL and insert it every time you put a row in. Another way would be to construct a view that has the computation. Would be to update the column after you've loaded it with NULLs. 

    SELECT isid, foobar_date
     MAX(something_amt)
     OVER (PARTITION BY isid
                 ORDER BY foobar_date 
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS best_something_amt
    FROM Foobars;



    ISID DATE Amount
    ('ZA132 01', 1000
    twice ('ZA132 02', 2000
    ('ZA132 03', 700
    ('ZA132 04', 700
    ('ZA132 05', 700
    ('ZA133 01', 400
    ('ZA133 02', 400
    ('ZA133 03-08-2019 600
    ('ZA133 04', 400
    ('ZA133 05', 5000
    ZA144 05', 700
    ZA144 06', 0
    ZA144 07', 0

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, August 8, 2019 7:56 PM
  • Hi ArunaMSBI,

     

    Also , please try following script .

     
    ;with cte as(
    select *
    from (select *, 
    lag(Amount)over(partition by ISID order by DATE) lag_value,
    row_number()over(partition by ISID order by DATE) rn
    from mytable ) t
    where Amount is not null and lag_value is null and rn<>1
    ),cte1 as (
    select a.ISID,a.DATE,
    case when a.Amount is null then case when b.Amount is null then 0 else b.Amount end  else a.Amount end Amount,
    row_number()over(partition by a.ISID,a.DATE order by b.DATE) rn 
    from mytable a 
    left join cte b on a.ISID=b.ISID and a.DATE<b.DATE)
    select ISID,DATE,Amount from cte1 where rn=1
    /*
    ISID  DATE       Amount
    ----- ---------- -----------
    ZA132 2019-01-08 1000
    ZA132 2019-02-08 2000
    ZA132 2019-03-08 700
    ZA132 2019-04-08 700
    ZA132 2019-05-08 700
    ZA133 2019-01-08 400
    ZA133 2019-02-08 400
    ZA133 2019-03-08 600
    ZA133 2019-04-08 400
    ZA133 2019-05-08 5000
    ZA144 2019-05-08 700
    ZA144 2019-06-08 0
    ZA144 2019-07-08 0
    */
    

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 9, 2019 6:41 AM
  • Hi Shanvitha,

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 14, 2019 9:34 AM