Skip to Content
0
Former Member
Nov 18, 2011 at 10:30 AM

Statement Query Problem

24 Views

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