Comments by Kunal Ved (Top 25 by date)

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 (C