Forum Discussion

matt0020190's avatar
matt0020190
Brass Contributor
Dec 13, 2021

Second to last value from table with condition

Hi experts   Can you help me with the below?   I have a list of cash values in a table, example as follows by a rotating week number (1-4).   A ------------ B 1 ----------- 40 2------------- ...
excel
peiyezhu's avatar
peiyezhu
Bronze Contributor
Mar 11, 2023
how about this？

http://e.anyoupin.cn/bsbm/pdo/eh2/a2502/mybookmark/sql/sql/display_table_t.php?find=+create+temp+table+aa+as+%0D%0A+select++fillna%28iif%28rowid%3D1+or+f01%3D1%2Crowid%2C%27%27%29%29+r%2C*+from+Second_to_last_value_from_table_with_condition%3B%0D%0A%0D%0A%2F%2Fselect+*+from+aa%3B%0D%0Acreate+temp+table+bb+as+%0D%0Aselect++row_number%28%29+over+%28order+by+r+desc%29+last%2Cr+from+aa+group+by+r%3B%0D%0Aselect+last+last_two+%2Cf01+lookup_week%2Cf02+look_period%2Cf03+amount+from+bb+join+aa+using%28r%29+where+f01%3D3+and+%28last%3D2+or+last%3D1%29+order+by+last+desc%3B%0D%0Aselect+last%2Cf01%2Cf02%2Ciif%28f01%3D3+and+%28last%3D2+or+last%3D1%29%2Chighlight%28f03%2C%27yellow%27%29%2Cf03%29+f03+from+bb+join+aa+using%28r%29+order+by+last+desc%3B