---------------------------------------------
This is my Stored Proc
---------------------------------------------
CREATE PROCEDURE [dbo].[USP_GET_ADMISSION_SUMMARY_REPORT]
(
@DATE_FROM DATETIME,
@DATE_TO DATETIME,
@CENTRE_ID INT=-1
)
AS BEGIN
SELECT CENTRE_NAME, [TOTAL SAIG],[TOTAL BCA],[TOTAL SAIG BCA],[TOTAL REGULAR],[TOTAL ADMISSIONS],[SAIG BILLING],[BCA BILLING],[SAIG BCA BILLING], [REGULAR BILLING], [TOTAL BILLING],[SAIG COLLECTION],[BCA COLLECTION],[SAIG BCA COLLECTION],[REGULAR COLLECTI
ON],[TOTAL COLLECTION]
FROM
(
SELECT
ADMISSION_MASTER.CENTRE_ID,
COUNT(*) AS TOTAL_ADMISSIONS,
COURSE=CASE
WHEN COURSE_TAKEN LIKE '%SAIG%' AND COURSE_TAKEN NOT LIKE '%BCA%' THEN 'TOTAL SAIG'
WHEN COURSE_TAKEN LIKE '%BCA%' AND COURSE_TAKEN NOT LIKE '%SAIG%' THEN 'TOTAL BCA'
WHEN COURSE_TAKEN LIKE '%SAIG%BCA%' THEN 'TOTAL SAIG BCA'
ELSE 'TOTAL REGULAR'
END
FROM
ADMISSION_MASTER
WHERE (ADMISSION_MASTER.ADMISSION_DATE BETWEEN @DATE_FROM AND @DATE_TO)
AND (ADMISSION_MASTER.CENTRE_ID=@CENTRE_ID OR @CENTRE_ID=-1)
GROUP BY CENTRE_ID, COURSE_TAKEN
UNION ALL
SELECT
ADMISSION_MASTER.CENTRE_ID,
COUNT(*) AS TOTAL_ADMISSIONS,
COURSE='TOTAL ADMISSIONS'
FROM
ADMISSION_MASTER
WHERE (ADMISSION_MASTER.ADMISSION_DATE BETWEEN @DATE_FROM AND @DATE_TO)
AND (ADMISSION_MASTER.CENTRE_ID=@CENTRE_ID OR @CENTRE_ID=-1)
GROUP BY CENTRE_ID, COURSE_TAKEN
UNION ALL
SELECT
ADMISSION_MASTER.CENTRE_ID,
SUM(TOTALFEES),
COURSE=CASE
WHEN COURSE_TAKEN LIKE '%SAIG%' AND (COURSE_TAKEN NOT LIKE '%BCA%') THEN 'SAIG BILLING'
WHEN COURSE_TAKEN LIKE '%BCA%' AND (COURSE_TAKEN NOT LIKE '%SAIG%') THEN 'BCA BILLING'
WHEN COURSE_TAKEN LIKE '%SAIG%BCA%' THEN 'SAIG BCA BILLING'
ELSE 'REGULAR BILLING'
END
FROM
ADMISSION_MASTER
WHERE (ADMISSION_MASTER.ADMISSION_DATE BETWEEN @DATE_FROM AND @DATE_TO)
AND (ADMISSION_MASTER.CENTRE_ID=@CENTRE_ID OR @CENTRE_ID=-1)
GROUP BY CENTRE_ID, COURSE_TAKEN
UNION ALL
SELECT
ADMISSION_MASTER.CENTRE_ID,
SUM(TOTALFEES),
COURSE='TOTAL BILLING'
FROM
ADMISSION_MASTER
WHERE (ADMISSION_MASTER.ADMISSION_DATE BETWEEN @DATE_FROM AND @DATE_TO)
AND (ADMISSION_MASTER.CENTRE_ID=@CENTRE_ID OR @CENTRE_ID=-1)
GROUP BY CENTRE_ID
UNION ALL
SELECT
ADMISSION_RECEIPTS.CENTRE_ID,
SUM(ADMISSION_RECEIPTS.AMOUNT_PAID_FEES) AS COLLECTION,
COURSE=CASE
WHEN COURSE_TAKEN LIKE '%SAIG%' AND COURSE_TAKEN NOT LIKE '%BCA%' THEN 'SAIG COLLECTION'
WHEN COURSE_TAKEN LIKE '%BCA%' AND COURSE_TAKEN NOT LIKE '%SAIG%' THEN 'BCA COLLECTION'
WHEN COURSE_TAKEN LIKE '%SAIG BCA%' THEN 'SAIG BCA COLLECTION'
ELSE 'REGULAR COLLECTION'
END
FROM
ADMISSION_RECEIPTS INNER JOIN ADMISSION_MASTER
ON ADMISSION_RECEIPTS.ADMISSION_ID = ADMISSION_MASTER.ADMISSION_ID
WHERE
(ADMISSION_RECEIPTS.PAYMENT_DATE BETWEEN @DATE_FROM AND @DATE_TO)
AND (ADMISSION_RECEIPTS.CENTRE_ID=@CENTRE_ID OR @CENTRE_ID=-1)
GROUP BY
ADMISSION_RECEIPTS.CENTRE_ID, COURSE_TAKEN
UNION ALL
SELECT
ADMISSION_RECEIPTS.CENTRE_ID,
SUM(ADMISSION_RECEIPTS.AMOUNT_PAID_FEES) AS COLLECTION,
COURSE='TOTAL COLLECTION'
FROM
ADMISSION_RECEIPTS INNER JOIN ADMISSION_MASTER
ON ADMISSION_RECEIPTS.ADMISSION_ID = ADMISSION_MASTER.ADMISSION_ID
WHERE
(ADMISSION_RECEIPTS.PAYMENT_DATE BETWEEN @DATE_FROM AND @DATE_TO)
AND (ADMISSION_RECEIPTS.CENTRE_ID=@CENTRE_ID OR @CENTRE_ID=-1)
GROUP BY
ADMISSION_RECEIPTS.CENTRE_ID
) AS SOURCE
PIVOT
(
SUM(TOTAL_ADMISSIONS) FOR COURSE IN ([TOTAL REGULAR],[TOTAL SAIG],[TOTAL BCA],[TOTAL SAIG BCA],[TOTAL ADMISSIONS],[SAIG BILLING],[BCA BILLING],[SAIG BCA BILLING],[REGULAR BILLING],[TOTAL BILLING],[SAIG COLLECTION],[BCA COLLECTION],[SAIG BCA COLLECTION],[R
EGULAR COLLECTION],[TOTAL COLLECTION]
)
) AS PVT
RIGHT OUTER JOIN CENTRE_MASTER
ON CENTRE_MASTER.CENTRE_ID=PVT.CENTRE_ID
WHERE
CENTRE_MASTER.CENTRE_NAME NOT LIKE '%TEST%'
AND CENTRE_MASTER.ISACTIVE=1
AND (CENTRE_MASTER.CENTRE_ID=@CENTRE_ID OR @CENTRE_ID=-1)
ORDER BY [TOTAL COLLECTION] DESC ,[TOTAL BILLING] DESC
END