Duplication Found in IP Patients for Daily MIS Finance:

SELECT `ENCOUNTER NO`, COUNT(`ENCOUNTER NO`) FROM reports.daily_ip_finalised_bill_details where Date(`BILL DATE`) between '2023-10-01' and '2023-11-31'
GROUP BY `ENCOUNTER NO` HAVING COUNT(`ENCOUNTER NO`) > 1;

SELECT `ENCOUNTER NO`, COUNT(`ENCOUNTER NO`) FROM reports_mgmci.daily_ip_finalised_bill_details where Date(`BILL DATE`) between '2023-10-01' and '2023-11-31'
GROUP BY `ENCOUNTER NO` HAVING COUNT(`ENCOUNTER NO`) > 1;

QMS Query:
SELECT * FROM qms.his_revenue_daily_revenue_op_report where DATE_FORMAT(STR_TO_DATE(`BILL DATE`,'%d-%M-%Y'),'%Y-%m-%d')=(Select current_date())


SELECT * FROM reports.`daily_revenue_op_report` WHERE `BILL DATE`=(DATE_SUB(CURDATE(), INTERVAL 1 DAY)) 

SELECT * FROM reports.daily_ip_finalised_bill_details WHERE `BILL DATE`=(DATE_SUB(CURDATE(), INTERVAL 1 DAY)) 

SELECT * FROM reports.`pharmacy_daily_sales` WHERE DATE_FORMAT(STR_TO_DATE(`BILL DATE`,'%d-%M-%Y'),'%Y-%m-%d')=(DATE_SUB(CURDATE(), INTERVAL 1 DAY))  AND `PHARMACY LOCATION NAME`='OP PHARMACY'


SELECT * FROM reports_mgmci.`daily_revenue_op_report` WHERE `BILL DATE`=(DATE_SUB(CURDATE(), INTERVAL 1 DAY)) 

SELECT * FROM reports_mgmci.daily_ip_finalised_bill_details WHERE `BILL DATE`=(DATE_SUB(CURDATE(), INTERVAL 1 DAY)) 

SELECT * FROM reports.`pharmacy_daily_sales` WHERE DATE_FORMAT(STR_TO_DATE(`BILL DATE`,'%d-%M-%Y'),'%Y-%m-%d')=(DATE_SUB(CURDATE(), INTERVAL 1 DAY))  AND `PHARMACY LOCATION NAME`='OP PHARMACY [MGMCI]'

QMS Query:
SELECT * from qms.his_revenue_daily_revenue_op_report where CURRENTDATE=(Select curdate()) and `ENCOUNTER NO` in (SELECT `ENCOUNTER NO` FROM qms.phc_patient_visit_for_opd_followup where `VISIT TYPE BY DEPARTMENT`!='SERVICES' and DATE_FORMAT(STR_TO_DATE(`DATE - TIME`,'%d-%M-%Y'),'%Y-%m-%d')=(SELECT DATE_SUB(CURDATE(), INTERVAL 0 DAY))) order by `BILL TIME`,TokenNo, Time;

Patient List - Reqested By Ms.Shanu:

Select `Encounter No`,UHID,`Patient Name`,`Doctor Name`,Department,`Bill Date`,
COUNT( IF(`Service Group` = 'CONSULTATION', 1, NULL)) AS Consultation,
COUNT( IF(`Service Name` like '%Echo%', 1, NULL)) AS Echo,
COUNT( IF(`Service Name` like '%ECG%', 1, NULL)) AS ECG,
COUNT( IF(`Service Name` like '%TMT%', 1, NULL)) AS TMT,
COUNT( IF(`Service Group` = 'LABORATORY', 1, NULL)) AS LAB,
COUNT( IF(`Service Group` = 'Mammogram', 1, NULL)) AS Mammogram,
COUNT( IF(`Service Name` like '%X Ray%', 1, NULL)) AS XRay,
COUNT( IF(`Service Name` like '%Ultrasound%', 1, NULL)) AS Ultrasound,
COUNT( IF(`Service Name` like '%MRI%', 1, NULL)) AS MRI,
COUNT( IF(`Service Name` like '%CT %', 1, NULL)) AS CT,
COUNT( IF(`Service Group` = 'RADIOLOGY', 1, NULL)) AS RADIOLOGY
FROM reports.`item wise service report` where `Bill Date` between '2024-04-01' and '2024-04-31' and `Visit Type`='OP' and `Service Group` not in ('HOSPITAL ADMINISTRATIVE CHARGES','PHARMACY','PROFESSIONAL FEES','ALLIED HEALTH','BED CHARGES','EQUIPMENT','INPATIENT SERVICES','CLINICAL TRIAL','MHC','NUTRITION & DIETETICS','ORTHO','OTHERS') and Department!='EMERGENCY MEDICINE' group by `Encounter No` order by Department

