Get last field value into next row

[evr72]

Hello,

Is there a way to get the last value in a column into the next row

My table looks like this

Day Begin Invoice Cash Change Ending
1 7500 2500 500 2000 [highlight #FCE94F]7000[/highlight]
2 [highlight #FCE94F]7000[/highlight] 1500 1000 500 [highlight #FCE94F]6500[/highlight]
3 [highlight #FCE94F]6500[/highlight] 4500 2500 2000 4500


I tried the example below, but that didn't work, it just gives me the first day

SELECT
CASE WHEN DAY ='SATURDAY' THEN 1 WHEN DAY = 'SUNDAY' THEN 1 ELSE 0 END AS DYWK,
CALCURMO."DAY",
CALCURMO.DY,
ARBBALANCE.BALANCE,
(CASE WHEN CALCURMO.DY < 02 THEN ARBBALANCE.BALANCE WHEN CALCURMO.DY > 01 then (CASE WHEN ARBBALANCE.BALANCE - (ARDAILYBI1.INVAM01 - ARDAILYBI1.AMTRC01) < 0 THEN ARBBALANCE.BALANCE - (ARDAILYBI1.INVAM01 - ARDAILYBI1.AMTRC01) *-1 ELSE ARBBALANCE.BALANCE - (ARDAILYBI1.INVAM01 - ARDAILYBI1.AMTRC01) END)  END)AS BGBAL,
ARDAILYBI1.INVAM01,
ARDAILYBI1.AMTRC01,
CASE WHEN (ARDAILYBI1.INVAM01 - ARDAILYBI1.AMTRC01) < 0 THEN (ARDAILYBI1.INVAM01 - ARDAILYBI1.AMTRC01) * -1 ELSE (ARDAILYBI1.INVAM01 - ARDAILYBI1.AMTRC01)  END AS CHANGE1,

CASE WHEN ARBBALANCE.BALANCE - (ARDAILYBI1.INVAM01 - ARDAILYBI1.AMTRC01) < 0 THEN ARBBALANCE.BALANCE - (ARDAILYBI1.INVAM01 - ARDAILYBI1.AMTRC01) *-1 ELSE ARBBALANCE.BALANCE - (ARDAILYBI1.INVAM01 - ARDAILYBI1.AMTRC01) END AS ENDINGBALANCE
 

FROM CALCURMO
LEFT OUTER JOIN ARBBALANCE ON CALCURMO.DY = ARBBALANCE.DY
LEFT OUTER JOIN ARDAILYBI1 ON CALCURMO.DY = ARDAILYBI1.DYTRNDT
WHERE (CASE WHEN DAY ='SATURDAY' THEN 1 WHEN DAY = 'SUNDAY' THEN 1 ELSE 0 END <>1)

The CALCURMO table is the current month by days. day = dy
The ARBBALANCE has the very first record (Balance) of the Begin column
The ARDAILYBI1 table has the INVAM01 = INVOICE and AMTRC01 = CASH
THE CHANGE is a calculation

 

[SkipVought]

Hi,

"My table looks like [highlight #FCE94F]this[/highlight]"

[pre][highlight #FCE94F]Day[/highlight]
[highlight #FCAF3E]
1
2
3[/highlight]
[/pre]

CASE WHEN [highlight #FCE94F]DAY[/highlight] =[highlight #FCAF3E]'SATURDAY'[/highlight] THEN 1

???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[evr72]

I use that to exclude Saturday and Sunday

 

[evr72]

I used this to exclude Saturday and Sunday. I know I could have made it easier

WHERE (CASE WHEN DAY ='SATURDAY' THEN 1 WHEN DAY = 'SUNDAY' THEN 1 ELSE 0 END <>1)

 

[SkipVought]

In your table, Day is numeric.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[evr72]

Is that the issue?

 

[SkipVought]

There could be more, but that is indeed one.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein