Demo entry 2313549

asds

   

Submitted by anonymous on Jul 22, 2015 at 17:27
Language: SQL. Code size: 2.8 kB.

CREATE or replace
    ALGORITHM = MERGE 
    SQL SECURITY DEFINER
VIEW `report_patient_therapy_scheduling` AS
    SELECT 
        `t`.`Id` AS `Id`,
        `p`.`id` AS `PatientId`,
        CONCAT(`p`.`f_name`, ' ', `p`.`l_name`) AS `PatientName`,
        `icomp`.`id` AS `InsuranceCompanyId`,
        `icomp`.`name` AS `InsuranceCompanyName`,
        `t`.`PrescrDate` AS `TherapyDate`,
        `t`.`RefPhysicianId` AS `RefPhysicianId`,
        `p`.`County` AS `County`,
        `s`.`abbrev` AS `State`,
        `p`.`StateId` AS `StateId`,
        CONCAT(`rp`.`f_name`, ' ', `rp`.`l_name`) AS `RefPhysicianName`,
        `t`.`TherapyTypeId` AS `TherapyTypeId`,
        `tt`.`Name` AS `TherapyTypeName`,
        `td`.`PriorityLevelType` AS `PriorityLevelType`,
        `p`.`TherapyPhoneCallStatus` AS `TherapyPhoneCallStatus`,
        `sp`.`id` AS `SalesPersonId`,
        `sp`.`FullName` AS `SalesPersonName`,
        `tc`.`Date` AS `ContactDate`,
        `tc`.`PersonType` AS `ContactPersonType`,
        `tc`.`PersonName` AS `ContactPersonName`
    FROM
        `patient_therapy` `t`
        JOIN `patient_therapy_details` `td` ON `t`.`Id` = `td`.`Id`
        JOIN `patients` `p` ON `p`.`id` = `t`.`PatientId` AND NOT `p`.`Archive`
        JOIN `states` `s` ON `p`.`StateId` = `s`.`Id`
        JOIN `pat_insurance` `pi` ON (`pi`.`pid` = `p`.`id` AND `pi`.`ins_typ_id` = 1 AND `pi`.`VerificationStatus` = 1 AND NOT `pi`.`Archive`)
        JOIN `insurance_names` `icomp` ON `pi`.`ins_name_id` = `icomp`.`id`
        JOIN `ref_physicians` `rp` ON `t`.`RefPhysicianId` = `rp`.`id`
        JOIN `therapy_types` `tt` ON `t`.`TherapyTypeId` = `tt`.`Id`
        LEFT JOIN `ref_phy_offices` `rho` ON `t`.`RefPhysicianOfficeId` = `rho`.`id`
        LEFT JOIN `users` `sp` on `rho`.`sales_person_Id` = `sp`.`id`
        LEFT JOIN `patient_therapy_contact` tc ON tc.TherapyId = t.Id
        LEFT JOIN `patient_therapy_contact` tci ON (t.Id = tci.TherapyId and tci.Date > tc.date)
        JOIN `therapy_type_to_equipment_groups` ttteg ON ttteg.TherapyTypeId = t.TherapyTypeId
    WHERE
        `t`.`TherapyStatus` = 2 
        AND tci.Id is null
        AND (`t`.`DeliveryMethod` IS NULL OR `t`.`DeliveryMethod` <> 2)
		AND (NOT (EXISTS( SELECT 
                1
            FROM
                `events` `e`
            WHERE
                `e`.`patient_id` = `t`.`PatientId`
                    AND `e`.`start_time` > `t`.`PrescrDate`
                    AND `e`.`EventType` NOT IN (7 , 8)
                    AND `e`.`cal_id` IN (4 , 10)
                    AND `e`.`trt_id` IN (4 , 40)
                    AND LOCATE(CONCAT('id="', `ttteg`.`EquipmentGroupId`, '"'), `e`.`EquipmentMetaInformation`) > 0)))
	group by `t`.`Id`;

This snippet took 0.00 seconds to highlight.

Back to the Entry List or Home.

Delete this entry (admin only).