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
Assumptions:
perform
table has 2x columns named uniq_id
and UNIQUEID
(otherwise OP can address typos)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]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 ...