Stephen!
Guess what, guess what!!!!! I finally got the UNION SELECT to work! Know
what the problem was? It was looking for a GROUP BY!!!! Here's the command
I ended up with:
set v vpyrb date =
(rdate(1,1,((int(sget(format(.#date,'mm/dd/yyyy'),4,7)))-1)))
CREATE temp VIEW VDuesAct (DMonth,CYBAmt,CYPAmt,CYCAmt,PYBAmt,PYPAmt,PYCAmt)
AS +
SELECT
(imon(idate)),(ifeq((iyr(idate)),(iyr(#date)),(sum(iamt)),0)),0,0,(ifeq((iyr(idate)),(iyr(.vpyrb)),(sum(iamt)),0)),0,0+
from vcie where idate >= (.vpyrb) and acctid = '401' group by idate +
UNION SEL
(imon(pdate)),0,(ifeq((iyr(pdate)),(iyr(#date)),(sum(pamt)),0)),0,0,(ifeq((iyr(pdate)),(iyr(.vpyrb)),(sum(iamt)),0)),0
from vcie +
where pdate >= (.vpyrb) and acctid = '401' group by pdate +
UNION SEL
(imon(pdate)),0,0,(ifeq((iyr(pdate)),(iyr(#date)),(sum(pamt)),0)),0,0,(ifeq((iyr(pdate)),(iyr(.vpyrb)),(sum(iamt)),0))
from vcie +
where pdate >= (.vpyrb) and acctid = '401' and payway = 'cancel' group by
pdate
Now I'm pretty sure I can get the report to summarize and break as I need it
to!! Thank you SOOO much for your help. This is going to be such an
awesome report and I think alot of y'all may have some use for it too! I am
going to use this to do a report listing monthly billing and payments and
compare it to the same month last year, with the percentage change for
each - what a great management tool!
Stephen, I really appreciate your offer to create the syntax for me. Can
you tell I'm a tad bit "focused"? (My husband prefers that term to
hard-headed!!!)
Have a great weekend!!!!!
Jan Barley
