sql

jslin0724 2010-10-21 15:13:19 ‧ 3369 瀏覽

分享至

有一段SQL語法, 已經SELECT 我需要數據資料, 但想進一步依TC004(廠編)個別統計當月進貨筆數, 並且以 TD012 預交日跟TG003 進貨日期倆個日期比較,
就是當 TG003 進貨日期 > TD012 預交日的個別當月逾期筆數

SELECT T1.TC001, T1.TC002, T1.TC003, T1.TC004, T4.TG021, T2.TD012, T3.TH001, T3.TH002, T4.TG003
FROM HT1..PURTC T1
INNER JOIN HT1..PURTD T2
ON T1.TC001 = T2.TD001 AND T1.TC002 = T2.TD002
INNER JOIN HT1..PURTH T3
ON T1.TC001 = T3.TH011 AND T1.TC002 = T3.TH012 AND T2.TD003 = T3.TH013
INNER JOIN HT1..PURTG T4
ON T3.TH001 = T4.TG001 AND T3.TH002 = T4.TG002
WHERE T1.TC001 = '3300' AND T1.TC003 LIKE '201009%'
ORDER BY T1.TC004, T1.TC003

希望得到結果:
年月廠編廠商名稱進貨筆數逾期筆數
201009 00018 銳昌 2 2
201009 00096 宮前 16 7
201009 00107 智峰 4 0
201009 00111 允勝 23 11
201009 00140 東揚 2 2

Looking Ahead on Request
「Wish you all the best」 Thanks a lot ！！

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

richardsuma

iT邦大師 1 級 ‧ 2010-10-21 16:09:20

最佳解答

SELECT T1.TC001, T1.TC002, T1.TC003, T1.TC004, T4.TG021, T2.TD012, T3.TH001, T3.TH002, T4.TG003,COUNT(T1.TC002)
FROM PURTC T1,PURTD T2, PURTH T3,PURTG T4
where T1.TC001 = T2.TD001
AND T1.TC002 = T2.TD002
and T1.TC001 = T3.TH011
AND T1.TC002 = T3.TH012
AND T2.TD003 = T3.TH013
and T3.TH001 = T4.TG001
AND T3.TH002 = T4.TG002
and T1.TC001 = '3300'
AND T1.TC003 LIKE '201009%'
group by T1.TC001, T1.TC002, T1.TC003, T1.TC004, T4.TG021, T2.TD012, T3.TH001, T3.TH002, T4.TG003
having T4.TG003>T2.TD012
ORDER BY T1.TC004, T1.TC003

回應 1
分享
檢舉

jslin0724 iT邦新手 4 級 ‧ 2010-11-25 09:24:35 檢舉

後來參考了richardsuma提供語法再加了以下SELECT 語法完成了謝謝 ~~~

&lt;pre class="c" name="code">SELECT 201009 AS '年月', T1.TC004 AS 廠商編碼, T5.MA002 AS 廠商名稱,
COUNT(T1.TC004) AS 月交貨總筆數,
SUM(CASE WHEN (CAST (T4.TG003 AS NUMERIC(8)) > CAST(T2.TD012 AS NUMERIC(8))) THEN 1
         ELSE 0
    END) AS 月逾期總筆數,
ROUND((CONVERT(DECIMAL(10,2),(COUNT(T1.TC004)) -
       SUM(CASE WHEN (CAST(T4.TG003 AS NUMERIC(8)) > CAST(T2.TD012 AS NUMERIC(8))) THEN 1
           ELSE 0 END)) / COUNT(T1.TC004) * 100), 2) AS 達成率百分比,
CASE WHEN ROUND((CONVERT(DECIMAL(10,2),(COUNT(T1.TC004)) -
       SUM(CASE WHEN (CAST(T4.TG003 AS NUMERIC(8)) > CAST(T2.TD012 AS NUMERIC(8))) THEN 1
           ELSE 0 END)) / COUNT(T1.TC004) * 100), 2) >= 80 THEN 'O'
			ELSE 'X' 
END AS 合格   
FROM HT1..PURTC T1, HT1..PURTD T2, HT1..PURTH T3, HT1..PURTG T4, HT1..PURMA T5

登入發表回應