Changes between Version 37 and Version 38 of AdvancedReports

Timestamp:

07/22/24 14:57:49 (6 months ago)

Author:

184006

Comment:

--

AdvancedReports

@@ -309 +309 @@
   avg_sales_quantity DESC;
 }}}
+=== За секој миленик кој болести се дијагностицирани, какви лекови и терапија е препишена.
+{{{
+WITH PetDiagnosis AS (
+    SELECT
+        p.id AS pet_id,
+        p.name AS pet_name,
+        d.description AS diagnosis_description
+    FROM
+        pets p
+    JOIN
+        diagnostics_established_pets dep ON p.id = dep.id_pets
+    JOIN
+        diagnostics d ON dep.id_diagnostics = d.id
+),
+PetMedications AS (
+    SELECT
+        p.id AS pet_id,
+        m.name AS medicine_name,
+        m.description AS medicine_description
+    FROM
+        pets p
+    JOIN
+        therapy_takes_pets ttp ON p.id = ttp.id_pets
+    JOIN
+        therapy t ON ttp.id_therapy = t.id
+    JOIN
+        medecines m ON t.diagnosticsID = m.diagnosticsID
+),
+PetTherapy AS (
+    SELECT
+        p.id AS pet_id,
+        t.description AS therapy_description,
+        t.appoitmentDate AS therapy_date
+    FROM
+        pets p
+    JOIN
+        therapy_takes_pets ttp ON p.id = ttp.id_pets
+    JOIN
+        therapy t ON ttp.id_therapy = t.id
+)
+
+SELECT
+    pd.pet_id,
+    pd.pet_name,
+    pd.diagnosis_description,
+    pm.medicine_name,
+    pm.medicine_description,
+    pt.therapy_description,
+    pt.therapy_date
+FROM
+    PetDiagnosis pd
+LEFT JOIN
+    PetMedications pm ON pd.pet_id = pm.pet_id
+LEFT JOIN
+    PetTherapy pt ON pd.pet_id = pt.pet_id
+ORDER BY
+    pd.pet_id, pt.therapy_date;
+
+}}}