---
# System prepended metadata

title: '從後台切入的    3:57   4336筆  少了172筆'

---

# 空腹血脂 WebID 1042
![image](https://lh3.googleusercontent.com/u/0/drive-viewer/AKGpihbcJ9BA2PTgGiOrTMrYGmrOT7UuwrsgdgkncFhRhwyjqFB4YdVN_JbGF-Ud1QPSGXXAH-fck2O8KooFM9ZgjK9Anea3FJ-ataM=w1920-h919-rw-v1)

Web ID **1042**

![image](https://lh3.googleusercontent.com/u/0/drive-viewer/AKGpihYQt5FHKuseFUD_C8u2MlBZng74sLWUnhATGmAriDwVoQoO3vhfG2W4-7HPMRVV9NQ8zvtL0TmKiZQzQRs29O3Q-S9JJ0bTBA=w1920-h919)




---

- [ ]  (條件1)有申報醫令代碼前五碼有
    - [ ]  09001 (血清總膽固醇CHOL)+
    - [ ]  09004 (空腹血清中性脂肪TG)+
    - [ ]  09043(高密度脂蛋白膽固醇HDL)三項；
- [ ]  (條件2)或有申報醫令代碼前五碼有
    - [ ]  09001(血清總膽固醇CHOL)+
    - [ ]  09004(空腹血清中性脂肪TG)+
    - [ ]  09044(低密度脂蛋白膽固醇LDL)三項；
- [ ]  (條件3)
    - [ ]  申報醫令代碼21及就醫序號IC21  92筆 vs 105
    - [ ]  或醫令代碼22及就醫序號IC22    79筆 vs 89筆
    - [ ]  或醫令代碼25及就醫序號IC23 0筆
    - [ ]  或醫令代碼27及就醫序號IC24 0筆
    - [ ]  或醫令代碼21+L1001C及就醫序號IC21  (我們只有L1001, B及C都沒有)   192筆
    - [ ]  或醫令代碼25+L1001C及就醫序號IC23   0筆

### Web報表版本原始碼   2023年  4819筆   重前台切入 多了311 筆

```sql
SELECT COUNT (DISTINCT TT.Cht_id) Amt
FROM   (SELECT   DISTINCT BB.Dateseq, BB.Cht_id, COUNT (BB.Price_code) Amt
        FROM     (SELECT DISTINCT CC.Dateseq, CC.Cht_id
                  FROM   Rpatreg CC
                         INNER JOIN (SELECT GG.Cht_id, GG.Dateseq
                                     FROM   Hsp_cm GG INNER JOIN Icd10cm DD ON GG.Cm_code = DD.Icd10cm_code
                                     WHERE      GG.Cln_type = '1'
                                            AND DD.Icd9cm_code LIKE '250%'
                                            AND TRUNC (GG.Cre_date) BETWEEN TO_DATE (:C1, 'YYYY/MM/DD') AND TO_DATE (:C2, 'YYYY/MM/DD')) EE
                              ON     CC.Dateseq = EE.Dateseq
                                 AND CC.Cht_id = EE.Cht_id
                  WHERE      CC.Reg_status <> 'X'
                         AND CC.Cln_date BETWEEN TO_DATE (:C1, 'YYYY/MM/DD') AND TO_DATE (:C2, 'YYYY/MM/DD')) FF
                 INNER JOIN Dpatord BB
                      ON     FF.Dateseq = BB.Dateseq
                         AND FF.Cht_id = BB.Cht_id
        WHERE        BB.Acc_code = '29'
                 AND TRIM (BB.Price_code) IN ('09001', '09004', '09043')
        GROUP BY BB.Dateseq, BB.Cht_id
        UNION
        SELECT   DISTINCT GG.Dateseq, GG.Cht_id, COUNT (GG.Price_code) Amt
        FROM     (SELECT DISTINCT JJ.Dateseq, JJ.Cht_id
                  FROM   Rpatreg JJ
                         INNER JOIN (SELECT KK.Cht_id, KK.Dateseq
                                     FROM   Hsp_cm KK INNER JOIN Icd10cm MM ON KK.Cm_code = MM.Icd10cm_code
                                     WHERE      KK.Cln_type = '1'
                                            AND MM.Icd9cm_code LIKE '250%'
                                            AND TRUNC (KK.Cre_date) BETWEEN TO_DATE (:C1, 'YYYY/MM/DD') AND TO_DATE (:C2, 'YYYY/MM/DD')) PP
                              ON     JJ.Dateseq = PP.Dateseq
                                 AND JJ.Cht_id = PP.Cht_id
                  WHERE      JJ.Reg_status <> 'X'
                         AND JJ.Cln_date BETWEEN TO_DATE (:C1, 'YYYY/MM/DD') AND TO_DATE (:C2, 'YYYY/MM/DD')) QQ
                 INNER JOIN Dpatord GG
                      ON     QQ.Dateseq = GG.Dateseq
                         AND QQ.Cht_id = GG.Cht_id
        WHERE        GG.Acc_code = '29'
                 AND TRIM (GG.Price_code) IN ('09001', '09004', '09044')
        GROUP BY GG.Dateseq, GG.Cht_id) TT
WHERE  TT.Amt = 3
```

## 從前台切入第二版精確版的話很合理  2:16s   4555 只多了47筆

```sql
WITH Tmpview
     AS (SELECT   Oo.Cht_id, Oo.Dateseq, COUNT( Price_code ) AS Pcnt
         FROM     Dpatord Oo
         WHERE        Price_code IN ('09001', '09001     ', '09001E6   ', '09001E7   ', '09001E8   ', '09004', '09004     ', '09043', '09043     ')
                  AND EXISTS
                           (SELECT 1
                            FROM   Rpatreg
                            WHERE      Dateseq = Oo.Dateseq
                                   AND reg_status <> 'X'
                                   AND Cln_date BETWEEN TO_DATE( :c1, 'yyyymmdd' ) AND TO_DATE( :c1, 'yyyymmdd' ))
         GROUP BY Oo.Cht_id, Oo.Dateseq
         UNION
         SELECT   Oo.Cht_id, Oo.Dateseq, COUNT( Price_code ) AS Pcnt
         FROM     Dpatord Oo
         WHERE        Price_code IN ('09001', '09001     ', '09001E6   ', '09001E7   ', '09001E8   ', '09004', '09004     ', '09044', '09044     ')
                  AND EXISTS
                           (SELECT 1
                            FROM   Rpatreg
                            WHERE      Dateseq = Oo.Dateseq
                                   AND reg_status <> 'X'
                                   AND Cln_date BETWEEN TO_DATE( :c1, 'yyyymmdd' ) AND TO_DATE( :c2, 'yyyymmdd' ))
         GROUP BY Oo.Cht_id, Oo.Dateseq)
SELECT DISTINCT Cht_id
FROM   Tmpview
WHERE      Pcnt = 3
       AND Tmpview.Cht_id IN (SELECT Row2 FROM Demo2)
```

# 從後台切入的    3:57   4336筆  少了172筆

已把所有的條件放開了，還是少。join_type,APPLY_FLAG ,NH_STATUS ,CLN_TYPE 等等

```sql
WITH Tmpview
     AS (SELECT   Oo.Cht_id, Oo.Dateseq, COUNT (Price_code) AS Pcnt
         FROM     Onhord Oo
         WHERE        Price_code IN ('09001','09001     ', '09001E6   ','09001E7   ','09001E8   ','09004','09004     ', '09043','09043     ')
                  AND EXISTS
                           (SELECT 1
                            FROM   Onhreg
                            WHERE      Dateseq = Oo.Dateseq
                                   AND Cln_date BETWEEN TO_DATE ('20230101', 'yyyymmdd') AND TO_DATE ('20231231', 'yyyymmdd'))
         GROUP BY Oo.Cht_id, Oo.Dateseq
         UNION
         SELECT   Oo.Cht_id, Oo.Dateseq, COUNT (Price_code) AS Pcnt
         FROM     Onhord Oo
         WHERE        Price_code IN ('09001','09001     ', '09001E6   ','09001E7   ','09001E8   ','09004','09004     ', '09044','09044     ')
                  AND EXISTS
                           (SELECT 1
                            FROM   Onhreg
                            WHERE      Dateseq = Oo.Dateseq
                                   AND Cln_date BETWEEN TO_DATE ('20230101', 'yyyymmdd') AND TO_DATE ('20231231', 'yyyymmdd'))
         GROUP BY Oo.Cht_id, Oo.Dateseq)
SELECT DISTINCT Cht_id
FROM   Tmpview
WHERE  Pcnt = 3 and tmpView.cht_id in (select row2 from demo2) 
```

### 前台符合分子條件 到後台就被轉掉的例子 共219個用戶

```sql
select * from dpatord where dateseq ='2023032110104'
select * from Onhord where dateseq ='2023032110104'

bengo : T121515423620623,2023060810576
bengo : T121531454611001,2023030210512
bengo : T121548422741026,2023053110694
bengo : T121587205561117,2023071311880
bengo : T121603008620121,2023090810966
bengo : T121620821580626,2023070711522
bengo : T121621220640801,2023101311768
bengo : T121633702621020,2023122110426
bengo : T121645622610527,2023073110723
bengo : T121656009610511,2022121510681
bengo : T121657079680220,2023061310692
bengo : T121691008690818,2023091811136
bengo : T121693244650304,2023082910658
bengo : T121696469620830,2023021511902
bengo : T121697304561214,2023021410659
bengo : T121699808580825,2023083011594
bengo : T121725241730324,2023081511234
bengo : T121750673570323,2023102412017
bengo : T121786128640412,2022121610855
bengo : T121807953691006,2023061910146
bengo : T121848534600323,2023063011795
bengo : T121880792670126,2023080411742
bengo : T121885957711017,2023052110159
bengo : T121912673570213,2023101210579
bengo : T121914195690602,2023060610944
bengo : T121923443720925,2023080911579
bengo : T121929472680821,2023061212156
bengo : T121962780700409,2023111612152
bengo : T122001366601224,2022102111662
bengo : T122001366601224,2023070711362
bengo : T122161456640403,2023093010288
bengo : T122195621810808,2023012810608
bengo : T122275473740704,2023021511820
bengo : T122322824740715,2023030811603
bengo : T122322824740715,2023071311144
bengo : T122391285750908,2023090810481
bengo : T122391418751111,2023122811110
bengo : T122450289750313,2023011110761

```