Hi Guys,
I am writing a query to create a customer statement, however I am getting an error message and I can work out why I am getting the message, could one of you very wise people point me in the right direction:
select shortname,cardname, address, zipcode, city, ocry.name as countryn, county,groupnum,
case when fccurrency is null then 'GBP' else fccurrency
end as fccurrency,
ref1,Ref2 as 'customer ref', linememo, balduedeb, balduecred, balfcdeb, balfccred, taxdate,
case when balduedeb <> 0 then balduedeb
when balduecred <> 0 then (balduecred*-1)
else 0 end as Openamt,
datediff(dd,taxdate,getdate()) as nodays,
case
when (balduedeb <> 0 and datediff(dd,taxdate,getdate()) < = 30) then balduedeb
when (balduecred <> 0 and datediff(dd,taxdate,getdate()) < = 30) then (balduecred*-1)
else 0 end as age0030,
case
when (balduedeb <> 0 and datediff(dd,taxdate,getdate()) > 30)
and (balduedeb <> 0 and datediff(dd,taxdate,getdate()) < = 60)
then balduedeb
when (balduecred <> 0 and datediff(dd,taxdate,getdate()) > 30)
and (balduecred <> 0 and datediff(dd,taxdate,getdate()) < = 60)
then (balduecred*-1)
else 0 end as age3160,
case
when (balduedeb <> 0 and datediff(dd,taxdate,getdate()) > 60)
and (balduedeb <> 0 and datediff(dd,taxdate,getdate()) < = 90)
then balduedeb
when (balduecred <> 0 and datediff(dd,taxdate,getdate()) > 60)
and (balduecred <> 0 and datediff(dd,taxdate,getdate()) < = 90)
then (balduecred*-1)
else 0 end as age6190,
case
when (balduedeb <> 0 and datediff(dd,taxdate,getdate()) > 90)
and (balduedeb <> 0 and datediff(dd,taxdate,getdate()) < = 120)
then balduedeb
when (balduecred <> 0 and datediff(dd,taxdate,getdate()) > 90)
and (balduecred <> 0 and datediff(dd,taxdate,getdate()) < = 120)
then (balduecred*-1)
else 0 end as age90120,
case
when (balduedeb <> 0 and datediff(dd,taxdate,getdate()) > 120)
then balduedeb
when (balduecred <> 0 and datediff(dd,taxdate,getdate()) > 120)
then (balduecred*-1)
else 0 end as age00121
case when baldfcdeb <> 0 then balfcdeb
when balfccred <> 0 then (balfccred*-1)
else 0 end as OpenamtFC,
datediff(dd,taxdate,getdate()) as nodays,
case
when (balfcdeb <> 0 and datediff(dd,taxdate,getdate()) < = 30) then balfcdeb
when (balfccred <> 0 and datediff(dd,taxdate,getdate()) < = 30) then (balfccred*-1)
else 0 end as age0030fc,
case
when (balfcdeb <> 0 and datediff(dd,taxdate,getdate()) > 30)
and (balfcdeb <> 0 and datediff(dd,taxdate,getdate()) < = 60)
then balfcdeb
when (balfccred <> 0 and datediff(dd,taxdate,getdate()) > 30)
and (balfccred <> 0 and datediff(dd,taxdate,getdate()) < = 60)
then (balfccred*-1)
else 0 end as age3160fc,
case
when (balfcdeb <> 0 and datediff(dd,taxdate,getdate()) > 60)
and (balfcdeb <> 0 and datediff(dd,taxdate,getdate()) < = 90)
then balfcdeb
when (balfccred <> 0 and datediff(dd,taxdate,getdate()) > 60)
and (balfccred <> 0 and datediff(dd,taxdate,getdate()) < = 90)
then (balfccred*-1)
else 0 end as age6190fc,
case
when (balfcdeb <> 0 and datediff(dd,taxdate,getdate()) > 90)
and (balfcdeb <> 0 and datediff(dd,taxdate,getdate()) < = 120)
then balfcdeb
when (balfccred <> 0 and datediff(dd,taxdate,getdate()) > 90)
and (balfccred <> 0 and datediff(dd,taxdate,getdate()) < = 120)
then (balfccred*-1)
else 0 end as age90120fc,
case
when (balfcdeb <> 0 and datediff(dd,taxdate,getdate()) > 120)
then balfcdeb
when (balfccred <> 0 and datediff(dd,taxdate,getdate()) > 120)
then (balfccred*-1)
else 0 end as age00121fc
from jdt1
join ocrd on shortname = cardcode and cardtype = 'C'
left outer join ocry on country = code
where balduedeb <> 0
or balduecred <> 0
Many thanks in advance
Kind regards
Sean