Multiple Query to Single Query

Question

I am working with SQL Server 2008.

I created a stored procedure like this:

SELECT  SUM(m.BookingAmt) + SUM(m.FormFee) AS 'SIP'
FROM    dbo.Member AS m
    INNER JOIN dbo.PlanMaster AS pm ON m.PlanId = pm.PlanId
    INNER JOIN dbo.PlanTypeMaster AS ptm ON pm.PlanTypeId = ptm.PlanTypeId
WHERE   ptm.IsSingleInstallment = 'true'
    AND m.CompanyId = @CompanyId
    AND m.CscId = @CscId
    AND m.CommencementDate = @Date

SELECT  SUM(i.PaymentAmt) + SUM(m.FormFee) AS 'Fresh'
FROM    dbo.Installment AS i
    INNER JOIN dbo.Member AS m ON i.MemberId = m.MemberId
    INNER JOIN dbo.PlanMaster AS pm ON i.PlanId = pm.PlanId
    INNER JOIN dbo.PlanTypeMaster AS ptm ON pm.PlanTypeId = ptm.PlanTypeId
WHERE   i.InstallmentNo = 1
    AND m.CompanyId = @CompanyId
    AND m.CscId = @CscId
    AND i.PaymentDate = @Date
    AND ptm.IsSingleInstallment = 'false'

SELECT  SUM(i.PaymentAmt) AS '1stYear'
FROM    dbo.Installment AS i
    INNER JOIN dbo.Member AS m ON i.MemberId = m.MemberId
WHERE   i.InstallmentNo > 1
    AND i.InstallmentNo < 13
    AND m.CompanyId = @CompanyId
    AND m.CscId = @CscId
    AND i.PaymentDate = @Date


SELECT  SUM(i.PaymentAmt) AS '2ndYear'
FROM    dbo.Installment AS i
    INNER JOIN dbo.Member AS m ON i.MemberId = m.MemberId
WHERE   i.InstallmentNo > 12
    AND i.InstallmentNo < 25
    AND m.CompanyId = @CompanyId
    AND m.CscId = @CscId
    AND i.PaymentDate = @Date

SELECT  SUM(i.PaymentAmt) AS '3rdYear'
FROM    dbo.Installment AS i
    INNER JOIN dbo.Member AS m ON i.MemberId = m.MemberId
WHERE   i.InstallmentNo > 24
    AND i.InstallmentNo < 37
    AND m.CompanyId = @CompanyId
    AND m.CscId = @CscId
    AND i.PaymentDate = @Date

SELECT  SUM(i.PaymentAmt) AS '4thYear'
FROM    dbo.Installment AS i
    INNER JOIN dbo.Member AS m ON i.MemberId = m.MemberId
WHERE   i.InstallmentNo > 36
    AND m.CompanyId = @CompanyId
    AND m.CscId = @CscId
    AND i.PaymentDate = @Date

but I want to get result like 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 I get above result using one query? Please help me. Thanks

Answer 1

You can consolidate the queries that generate the 1stYear, 2ndYear, etc by using an aggregate function with a CASE expression to create columns instead of rows.

Then you can create subqueries for the other two, finally joining them all together on the date. So your final query could be:

SELECT coalesce(q1.CommencementDate, q2.PaymentDate, q3.PaymentDate) date,
  q1.SIP,
  q2.Fresh,
  q3.[1stYear],
  q3.[2ndYear],
  q3.[3rdYear],
  q3.[4thYear]
FROM
(
  SELECT m.CommencementDate,
    SUM(m.BookingAmt) + SUM(m.FormFee) AS 'SIP'
  FROM dbo.Member AS m
  INNER JOIN dbo.PlanMaster AS pm 
    ON m.PlanId = pm.PlanId
  INNER JOIN dbo.PlanTypeMaster AS ptm 
    ON pm.PlanTypeId = ptm.PlanTypeId
  WHERE ptm.IsSingleInstallment = 'true'
    AND m.CompanyId = @CompanyId
    AND m.CscId = @CscId
    AND m.CommencementDate = @Date
) q1
FULL OUTER JOIN
(
  SELECT i.PaymentDate,
    SUM(i.PaymentAmt) + SUM(m.FormFee) AS 'Fresh'
  FROM dbo.Installment AS i
  INNER JOIN dbo.Member AS m 
    ON i.MemberId = m.MemberId
  INNER JOIN dbo.PlanMaster AS pm 
    ON i.PlanId = pm.PlanId
  INNER JOIN dbo.PlanTypeMaster AS ptm 
    ON pm.PlanTypeId = ptm.PlanTypeId
  WHERE i.InstallmentNo = 1
    AND m.CompanyId = @CompanyId
    AND m.CscId = @CscId
    AND i.PaymentDate = @Date
    AND ptm.IsSingleInstallment = 'false'
) q2
  ON q1.CommencementDate = q2.PaymentDate
FULL OUTER JOIN
(
  SELECT i.PaymentDate,
    sum(case 
          when i.InstallmentNo > 1 AND i.InstallmentNo < 13
          then i.PaymentAmt else 0 end) AS [1stYear],
    sum(case 
          when i.InstallmentNo > 12 AND i.InstallmentNo < 25
          then i.PaymentAmt else 0 end) AS [2ndYear],
    sum(case 
          when i.InstallmentNo > 24 AND i.InstallmentNo < 37
          then i.PaymentAmt else 0 end) AS [3rdYear],
    sum(case 
          when i.InstallmentNo > 36
          then i.PaymentAmt else 0 end) AS [4thYear]
  SUM(i.PaymentAmt) AS '1stYear'
  FROM dbo.Installment AS i
  INNER JOIN dbo.Member AS m 
    ON i.MemberId = m.MemberId
  WHERE m.CompanyId = @CompanyId
    AND m.CscId = @CscId
    AND i.PaymentDate = @Date
  GROUP BY i.PaymentDate
) q3
  ON q1.CommencementDate = q3.PaymentDate;