Obtaining a field associated with the previous month in Sybase

Question

In the query below, I am trying to get the BALANCE value from table loans for the same Uniq_ID and same year, but previous month, and then compare it to the current rows BALANCE value. The fdate column looks like 3/1/2019. I try to do it as below but get the error "Sybase Database Error: Feature, reference containing a scalar value subquery (defined at line 13) inside a conditional expression (CASE, COALESCE, ARGN, NULLIF, or IF), is not supported". How can I try to do this another way?

SELECT h.UNIQUEID, f.num
(select BALANCE from loans b
    where b.UNIQ_ID = h.UNIQ_ID
    and year(b.FDATE) = year(h.FDATE) 
    and MONTH(b.fdate) = MONTH(h.fdate) - 1) AS prev_bal,
(case when prev_bal > 0 and prev_bal >= BALANCE then 1 else 0 end) as  flag
FROM loans h, perform f
where f.uniq_id = h.uniq_id 

Edit:

The data looks like this:

fdate           UNIQUEID              NUM    BALANCE   
3/1/2019       LNSAR17224-00453434    1      16254.1  
4/1/2019       LNSAR17224-00453434    1      15643.2    

I would like to add the column prev_bal which looks as follows:

fdate           UNIQUEID              NUM    BALANCE   prev_bal    
3/1/2019       LNSAR17224-00453434    1      16254.1    {null}     
4/1/2019       LNSAR17224-00453434    1      15643.2    16254.1 

Expected Output:

UNIQUEID              NUM   prev_bal    flag
LNSAR17224-00453434    1    {null}       0
LNSAR17224-00453434    1    16254.1      1

Answer 1

Assumptions:

• database is configured for case insensitivity (otherwise OP can edit to set all column references as upper() or lower() as needed)
• the perform table has 2x columns named uniq_id and UNIQUEID (otherwise OP can address typos)
• by previous month the OP is referencing the last entry prior to the current month (ie, not necessarily the previous calendar month); so this means we can make use of the max() function to find the previous month [alternative is to look for match on the previous calendar month and treat as outer join if it does not exist; certainly codable but will wait for OPs input]
• OP has shown a NULL value for the prev_balance of one output record; I take this to mean there may not be any previous month data (ie, we need to consider using an outer join)

One query that (I believe) addresses the above:

select h.UNIQUEID,
       f.num,
       d1.prev_balance,
       isnull(d1.flag,0) as flag

from   loans h
join   perform f
on     f.uniq_id = h.uniq_id

left
join   (select h2.UNIQ_ID,                                       -- data associated with previous date
               h2.BALANCE as prev_balance,
               case when h2.BALANCE > 0 and h2.BALANCE >= h.BALANCE 
                    then 1 
                    else 0
               end as flag
        from   loans h2
        where  h2.UNIQ_ID = h.UNIQ_ID
        and    h2.FDATE = (select max(h3.FDATE) as prev_fdate    -- find previous date
                           from   loans h3
                           where  h3.UNIQ_ID = h2.UNIQ_ID
                           and    h3.FDATE   < h2.FDATE)
       ) as d1

on     h.uniq_id = d1.uniq_id

Or getting rid of one level of sub-queries:

select h.UNIQUEID,
       f.num,
       h2.BALANCE as prev_balance,
       isnull(case when h2.BALANCE > 0 and h2.BALANCE >= h.BALANCE
                   then 1
                   else 0 
              end,0) as flag

from   loans h
join   perform f
on     h.uniq_id = f.uniq_id

left
join   loans h2
on     h.uniq_id = h2.uniq_id
and    h2.FDATE = (select max(h3.FDATE) as prev_fdate   -- find previous date
                   from   loans h3
                   where  h3.UNIQ_ID = h2.UNIQ_ID
                   and    h3.FDATE   < h2.FDATE)

NOTE: Don't have table DDL, or sample data, so currently unable to test the above for syntax/data accuracy ...

Answer 2

SELECT fdate, UNIQUEID, PNUM, BALANCE, lag(BALANCE) over (order by fdate)                
from your_table
order by fdate