sql server - Multiple Query to Single Query -
i working sql server 2008.
i created stored procedure this:
select sum(m.bookingamt) + sum(m.formfee) 'sip' dbo.member m inner join dbo.planmaster pm on m.planid = pm.planid inner join dbo.plantypemaster ptm on pm.plantypeid = ptm.plantypeid ptm.issingleinstallment = 'true' , m.companyid = @companyid , m.cscid = @cscid , m.commencementdate = @date select sum(i.paymentamt) + sum(m.formfee) 'fresh' dbo.installment inner join dbo.member m on i.memberid = m.memberid inner join dbo.planmaster pm on i.planid = pm.planid inner join dbo.plantypemaster ptm on pm.plantypeid = ptm.plantypeid i.installmentno = 1 , m.companyid = @companyid , m.cscid = @cscid , i.paymentdate = @date , ptm.issingleinstallment = 'false' select sum(i.paymentamt) '1styear' dbo.installment inner join dbo.member m on i.memberid = m.memberid i.installmentno > 1 , i.installmentno < 13 , m.companyid = @companyid , m.cscid = @cscid , i.paymentdate = @date select sum(i.paymentamt) '2ndyear' dbo.installment inner join dbo.member m on i.memberid = m.memberid i.installmentno > 12 , i.installmentno < 25 , m.companyid = @companyid , m.cscid = @cscid , i.paymentdate = @date select sum(i.paymentamt) '3rdyear' dbo.installment inner join dbo.member m on i.memberid = m.memberid i.installmentno > 24 , i.installmentno < 37 , m.companyid = @companyid , m.cscid = @cscid , i.paymentdate = @date select sum(i.paymentamt) '4thyear' dbo.installment inner join dbo.member m on i.memberid = m.memberid i.installmentno > 36 , m.companyid = @companyid , m.cscid = @cscid , i.paymentdate = @date but want result this...
--------------------------------------------------------------------- date | sip | fresh | 1styear | 2ndyear | 3rdyear | 4thyear | --------------------------------------------------------------------- 01/02/2013 | 2000 | 2500 | 5000 | 3500 | 4500 | 6500 | ------------------------------------------------------- -------------- 02/02/2013 | 6500 | 5000 | 1500 | 4500 | 3520 | 1852 | -------------------------------------------------------------------- 03/02/2013 | 2560 | 2500 | 3500 | 4500 | 2000 | 2000 | how can above result using 1 query? please me. thanks
you can consolidate queries generate 1styear, 2ndyear, etc using aggregate function case expression create columns instead of rows.
then can create subqueries other two, joining them on date. final query be:
select coalesce(q1.commencementdate, q2.paymentdate, q3.paymentdate) date, q1.sip, q2.fresh, q3.[1styear], q3.[2ndyear], q3.[3rdyear], q3.[4thyear] ( select m.commencementdate, sum(m.bookingamt) + sum(m.formfee) 'sip' dbo.member m inner join dbo.planmaster pm on m.planid = pm.planid inner join dbo.plantypemaster ptm on pm.plantypeid = ptm.plantypeid ptm.issingleinstallment = 'true' , m.companyid = @companyid , m.cscid = @cscid , m.commencementdate = @date ) q1 full outer join ( select i.paymentdate, sum(i.paymentamt) + sum(m.formfee) 'fresh' dbo.installment inner join dbo.member m on i.memberid = m.memberid inner join dbo.planmaster pm on i.planid = pm.planid inner join dbo.plantypemaster ptm on pm.plantypeid = ptm.plantypeid i.installmentno = 1 , m.companyid = @companyid , m.cscid = @cscid , i.paymentdate = @date , ptm.issingleinstallment = 'false' ) q2 on q1.commencementdate = q2.paymentdate full outer join ( select i.paymentdate, sum(case when i.installmentno > 1 , i.installmentno < 13 i.paymentamt else 0 end) [1styear], sum(case when i.installmentno > 12 , i.installmentno < 25 i.paymentamt else 0 end) [2ndyear], sum(case when i.installmentno > 24 , i.installmentno < 37 i.paymentamt else 0 end) [3rdyear], sum(case when i.installmentno > 36 i.paymentamt else 0 end) [4thyear] sum(i.paymentamt) '1styear' dbo.installment inner join dbo.member m on i.memberid = m.memberid m.companyid = @companyid , m.cscid = @cscid , i.paymentdate = @date group i.paymentdate ) q3 on q1.commencementdate = q3.paymentdate;
Comments
Post a Comment