Doctor's Revenue Details - COO:

SELECT revenue_adm_doctor,revenue_department,
COUNT( IF(((revenue_encounter_type = 'Outpatient') AND DATE(revenue_bill_date)  BETWEEN '2024-04-01' AND '2024-04-07'), 1, NULL)) AS 'OP Count1',
SUM( IF(((revenue_encounter_type = 'Outpatient') AND DATE(revenue_bill_date) BETWEEN '2024-04-01' AND '2024-04-07'), revenue_bill_amount, 0)) AS 'OP Revenue1',
COUNT( IF(((revenue_encounter_type = 'Inpatient') AND DATE(revenue_bill_date)  BETWEEN '2024-04-01' AND '2024-04-07'), 1, NULL)) AS 'IP Count1',
SUM( IF(((revenue_encounter_type = 'Inpatient') AND DATE(revenue_bill_date) BETWEEN '2024-04-01' AND '2024-04-07'), revenue_bill_amount, 0)) AS 'IP Revenue1',
SUM( IF(((revenue_encounter_type = 'Outpatient') AND DATE(revenue_bill_date) BETWEEN '2024-04-01' AND '2024-04-07'), revenue_bill_amount, 0))+SUM( IF(((revenue_encounter_type = 'Inpatient') AND DATE(revenue_bill_date) BETWEEN '2024-04-01' AND '2024-04-07'), revenue_bill_amount, 0)) as 'Total Revenue1',


COUNT( IF(((revenue_encounter_type = 'Outpatient') AND DATE(revenue_bill_date)  BETWEEN '2024-05-01' AND '2024-05-07'), 1, NULL)) AS 'OP Count2',
SUM( IF(((revenue_encounter_type = 'Outpatient') AND DATE(revenue_bill_date) BETWEEN '2024-05-01' AND '2024-05-07'), revenue_bill_amount, 0)) AS 'OP Revenue2',
COUNT( IF(((revenue_encounter_type = 'Inpatient') AND DATE(revenue_bill_date)  BETWEEN '2024-05-01' AND '2024-05-07'), 1, NULL)) AS 'IP Count2',
SUM( IF(((revenue_encounter_type = 'Inpatient') AND DATE(revenue_bill_date) BETWEEN '2024-05-01' AND '2024-05-07'), revenue_bill_amount, 0)) AS 'IP Revenue2',

SUM( IF(((revenue_encounter_type = 'Outpatient') AND DATE(revenue_bill_date) BETWEEN '2024-05-01' AND '2024-05-07'), revenue_bill_amount, 0))+SUM( IF(((revenue_encounter_type = 'Inpatient') AND DATE(revenue_bill_date) BETWEEN '2024-05-01' AND '2024-05-07'), revenue_bill_amount, 0)) as 'Total Revenue2'


FROM test.ci_revenue WHERE revenue_visit_id!='Pharmacy' AND revenue_adm_doctor!='' AND revenue_department!=''  GROUP BY revenue_adm_doctor_code,revenue_department order by revenue_department


Doctor's Revenue - Sarath:

