Demo entry 2323967

additional view

   

Submitted by Alexander on Jul 23, 2015 at 11:10
Language: SQL. Code size: 3.0 kB.

CREATE or replace
    ALGORITHM = MERGE 
    SQL SECURITY DEFINER
  VIEW `therapy_to_event_view` AS
  SELECT 
    `t`.`Id` AS `TherapyId`,
    `e`.`Id` AS `EventId`
    FROM 
         `patient_therapy` `t`
     JOIN events e ON (`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 exists(
                            select id from therapy_type_to_equipment_groups ttteg 
                            where ttteg.TherapyTypeId = t.TherapyTypeId 
                                  AND LOCATE(CONCAT('id="', `ttteg`.`EquipmentGroupId`, '"'), `e`.`EquipmentMetaInformation`) > 0));

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` AND `t`.`TherapyStatus` = 2 AND (`t`.`DeliveryMethod` IS NULL OR `t`.`DeliveryMethod` <> 2)
        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 AND tci.Id is null)      
        
    WHERE
        (NOT (EXISTS( SELECT 1 FROM `therapy_to_event_view` ttev WHERE `ttev`.`TherapyId` = `t`.`Id`)));

This snippet took 0.01 seconds to highlight.

Back to the Entry List or Home.

Delete this entry (admin only).