SQL หาจำนวนวันนอน Mbase_data

SELECT DISTINCT a.VISIT_ID, b.HN, a.ADM_DT, a.ADM_ID ,a.DSC_DT, a.RW, a.DRG, a.ADJRW , b.INSCL, u.DATE_ABORT,u.HOSPMAIN,
((to_days(a.DSC_DT)*24)- (to_days(a.ADM_DT)*24))/24 AS DAYS, abs((time_to_sec(a.DSC_DT)/3600) - (time_to_sec(a.ADM_DT)/3600)) as Times,
CASE
WHEN abs((time_to_sec(a.DSC_DT)/3600) - (time_to_sec(a.ADM_DT)/3600))>= 6  THEN '1'
WHEN abs((time_to_sec(a.DSC_DT)/3600) - (time_to_sec(a.ADM_DT)/3600))< 6  THEN '0'
END AS HOUR
FROM ipd_reg a
INNER JOIN opd_visits b on a.VISIT_ID = b.VISIT_ID AND b.IS_CANCEL = 0 AND b.INSCL BETWEEN '03' AND '04'
INNER JOIN cid_hn c ON b.HN = c.HN
INNER JOIN population p ON c.CID = p.CID 
INNER JOIN uc_inscl u ON p.CID = u.CID
INNER JOIN hosp_nhso_new n ON u.HOSPMAIN = n.HOSP_ID
INNER JOIN hosp3400 h ON u.HOSPMAIN = h.HOSP_ID AND u.HOSPMAIN = '10953'
WHERE a.ADM_DT BETWEEN @date1 AND @date2
AND a.IS_CANCEL = 0
AND (u.date_abort >= date(b.REG_DATETIME) or day(u.date_abort)=0)
AND a.rw < 1.6
AND a.DSC_DT <> 0 GROUP BY a.VISIT_ID

SQL mbase การคำนวนวันนอนและค่าRW

SET @date1='2017.10.01 00:01';
SET @date2='2017.12.30 23:59';

SELECT 'ผู้ป่วยในRW1.2-1.8' as 'สิทธิ์UCนอกอำเภอในจังหวัด' ,COUNT(s.HN) as visits,SUM(s.DAYS+s.HOUR)
FROM
(SELECT DISTINCT a.VISIT_ID, b.HN, a.ADM_DT, a.ADM_ID ,a.DSC_DT, a.RW, a.DRG, a.ADJRW , b.INSCL, u.DATE_ABORT,u.HOSPMAIN,
((to_days(a.DSC_DT)*24)- (to_days(a.ADM_DT)*24))/24 AS DAYS, abs((time_to_sec(a.DSC_DT)/3600) - (time_to_sec(a.ADM_DT)/3600)) as Times,
CASE
WHEN abs((time_to_sec(a.DSC_DT)/3600) - (time_to_sec(a.ADM_DT)/3600))>= 6  THEN '1'
WHEN abs((time_to_sec(a.DSC_DT)/3600) - (time_to_sec(a.ADM_DT)/3600))< 6  THEN '0'
END AS HOUR
FROM ipd_reg a
INNER JOIN opd_visits b on a.VISIT_ID = b.VISIT_ID AND b.IS_CANCEL = 0 AND b.INSCL BETWEEN '03' AND '04'
INNER JOIN cid_hn c ON b.HN = c.HN
INNER JOIN population p ON c.CID = p.CID 
INNER JOIN uc_inscl u ON p.CID = u.CID
INNER JOIN hosp_nhso_new n ON u.HOSPMAIN = n.HOSP_ID
INNER JOIN hosp3400 h ON u.HOSPMAIN = h.HOSP_ID AND u.HOSPMAIN = '10953'
WHERE a.ADM_DT BETWEEN @date1 AND @date2
AND a.IS_CANCEL = 0
AND (u.date_abort >= date(b.REG_DATETIME) or day(u.date_abort)=0)
AND a.rw < 1.6
AND a.DSC_DT <> 0 GROUP BY a.VISIT_ID) as s

op nhso หน้าเขียว

http://op.nhso.go.th/op/main/MainWebAction.do
541303359577   6213033241622