Lag function on derived column

1

Hi

I am planning to calculate the score which is defined as follows:
a child gets n number of pencil weekly and breaks 10 pencil each week .

I need to find how much shortage the child has faced in given year

child_1 pencil_in_hand breaks shortage
ABC 12 10 0 ( as he has 2 more pencil so there is no shortage)
ABC 3 10 -5 ( as previously he has 2 pencils and now he got 3 more so total 5-10)
ABC 7 10 -3 ( as previously he had nothing, this time shortage is -3)
ABC 16 10 0 ( no shortage and left with 6 more pencils)

I need to do this using postgres sql

Thanks

2

People in this forum are busy and will not have time to do this ...( create sample data)
we want to directly work on the SQL part ...

We can also help you understand how to write SQL ..

Please post sample data ...for your questions
like this ...

drop table #sampledata
go

create table #sampledata
(ts datetime2,
item varchar(20),
sellerid varchar(20)
)
go

insert into #sampledata
values
('2019-09-05 12:02:55.533','b123 ','dfjk'),
('2019-09-03 12:02:55.533','b123 ','TV12'),
('2019-09-03 12:02:55.533','b123 ','uiop')
go

1 Like
3

is this homework of some sort or real life?
what have you tried so far?

4

create table test1
(
week int,
child_1 varchar(50) ,
pencil_in_hand int ,
breaks int ,
);

insert into test1
values
(
(1,'ABC' , 12,10 ),
(2,'ABC' , 3,10 ),
(3,'ABC' , 7,10 ),
(4,'ABC' , 16,10 ),
)

---- I have tried the following.

select * , case when sum( pencil_in_hand - breaks) over(partition by child_1 order by week) <0 then sum( pencil_in_hand - breaks) over(partition by child_1 order by week) else 0 end as shortage
from test1 ;

However, it is not acccounting for the previously exceeding pencils.

This is a real life problem

5

hi

i tried to do this ... hope it helps :slight_smile: :slight_smile:
i love any feedback

if i am missing anything please point it out

i did it using recursive CTE ..
results are coming

i am sure there is ...direct way of doing this .. Scott Pletcher ( or any experts ).. please help

drop create data ... 
 drop table #test1 
 go 



create table #test1
(
week int,
child_1 varchar(50) ,
pencil_in_hand int ,
breaks int ,
)
go 

insert into #test1
values
(1,'ABC' , 12,10 ),
(2,'ABC' , 3,10 ),
(3,'ABC' , 7,10 ),
(4,'ABC' , 16,10 )
go 


select * from #test1 
go
SQL ... 
; WITH cte 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY (SELECT NULL)) AS rn, 
                week, 
                child_1, 
                pencil_in_hand, 
                breaks 
         FROM   #test1), 
     rec_cte 
     AS (SELECT *, 
                pencil_in_hand - breaks AS shortage 
         FROM   cte 
         WHERE  rn = 1 
         UNION ALL 
         SELECT b.*, 
                ( CASE 
                    WHEN a.shortage > 0 THEN a.shortage 
                    ELSE 0 
                  END ) + ( b.pencil_in_hand - b.breaks ) AS shortage 
         FROM   rec_cte a 
                JOIN cte b 
                  ON a.rn + 1 = b.rn) 
SELECT * 
FROM   rec_cte 

go

image

6

can we do it without recursive ? I am using Redshift and it doesnt supports that function

7

Recursion is very likely not the most efficient way to do this, but it's (relatively) easy to code up. I adjusted the data so that a positive "shortage" would occur two weeks in a row to make sure the code would keep the running total and apply it correctly. The actual code first (comment out the "overage" column when you're done with your testing), then the adjusted/enhanced data:

;WITH cte_child_shortage AS (
    SELECT child_1, week, pencil_in_hand, breaks,
        CASE WHEN pencil_in_hand - breaks > 0 THEN 0 ELSE pencil_in_hand - breaks END AS shortage,
        CASE WHEN pencil_in_hand - breaks > 0 THEN pencil_in_hand - breaks ELSE 0 END AS overage
    FROM (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY child_1 ORDER BY week) AS row_num
        FROM dbo.test1
    ) AS derived
    WHERE row_num = 1
    UNION ALL
    SELECT t1.child_1, t1.week, t1.pencil_in_hand, t1.breaks,
        calc1.shortage,
        calc1.overage
    FROM cte_child_shortage ccs
    INNER JOIN dbo.test1 t1 ON t1.child_1 = ccs.child_1 AND t1.week = ccs.week + 1
    CROSS APPLY (
        SELECT CASE WHEN t1.pencil_in_hand - t1.breaks + ccs.overage > 0 THEN 0
            ELSE t1.pencil_in_hand - t1.breaks + ccs.overage END AS shortage,
            CASE WHEN t1.pencil_in_hand - t1.breaks + ccs.overage > 0 
                 THEN t1.pencil_in_hand - t1.breaks + ccs.overage ELSE 0 END AS overage
    ) AS calc1
)
SELECT ccs.child_1, ccs.week, ccs.pencil_in_hand, ccs.breaks, ccs.shortage
    , ccs.overage