SELECT revenue_department,
COUNT( IF(((revenue_encounter_type = 'Outpatient') AND DATE(revenue_bill_date)  BETWEEN '2024-04-01' AND '2024-04-30' and Branch='MGM'), 1, NULL)) AS 'OP Count1',
SUM( IF(((revenue_encounter_type = 'Outpatient') AND DATE(revenue_bill_date) BETWEEN '2024-04-01' AND '2024-04-30' and Branch='MGM'), revenue_bill_amount, 0)) AS 'OP Revenue1',
COUNT( IF(((revenue_encounter_type = 'Inpatient') AND DATE(revenue_bill_date)  BETWEEN '2024-04-01' AND '2024-04-30' and Branch='MGM'), 1, NULL)) AS 'IP Count1',
SUM( IF(((revenue_encounter_type = 'Inpatient') AND DATE(revenue_bill_date) BETWEEN '2024-04-01' AND '2024-04-30' and Branch='MGM'), revenue_bill_amount, 0)) AS 'IP Revenue1',
SUM( IF(((revenue_encounter_type = 'Outpatient') AND DATE(revenue_bill_date) BETWEEN '2024-04-01' AND '2024-04-30' and Branch='MGM'), revenue_bill_amount, 0))+SUM( IF(((revenue_encounter_type = 'Inpatient') AND DATE(revenue_bill_date) BETWEEN '2024-04-01' AND '2024-04-30' and Branch='MGM'), revenue_bill_amount, 0)) as 'Total Revenue1',

COUNT( IF(((revenue_encounter_type = 'Outpatient') AND DATE(revenue_bill_date)  BETWEEN '2024-04-01' AND '2024-04-30' and Branch='MGMCI'), 1, NULL)) AS 'OP Count2',
SUM( IF(((revenue_encounter_type = 'Outpatient') AND DATE(revenue_bill_date) BETWEEN '2024-04-01' AND '2024-04-30' and Branch='MGMCI'), revenue_bill_amount, 0)) AS 'OP Revenue2',
COUNT( IF(((revenue_encounter_type = 'Inpatient') AND DATE(revenue_bill_date)  BETWEEN '2024-04-01' AND '2024-04-30' and Branch='MGMCI'), 1, NULL)) AS 'IP Count2',
SUM( IF(((revenue_encounter_type = 'Inpatient') AND DATE(revenue_bill_date) BETWEEN '2024-04-01' AND '2024-04-30' and Branch='MGMCI'), revenue_bill_amount, 0)) AS 'IP Revenue2',
SUM( IF(((revenue_encounter_type = 'Outpatient') AND DATE(revenue_bill_date) BETWEEN '2024-04-01' AND '2024-04-30' and Branch='MGMCI'), revenue_bill_amount, 0))+SUM( IF(((revenue_encounter_type = 'Inpatient') AND DATE(revenue_bill_date) BETWEEN '2024-04-01' AND '2024-04-30' and Branch='MGMCI'), revenue_bill_amount, 0)) as 'Total Revenue2',


COUNT( IF(((revenue_encounter_type = 'Outpatient') AND DATE(revenue_bill_date)  BETWEEN '2024-04-01' AND '2024-04-30' and Branch='MGMMalar'), 1, NULL)) AS 'OP Count3',
SUM( IF(((revenue_encounter_type = 'Outpatient') AND DATE(revenue_bill_date) BETWEEN '2024-04-01' AND '2024-04-30' and Branch='MGMMalar'), revenue_bill_amount, 0)) AS 'OP Revenue3',
COUNT( IF(((revenue_encounter_type = 'Inpatient') AND DATE(revenue_bill_date)  BETWEEN '2024-04-01' AND '2024-04-30' and Branch='MGMMalar'), 1, NULL)) AS 'IP Count3',
SUM( IF(((revenue_encounter_type = 'Inpatient') AND DATE(revenue_bill_date) BETWEEN '2024-04-01' AND '2024-04-30' and Branch='MGMMalar'), revenue_bill_amount, 0)) AS 'IP Revenue3',
SUM( IF(((revenue_encounter_type = 'Outpatient') AND DATE(revenue_bill_date) BETWEEN '2024-04-01' AND '2024-04-30' and Branch='MGMMalar'), revenue_bill_amount, 0))+SUM( IF(((revenue_encounter_type = 'Inpatient') AND DATE(revenue_bill_date) BETWEEN '2024-04-01' AND '2024-04-30' and Branch='MGMMalar'), revenue_bill_amount, 0)) as 'Total Revenue3'


