# 空腹血脂 WebID 1042
Web ID **1042**
---
- [ ] (條件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
```
Sign in with Wallet
Connect another wallet