Demo entry 2335645

report_order_status_view v2

   

Submitted by anonymous on Aug 03, 2015 at 09:40
Language: SQL. Code size: 1.8 kB.

CREATE or replace
    ALGORITHM = UNDEFINED 
    SQL SECURITY DEFINER
VIEW `report_order_status_view` AS
    SELECT 
        `t`.`Id` AS `Id`,
        `p`.`id` AS `PatientId`,
        CONCAT(`p`.`f_name`, ' ', `p`.`l_name`) AS `PatientName`,
        `t`.`PrescrDate` AS `ReferralDate`,
        `t`.`RefPhysicianId` AS `RefPhysicianId`,
        CONCAT(`rp`.`f_name`, ' ', `rp`.`l_name`) AS `RefPhysicianName`,
        `t`.`TherapyTypeId` AS `TherapyTypeId`,
        `sp`.`id` AS `SalesPersonId`,
        `sp`.`FullName` AS `SalesPersonName`,
        `t`.`TherapyStatus` AS `TherapyStatus`,
        `tt`.`Name` AS `TherapyTypeName`,
        `u`.`id` AS `UserId`,
        `u`.`FullName` AS `UserName`,
        `td`.`PriorityLevelType` AS `PriorityLevel`,
        `td`.`HoldReason` AS `HoldReason`,
        `td`.`OtherHoldReason` AS `OtherHoldReason`,
        `p`.`TherapyPhoneCallStatus` AS `TherapyPhoneCallStatus`,
        `t`.`DeliveryMethod` AS `DeliveryMethod`
    FROM
      `patient_therapy` `t`
        JOIN `patients` `p` ON `p`.`id` = `t`.`PatientId` AND NOT `p`.`Archive`
        JOIN `patient_therapy_details` `td` ON `td`.`Id` = `t`.`Id`
        LEFT JOIN `pat_insurance` `pi` ON `pi`.`pid` = `p`.`id` AND `pi`.`ins_typ_id` = 1 AND NOT `pi`.`Archive`
        JOIN `ref_physicians` `rp` ON `t`.`RefPhysicianId` = `rp`.`id`
        LEFT JOIN `ref_phy_offices` `rho` ON `t`.`RefPhysicianOfficeId` = `rho`.`id`
        LEFT JOIN `users` `u` ON `u`.`id` = IF((`t`.`LastModifiedBy` <> NULL), `t`.`LastModifiedBy`, `t`.`CreatedBy`)
        LEFT JOIN `users` `sp` ON `rho`.`sales_person_Id` = `sp`.`id`
        JOIN `therapy_types` `tt` ON `t`.`TherapyTypeId` = `tt`.`Id`
    WHERE
	(`t`.`TherapyStatus` = 2 AND  `pi`.`id` is not null AND `pi`.`VerificationStatus` <> 1 ) 
	OR `t`.`TherapyStatus` <> 2;

This snippet took 0.01 seconds to highlight.

Back to the Entry List or Home.

Delete this entry (admin only).