FROM test.ci_revenue_sarath WHERE revenue_visit_id!='Pharmacy' AND revenue_adm_doctor!='' AND revenue_department!=''  GROUP BY revenue_department order by revenue_department




SELECT GROUP_CONCAT(DISTINCT
         CONCAT('SUM(CASE WHEN Date_of_Occupancy = ''', Date_of_Occupancy,
                ''' THEN 1 ELSE 0 END) `', Date_of_Occupancy, '`'))
INTO @sql
  FROM his_db.inpatient_expenses_ip_occupancy_list where Date_of_Occupancy between '2024-07-01' and '2024-07-31';
SET @sql = CONCAT('SELECT Deptname,Doctor, ', @sql, ', Count(*) as "Total"
                     FROM his_db.inpatient_expenses_ip_occupancy_list where Date_of_Occupancy between "2024-07-01" and "2024-07-31" and locname="MGM"
                    GROUP BY Deptname,Doctor');
                    PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

EMR Usage Report Query:
Select `DEPARTMENT NAME`,`DOCTOR ID`,`CONSULTANT NAME`,Count(*) as `No of Encounters`,Sum(IF((`DOCTOR NOTES`!=''),1, 0)) as `No of Notes` FROM test1.patient_followup_details where DATE_FORMAT(STR_TO_DATE(`VISIT DATE`,'%d-%m-%Y'),'%Y-%m-%d') between '2024-06-01' and '2024-06-31' group by `DOCTOR ID` order by `DEPARTMENT NAME`



To Calculate Retention

SELECT SUM(day_visits) AS total_unique_uhid_day_visits
FROM (
    SELECT r.revenue_mrn, COUNT(DISTINCT DATE(r.revenue_bill_date)) AS day_visits
    FROM test1.ci_revenue_old r
    JOIN (
        SELECT DISTINCT UHID
        FROM test1.outpatient_visit_details
        WHERE STR_TO_DATE(`DATE - TIME`, '%d-%M-%Y') BETWEEN '2024-07-01' AND '2024-12-31'
    ) o ON r.revenue_mrn = o.UHID
    WHERE r.revenue_bill_date BETWEEN '2025-05-01' AND '2025-05-31' and revenue_mrn!='9000001169' and revenue_visit_id not in
    (SELECT `ENCOUNTER NO.` FROM test1.`dialysis patients details`)
    GROUP BY r.revenue_mrn
) AS visits_summary;


SELECT SUM(day_visits) AS total_unique_uhid_day_visits
FROM (
    SELECT r.revenue_mrn, COUNT(DISTINCT DATE(r.revenue_bill_date)) AS day_visits
    FROM test1.ci_revenue_mgmci r
    JOIN (
        SELECT DISTINCT UHID
        FROM test1.outpatient_visit_details_mgmci
        WHERE STR_TO_DATE(`DATE - TIME`, '%d-%M-%Y') BETWEEN '2024-07-01' AND '2024-12-31'
    ) o ON r.revenue_mrn = o.UHID
    WHERE r.revenue_bill_date BETWEEN '2025-05-01' AND '2025-05-31'
    GROUP BY r.revenue_mrn,r.revenue_bill_date
) AS visits_summary;




UPDATE revenue_sharing_details_finance.revenue_master_finance AS t1
JOIN test3.`op bill breakup revenue details_mgmvz` AS t2
  ON t1.UHID = t2.UHID
  AND t1.`BILL NO` = t2.`BILL NO`
SET t1.MHC = t2.`MASTER HEALTH CHECK`
WHERE t1.Reports = 'OPBB'
  AND t1.`SATELLITE LOCATION NAME` = 'MGMVZ'
  AND t2.`MASTER HEALTH CHECK` IS NOT NULL AND t2.`MASTER HEALTH CHECK` != ''
  AND t1.Date >= '2025-06-01' AND t1.Date < '2025-06-30'
  AND t2.`Bill date` >= '2025-06-01' AND t2.`Bill date` < '2025-06-30';