none
ReOrder level report ( Days Calculation )

    Question

  • Hi,

    I need Reorder level report ( Days Calculation )

    If balance quantity  Negative then not calculate between days

    If type ='STKADJ'  then and balance quantity =0 then not calculate

    finally result 167 days

    pls help.

    I want result like below image.

    DECLARE @FromDate Date='2018-01-01'
    DECLARE @ToDate Date='2019-01-11'

    create table #temp (_Type VARCHAR(20),_Date date,Qty int,BalQty INT)
    insert into #temp (_Type,_Date,Qty,BalQty) values ('IN','2018-01-10',10,10),('OUT','2018-01-15',8,2),('OUT','2018-02-12',2,0),
    ('OUT','2018-03-13',2,-2),('OUT','2018-04-04',3,-5)
    ,('IN','2018-05-24',10,5),('IN','2018-05-28',6,11),('STKADJ','2018-05-28',11,0),('OUT','2018-08-29',6,-6),('IN','2018-09-15',10,4)

    select * from #temp order by _Date,_Type

    drop table #temp


    • Edited by Farook M Friday, January 11, 2019 7:17 AM
    Friday, January 11, 2019 7:17 AM

Answers

  • Hi Farook M,

    Please try the following statement and see if it works.

    create table #temp (_Type VARCHAR(20),_Date date,Qty int,BalQty INT)
    insert into #temp (_Type,_Date,Qty,BalQty) values 
    ('IN','2018-01-01',10,10),
    ('OUT','2018-01-15',8,2),
    ('OUT','2018-02-12',2,0),
    ('OUT','2018-03-13',2,-2),
    ('OUT','2018-04-04',3,-5),
    ('IN','2018-05-24',10,5),
    ('IN','2018-05-28',6,11),
    ('STKADJ','2018-05-28',11,0),
    ('OUT','2018-08-29',6,-6),
    ('IN','2018-09-15',10,4)
    
    --Query
     DECLARE @FromDate Date='2018-01-01'
     DECLARE @ToDate Date='2019-01-11'
    
    ;WITH CTE AS
    (
    SELECT 
    _Type,
    _Date,
    Qty,
    BalQty,
    LEAD(_Date) OVER (PARTITION BY _Type ORDER BY _Date) AS Next_Date,
    LAG(_Type) OVER (ORDER BY _Date) AS LAG_Type
    from #temp
    ),
    cte2 as
    (
    select 
    T._Type,
    T._Date,
    T.Qty,
    T.BalQty,
    DATEDIFF(DAY,T._Date,CASE T._Type WHEN 'IN' THEN  
                              CASE WHEN T.LAG_Type='IN' THEN NULL  
    					      ELSE  ISNULL(T1._Date,@ToDate)  END 
    			           ELSE NULL END)+1 AS [DateDiff]
    from CTE T
    OUTER APPLY (SELECT TOP 1 _Date FROM #temp WHERE _Date>T._Date AND _Date<=T.Next_Date AND BalQty>=0 ORDER BY CASE _Type WHEN 'IN' THEN 2 ELSE 1 END, _Date DESC ) T1
    )
    select 
    s._Type,s._Date,s.Qty,s.BalQty,s.[DateDiff]
    from 
    (
    select 1 as levels,_Type,_Date,Qty,BalQty,[DateDiff] from cte2
    union all
    select 2,'Total',NULL,NULL,NULL,SUM([DateDiff]) from cte2  
    ) S
    order by levels,_Date
    --Output
    /*
    _Type                _Date      Qty         BalQty      DateDiff
    -------------------- ---------- ----------- ----------- -----------
    IN                   2018-01-01 10          10          43
    OUT                  2018-01-15 8           2           NULL
    OUT                  2018-02-12 2           0           NULL
    OUT                  2018-03-13 2           -2          NULL
    OUT                  2018-04-04 3           -5          NULL
    IN                   2018-05-24 10          5           5
    IN                   2018-05-28 6           11          NULL
    STKADJ               2018-05-28 11          0           NULL
    OUT                  2018-08-29 6           -6          NULL
    IN                   2018-09-15 10          4           119
    Total                NULL       NULL        NULL        167
    */
    
    drop table #temp

    Best Regards,

    Will


    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.

    • Marked as answer by Farook M Friday, January 11, 2019 9:17 AM
    Friday, January 11, 2019 9:08 AM
    Moderator
  • Hi,

    Thanks above query working fine.

    But i am using SQL2008 LEAD and LAG  syntax not working

    Please help.

    OK, please try

    create table #temp (_Type VARCHAR(20),_Date date,Qty int,BalQty INT)
    insert into #temp (_Type,_Date,Qty,BalQty) values 
    ('IN','2018-01-01',10,10),
    ('OUT','2018-01-15',8,2),
    ('OUT','2018-02-12',2,0),
    ('OUT','2018-03-13',2,-2),
    ('OUT','2018-04-04',3,-5),
    ('IN','2018-05-24',10,5),
    ('IN','2018-05-28',6,11),
    ('STKADJ','2018-05-28',11,0),
    ('OUT','2018-08-29',6,-6),
    ('IN','2018-09-15',10,4)
    
    --Query
     DECLARE @FromDate Date='2018-01-01'
     DECLARE @ToDate Date='2019-01-11'
    
    ;WITH CTE_ROW AS
    (
    SELECT 
    _Type,
    _Date,
    Qty,
    BalQty,
    ROW_NUMBER() OVER (PARTITION BY _Type ORDER BY _Date) AS RN1,
    ROW_NUMBER() OVER (ORDER BY _Date) AS RN2
    from #temp
    ),
    CTE AS
    (
    SELECT 
    T._Type,
    T._Date,
    T.Qty,
    T.BalQty,
    T1._Date AS Next_Date,
    T2._Type as LAG_Type
    from CTE_ROW T
    LEFT JOIN CTE_ROW T1 ON T._Type=T1._Type AND T.RN1+1=T1.RN1
    LEFT JOIN CTE_ROW T2 ON T.RN2-1=T2.RN2
    ),
    cte2 as
    (
    select 
    T._Type,
    T._Date,
    T.Qty,
    T.BalQty,
    DATEDIFF(DAY,T._Date,CASE T._Type WHEN 'IN' THEN  
                              CASE WHEN T.LAG_Type='IN' THEN NULL  
    					      ELSE  ISNULL(T1._Date,@ToDate)  END 
    			           ELSE NULL END)+1 AS [DateDiff]
    from CTE T
    OUTER APPLY (SELECT TOP 1 _Date FROM #temp WHERE _Date>T._Date AND _Date<=T.Next_Date AND BalQty>=0 ORDER BY CASE _Type WHEN 'IN' THEN 2 ELSE 1 END, _Date DESC ) T1
    )
    select 
    s._Type,s._Date,s.Qty,s.BalQty,s.[DateDiff]
    from 
    (
    select 1 as levels,_Type,_Date,Qty,BalQty,[DateDiff] from cte2
    union all
    select 2,'Total',NULL,NULL,NULL,SUM([DateDiff]) from cte2  
    ) S
    order by levels,_Date
    --Output
    /*
    _Type                _Date      Qty         BalQty      DateDiff
    -------------------- ---------- ----------- ----------- -----------
    IN                   2018-01-01 10          10          43
    OUT                  2018-01-15 8           2           NULL
    OUT                  2018-02-12 2           0           NULL
    OUT                  2018-03-13 2           -2          NULL
    OUT                  2018-04-04 3           -5          NULL
    IN                   2018-05-24 10          5           5
    IN                   2018-05-28 6           11          NULL
    STKADJ               2018-05-28 11          0           NULL
    OUT                  2018-08-29 6           -6          NULL
    IN                   2018-09-15 10          4           119
    Total                NULL       NULL        NULL        167
    */
    
    drop table #temp

    Best Regards,

    Will


    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.

    • Marked as answer by Farook M Friday, January 11, 2019 9:40 AM
    Friday, January 11, 2019 9:37 AM
    Moderator
  • DECLARE @FromDate Date='2018-01-01'
    DECLARE @ToDate Date='2019-01-11'
    
    create table #temp (_Type VARCHAR(20),_Date date,Qty int,BalQty INT)
    insert into #temp (_Type,_Date,Qty,BalQty) values 
    ('IN','2018-01-01',10,10) --modified date
    ,('OUT','2018-01-15',8,2)
    ,('OUT','2018-02-12',2,0),
    ('OUT','2018-03-13',2,-2)
    ,('OUT','2018-04-04',3,-5)
    ,('IN','2018-05-24',10,5)
    ,('IN','2018-05-28',6,11)
    ,('STKADJ','2018-05-28',11,0)
    ,('OUT','2018-08-29',6,-6)
    ,('IN','2018-09-15',10,4)
    
    
    ;with mycte as (
    select * , Case when  BalQty<0 or (_Type='STKADJ' and  BalQty=0 )   then 1 else 0  end  chk
    
    ,row_number() Over(order by _Date)-row_number() Over(partition by Case when  BalQty<0 or (_Type='STKADJ' and  BalQty=0 ) then 1 else 0 end  order by _Date)   grp
    
    from (
    Select  _Type,_Date,Qty,BalQty   from #temp
    Union all
    Select '',@ToDate as _Date, 0 Qty,0 BalQty ) t
    
    
    )
    
    ,mycte2 as (
    Select *, row_number() Over(Partition by  chk,grp Order by  _Date) rn
    ,Case when row_number() Over(Partition by  chk,grp Order by  _Date)= 1 and chk=0 then datediff(day,min(_Date) Over(Partition by  chk,grp ) , max(_Date) Over(Partition by  chk,grp  )) +1 else 0 end diff from mycte
     )
    
     select  _Type,_Date,Qty,BalQty, Sum(diff) dtdiff from mycte2
     GROUP BY
    	GROUPING SETS
    	(
    		  ((_Type),(_Date),(Qty),(BalQty) ),
    		()  
    	) 
     Order by ISNULL(_Date, Dateadd(day,1,@ToDate)) 
    
    drop table #temp
    
    /*
    _Type	_Date	Qty	BalQty	dtdiff
    IN	2018-01-01	10	10	43
    OUT	2018-01-15	8	2	0
    OUT	2018-02-12	2	0	0
    OUT	2018-03-13	2	-2	0
    OUT	2018-04-04	3	-5	0
    IN	2018-05-24	10	5	5
    IN	2018-05-28	6	11	0
    STKADJ	2018-05-28	11	0	0
    OUT	2018-08-29	6	-6	0
    IN	2018-09-15	10	4	119
    	2019-01-11	0	0	0
    NULL	NULL	NULL	NULL	167
    */

    • Marked as answer by Farook M Saturday, January 12, 2019 10:13 AM
    Friday, January 11, 2019 8:40 PM
    Moderator

All replies

  • Hi Farook M,

    Please try the following statement and see if it works.

    create table #temp (_Type VARCHAR(20),_Date date,Qty int,BalQty INT)
    insert into #temp (_Type,_Date,Qty,BalQty) values 
    ('IN','2018-01-01',10,10),
    ('OUT','2018-01-15',8,2),
    ('OUT','2018-02-12',2,0),
    ('OUT','2018-03-13',2,-2),
    ('OUT','2018-04-04',3,-5),
    ('IN','2018-05-24',10,5),
    ('IN','2018-05-28',6,11),
    ('STKADJ','2018-05-28',11,0),
    ('OUT','2018-08-29',6,-6),
    ('IN','2018-09-15',10,4)
    
    --Query
     DECLARE @FromDate Date='2018-01-01'
     DECLARE @ToDate Date='2019-01-11'
    
    ;WITH CTE AS
    (
    SELECT 
    _Type,
    _Date,
    Qty,
    BalQty,
    LEAD(_Date) OVER (PARTITION BY _Type ORDER BY _Date) AS Next_Date,
    LAG(_Type) OVER (ORDER BY _Date) AS LAG_Type
    from #temp
    ),
    cte2 as
    (
    select 
    T._Type,
    T._Date,
    T.Qty,
    T.BalQty,
    DATEDIFF(DAY,T._Date,CASE T._Type WHEN 'IN' THEN  
                              CASE WHEN T.LAG_Type='IN' THEN NULL  
    					      ELSE  ISNULL(T1._Date,@ToDate)  END 
    			           ELSE NULL END)+1 AS [DateDiff]
    from CTE T
    OUTER APPLY (SELECT TOP 1 _Date FROM #temp WHERE _Date>T._Date AND _Date<=T.Next_Date AND BalQty>=0 ORDER BY CASE _Type WHEN 'IN' THEN 2 ELSE 1 END, _Date DESC ) T1
    )
    select 
    s._Type,s._Date,s.Qty,s.BalQty,s.[DateDiff]
    from 
    (
    select 1 as levels,_Type,_Date,Qty,BalQty,[DateDiff] from cte2
    union all
    select 2,'Total',NULL,NULL,NULL,SUM([DateDiff]) from cte2  
    ) S
    order by levels,_Date
    --Output
    /*
    _Type                _Date      Qty         BalQty      DateDiff
    -------------------- ---------- ----------- ----------- -----------
    IN                   2018-01-01 10          10          43
    OUT                  2018-01-15 8           2           NULL
    OUT                  2018-02-12 2           0           NULL
    OUT                  2018-03-13 2           -2          NULL
    OUT                  2018-04-04 3           -5          NULL
    IN                   2018-05-24 10          5           5
    IN                   2018-05-28 6           11          NULL
    STKADJ               2018-05-28 11          0           NULL
    OUT                  2018-08-29 6           -6          NULL
    IN                   2018-09-15 10          4           119
    Total                NULL       NULL        NULL        167
    */
    
    drop table #temp

    Best Regards,

    Will


    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.

    • Marked as answer by Farook M Friday, January 11, 2019 9:17 AM
    Friday, January 11, 2019 9:08 AM
    Moderator
  • Hi,

    Thanks above query working fine.

    But i am using SQL2008 LEAD and LAG  syntax not working

    Please help.

    Friday, January 11, 2019 9:23 AM
  • Hi,

    Thanks above query working fine.

    But i am using SQL2008 LEAD and LAG  syntax not working

    Please help.

    OK, please try

    create table #temp (_Type VARCHAR(20),_Date date,Qty int,BalQty INT)
    insert into #temp (_Type,_Date,Qty,BalQty) values 
    ('IN','2018-01-01',10,10),
    ('OUT','2018-01-15',8,2),
    ('OUT','2018-02-12',2,0),
    ('OUT','2018-03-13',2,-2),
    ('OUT','2018-04-04',3,-5),
    ('IN','2018-05-24',10,5),
    ('IN','2018-05-28',6,11),
    ('STKADJ','2018-05-28',11,0),
    ('OUT','2018-08-29',6,-6),
    ('IN','2018-09-15',10,4)
    
    --Query
     DECLARE @FromDate Date='2018-01-01'
     DECLARE @ToDate Date='2019-01-11'
    
    ;WITH CTE_ROW AS
    (
    SELECT 
    _Type,
    _Date,
    Qty,
    BalQty,
    ROW_NUMBER() OVER (PARTITION BY _Type ORDER BY _Date) AS RN1,
    ROW_NUMBER() OVER (ORDER BY _Date) AS RN2
    from #temp
    ),
    CTE AS
    (
    SELECT 
    T._Type,
    T._Date,
    T.Qty,
    T.BalQty,
    T1._Date AS Next_Date,
    T2._Type as LAG_Type
    from CTE_ROW T
    LEFT JOIN CTE_ROW T1 ON T._Type=T1._Type AND T.RN1+1=T1.RN1
    LEFT JOIN CTE_ROW T2 ON T.RN2-1=T2.RN2
    ),
    cte2 as
    (
    select 
    T._Type,
    T._Date,
    T.Qty,
    T.BalQty,
    DATEDIFF(DAY,T._Date,CASE T._Type WHEN 'IN' THEN  
                              CASE WHEN T.LAG_Type='IN' THEN NULL  
    					      ELSE  ISNULL(T1._Date,@ToDate)  END 
    			           ELSE NULL END)+1 AS [DateDiff]
    from CTE T
    OUTER APPLY (SELECT TOP 1 _Date FROM #temp WHERE _Date>T._Date AND _Date<=T.Next_Date AND BalQty>=0 ORDER BY CASE _Type WHEN 'IN' THEN 2 ELSE 1 END, _Date DESC ) T1
    )
    select 
    s._Type,s._Date,s.Qty,s.BalQty,s.[DateDiff]
    from 
    (
    select 1 as levels,_Type,_Date,Qty,BalQty,[DateDiff] from cte2
    union all
    select 2,'Total',NULL,NULL,NULL,SUM([DateDiff]) from cte2  
    ) S
    order by levels,_Date
    --Output
    /*
    _Type                _Date      Qty         BalQty      DateDiff
    -------------------- ---------- ----------- ----------- -----------
    IN                   2018-01-01 10          10          43
    OUT                  2018-01-15 8           2           NULL
    OUT                  2018-02-12 2           0           NULL
    OUT                  2018-03-13 2           -2          NULL
    OUT                  2018-04-04 3           -5          NULL
    IN                   2018-05-24 10          5           5
    IN                   2018-05-28 6           11          NULL
    STKADJ               2018-05-28 11          0           NULL
    OUT                  2018-08-29 6           -6          NULL
    IN                   2018-09-15 10          4           119
    Total                NULL       NULL        NULL        167
    */
    
    drop table #temp

    Best Regards,

    Will


    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.

    • Marked as answer by Farook M Friday, January 11, 2019 9:40 AM
    Friday, January 11, 2019 9:37 AM
    Moderator
  • Good. working fine

    Thanks.

    Friday, January 11, 2019 9:40 AM
  • DECLARE @FromDate Date='2018-01-01'
    DECLARE @ToDate Date='2019-01-11'
    
    create table #temp (_Type VARCHAR(20),_Date date,Qty int,BalQty INT)
    insert into #temp (_Type,_Date,Qty,BalQty) values 
    ('IN','2018-01-01',10,10) --modified date
    ,('OUT','2018-01-15',8,2)
    ,('OUT','2018-02-12',2,0),
    ('OUT','2018-03-13',2,-2)
    ,('OUT','2018-04-04',3,-5)
    ,('IN','2018-05-24',10,5)
    ,('IN','2018-05-28',6,11)
    ,('STKADJ','2018-05-28',11,0)
    ,('OUT','2018-08-29',6,-6)
    ,('IN','2018-09-15',10,4)
    
    
    ;with mycte as (
    select * , Case when  BalQty<0 or (_Type='STKADJ' and  BalQty=0 )   then 1 else 0  end  chk
    
    ,row_number() Over(order by _Date)-row_number() Over(partition by Case when  BalQty<0 or (_Type='STKADJ' and  BalQty=0 ) then 1 else 0 end  order by _Date)   grp
    
    from (
    Select  _Type,_Date,Qty,BalQty   from #temp
    Union all
    Select '',@ToDate as _Date, 0 Qty,0 BalQty ) t
    
    
    )
    
    ,mycte2 as (
    Select *, row_number() Over(Partition by  chk,grp Order by  _Date) rn
    ,Case when row_number() Over(Partition by  chk,grp Order by  _Date)= 1 and chk=0 then datediff(day,min(_Date) Over(Partition by  chk,grp ) , max(_Date) Over(Partition by  chk,grp  )) +1 else 0 end diff from mycte
     )
    
     select  _Type,_Date,Qty,BalQty, Sum(diff) dtdiff from mycte2
     GROUP BY
    	GROUPING SETS
    	(
    		  ((_Type),(_Date),(Qty),(BalQty) ),
    		()  
    	) 
     Order by ISNULL(_Date, Dateadd(day,1,@ToDate)) 
    
    drop table #temp
    
    /*
    _Type	_Date	Qty	BalQty	dtdiff
    IN	2018-01-01	10	10	43
    OUT	2018-01-15	8	2	0
    OUT	2018-02-12	2	0	0
    OUT	2018-03-13	2	-2	0
    OUT	2018-04-04	3	-5	0
    IN	2018-05-24	10	5	5
    IN	2018-05-28	6	11	0
    STKADJ	2018-05-28	11	0	0
    OUT	2018-08-29	6	-6	0
    IN	2018-09-15	10	4	119
    	2019-01-11	0	0	0
    NULL	NULL	NULL	NULL	167
    */

    • Marked as answer by Farook M Saturday, January 12, 2019 10:13 AM
    Friday, January 11, 2019 8:40 PM
    Moderator