FROM cte_child_shortage ccs
ORDER BY child_1, week

truncate table test1;
insert into test1
values
(1,'ABC' , 12,10 ),
(2,'ABC' , 3,10 ),
(3,'ABC' , 7,10 ),
(4,'ABC' , 16,10 ),
(0,'DEF' , 12,10 ), /* 2 left over from this week */
(1,'DEF' , 11,10 ), /* and 1 more left over from this week */
(2,'DEF' , 3,10 ), /* this week is only 4 short now, because 2 + 1 were left over */
(3,'DEF' , 7,10 ),
(4,'DEF' , 16,10 )
8

hi please see if this works !!!

SQL using LAG

drop create data .. 
drop table #test1 
 go 



create table #test1
(
week int,
child_1 varchar(50) ,
pencil_in_hand int ,
breaks int ,
)
go 

insert into #test1
values
(1,'ABC' , 12,10 ),
(2,'ABC' , 3,10 ),
(3,'ABC' , 7,10 ),
(4,'ABC' , 16,10 )
go 


select 'data',* from #test1 
go
SQL .. using LAG 
; WITH cte 
     AS (SELECT week, 
                child_1, 
                pencil_in_hand, 
                breaks, 
                pencil_in_hand - breaks AS shortage 
         FROM   #test1) 
SELECT week, 
       child_1, 
       pencil_in_hand, 
       breaks, 
       pencil_in_hand + CASE WHEN Lag(shortage) OVER(ORDER BY (SELECT NULL)) > 0 
       THEN 
       Lag(shortage) OVER(ORDER BY (SELECT NULL)) ELSE 0 END - breaks 
FROM   cte 

go

image
image.png771×371 44.2 KB

1 Like
9

You have to 0 out the "shortage" displayed when it's > 0.

10

here it is .. thanks scott for pointing it out :slight_smile:

SQL ..new making 0 
; WITH cte 
     AS (SELECT week, 
                child_1, 
                pencil_in_hand, 
                breaks, 
                pencil_in_hand - breaks AS shortage 
         FROM   #test1) 
SELECT 'my output', week, 
       child_1, 
       pencil_in_hand, 
       breaks, 
       case when 
	   (pencil_in_hand + CASE WHEN Lag(shortage) OVER(ORDER BY (SELECT NULL)) > 0 
       THEN 
       Lag(shortage) OVER(ORDER BY (SELECT NULL)) ELSE 0 END - breaks ) < 0 then 0 else 
	   (pencil_in_hand + CASE WHEN Lag(shortage) OVER(ORDER BY (SELECT NULL)) > 0 
       THEN 
       Lag(shortage) OVER(ORDER BY (SELECT NULL)) ELSE 0 END - breaks ) end 
FROM   cte 

go
slightly different sql .. same results .. shortage column better readability of code 
; WITH cte 
     AS (SELECT week, 
                child_1, 
                pencil_in_hand, 
                breaks, 
                pencil_in_hand - breaks AS shortage 
         FROM   #test1), 
     cte_lag 
     AS (SELECT week, 
                child_1, 
                pencil_in_hand, 
                pencil_in_hand + CASE WHEN Lag(shortage) OVER(ORDER BY (SELECT 
                NULL)) 
                > 
                   0 THEN 
                Lag(shortage) OVER(ORDER BY (SELECT NULL)) ELSE 0 END - breaks 
                AS 
                   shortage 
         FROM   cte) 
SELECT 'my output',
       week, 
       child_1, 
       pencil_in_hand, 
       CASE 
         WHEN shortage > 0 THEN shortage 
         ELSE 0 
       END AS shortage 
FROM   cte_lag 

go

image

11

This is again using recursion . Is there any mathematical way we can do it ?

12

you did it reverse , it should show 0 , when its > 0 .

13

Ok

Just change the case logic

I mean reverse it