quote:

---

Originally posted by vedjha

every table having same column as cclosingno, provide same data.
that's why i us join to all columns

select m.aid,m.cstatus as AssociateID,sum(p.fpay) as payment,
(sum(l.lvl1)+sum(l.lvl2)+sum(l.lvl3)+sum(l.lvl4)+sum(l.lvl5)) as levelpay ,
sum(d.fpay) as directsppay, sum(s.fpay) as sponsoringpay
from members m join payment p
on m.cstatus='V'
and m.cclosing=p.cClosingno
and m.cclosing='1'
join levelpayment l
on m.cclosing=l.cClosingno
join directsppay d
on m.cclosing=d.cClosingno
join sponsoringpay s
on m.cclosing=s.cClosingno
group by m.aid,m.cstatus
order by m.aid asc



Ved Prakash Jha

---

select m.aid,m.cstatus as AssociateID,
sum(case when m.cstatus='V' and m.cclosing='1' then r.fpay else 0 end) as payment,
sum(r.lvl1)+sum(r.lvl2)+sum(r.lvl3)+sum(r.lvl4)+sum(r.lvl5) as levelpay ,
sum(r.dpay) as directsppay, 
sum(r.spay) as sponsoringpay
from members m join 
(
select cClosingno,
SUM(case when type='fpay' then value else 0 end) as fpay,
SUM(case when type='lvl1' then value else 0 end) as lvl1,
SUM(case when type='lvl2' then value else 0 end) as lvl2,
SUM(case when type='lvl3' then value else 0 end) as lvl3,
SUM(case when type='lvl4' then value else 0 end) as lvl4,
SUM(case when type='lvl5' then value else 0 end) as lvl5,
SUM(case when type='dpay' then value else 0 end) as dpay,
SUM(case when type='spay' then value else 0 end) as spay
FROM
(
select cClosingno,fpay as value,'fpay' as type
from payment p

union all

select cClosingno,lvl1,'lvl1'
from levelpayment
where cClosingno='1'

union all

select cClosingno,lvl2,'lvl2'
from levelpayment
where cClosingno='1'

union all

select cClosingno,lvl3,'lvl3'
from levelpayment
where cClosingno='1'

union all

select cClosingno,lvl4,'lvl4'
from levelpayment
where cClosingno='1'

union all

select cClosingno,lvl5,'lvl5'
from levelpayment
where cClosingno='1'

union all

select cClosingno,fpay,'dpay'
from directsppay
where cClosingno='1'

union all

select cClosingno,fpay,'spay'
from sponsoringpay
where cClosingno='1'
)t
group by t.cClosingno
)r
on r.cClosingno=m.cclosing

quote:

---

Originally posted by vedjha

I have to generate payment closing wise.

Ved Prakash Jha

---

select m.aid,m.cstatus as AssociateID,
sum(case when m.cstatus='V' and m.cclosing='1' then r.fpay else 0 end) as payment,
sum(r.lvl1)+sum(r.lvl2)+sum(r.lvl3)+sum(r.lvl4)+sum(r.lvl5) as levelpay ,
sum(r.dpay) as directsppay, 
sum(r.spay) as sponsoringpay
from members m join 
(
select cClosingno,
SUM(case when type='fpay' then value else 0 end) as fpay,
SUM(case when type='lvl1' then value else 0 end) as lvl1,
SUM(case when type='lvl2' then value else 0 end) as lvl2,
SUM(case when type='lvl3' then value else 0 end) as lvl3,
SUM(case when type='lvl4' then value else 0 end) as lvl4,
SUM(case when type='lvl5' then value else 0 end) as lvl5,
SUM(case when type='dpay' then value else 0 end) as dpay,
SUM(case when type='spay' then value else 0 end) as spay
FROM
(
select cClosingno,fpay as value,'fpay' as type
from payment p

union all

select cClosingno,lvl1,'lvl1'
from levelpayment
where cClosingno='1'

union all

select cClosingno,lvl2,'lvl2'
from levelpayment
where cClosingno='1'

union all

select cClosingno,lvl3,'lvl3'
from levelpayment
where cClosingno='1'

union all

select cClosingno,lvl4,'lvl4'
from levelpayment
where cClosingno='1'

union all

select cClosingno,lvl5,'lvl5'
from levelpayment
where cClosingno='1'

union all

select cClosingno,fpay,'dpay'
from directsppay
where cClosingno='1'

union all

select cClosingno,fpay,'spay'
from sponsoringpay
where cClosingno='1'
)t
group by t.cClosingno
)r
on r.cClosingno=m.cclosing
GROUP BY m.aid,m.cstatus