iT邦幫忙

0

MSSQL SELECT近一年進貨單價後 作漲跌幅比較

匿名 2012-03-07 11:42:134477 瀏覽

目前已有寫出列出近一年進貨單價(非每月都會進貨,程式碼如下)
顯示畫面如圖1,希望能加入與上一筆單價比較後的漲跌幅顯示畫面如圖2
請問各位大大 該用什麼語法修改

SELECT 進貨單.品號 '品號' ,
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 1 THEN 進貨單.單價 END) AS VARCHAR),'') AS '01',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 2 THEN 進貨單.單價 END) AS VARCHAR),'') AS '02',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 3 THEN 進貨單.單價 END) AS VARCHAR),'') AS '03',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 4 THEN 進貨單.單價 END) AS VARCHAR),'') AS '04',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 5 THEN 進貨單.單價 END) AS VARCHAR),'') AS '05',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 6 THEN 進貨單.單價 END) AS VARCHAR),'') AS '06',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 7 THEN 進貨單.單價 END) AS VARCHAR),'') AS '07',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 8 THEN 進貨單.單價 END) AS VARCHAR),'') AS '08',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 9 THEN 進貨單.單價 END) AS VARCHAR),'') AS '09',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 10 THEN 進貨單.單價 END) AS VARCHAR),'') AS '10',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 11 THEN 進貨單.單價 END) AS VARCHAR),'') AS '11',
ISNULL(CAST(MAX(CASE DATEPART(mm,進貨單.進貨日期) WHEN 12 THEN 進貨單.單價 END) AS VARCHAR),'') AS '12'
FROM 進貨單
WHERE 進貨單.進貨日期 < (CONVERT(char(8),DATEADD(d,-DAY(getdate())+1,getdate()),112))
      AND 進貨單.進貨日期 >= (CONVERT(char(8),DATEADD(MM,DATEDIFF(MM,0,DATEADD(MM,-12,getdate())),0),112))      
GROUP BY 進貨單.品號
ORDER BY 進貨單.品號


2 個回答

0
kaowoei
iT邦研究生 4 級 ‧ 2012-03-22 15:02:27
最佳解答

若是MS SQL 2005 以後,可以試著參考PIVOT的語法。

6
Albert
iT邦高手 1 級 ‧ 2012-03-07 21:50:19

這要用 三層 Sub Select 就好了

這是我們的 統計表 很像你要的

我們是全球最大 開放原碼 ERP
Skype: Adempiere/Compiere
技術轉移顧問 Albert

你有更困難的題目嗎 ?

SELECT cc.ID,cc.YY,
cc.M01,
cc.M02,CASE NVL(cc.X01,0) WHEN 0 THEN NULL ELSE ROUND(cc.M02/cc.X01,2) END AS P02,
cc.M03,CASE NVL(cc.X02,0) WHEN 0 THEN NULL ELSE ROUND(cc.M03/cc.X02,2) END AS P03,
cc.M04,CASE NVL(cc.X03,0) WHEN 0 THEN NULL ELSE ROUND(cc.M04/cc.X03,2) END AS P04,
cc.M05,CASE NVL(cc.X04,0) WHEN 0 THEN NULL ELSE ROUND(cc.M05/cc.X04,2) END AS P05,
cc.M06,CASE NVL(cc.X05,0) WHEN 0 THEN NULL ELSE ROUND(cc.M06/cc.X05,2) END AS P06
FROM (
SELECT bb.ID, bb.YY,
bb.M01,bb.M02,bb.M03,bb.M04,bb.M05,bb.M06,bb.M07,bb.M08,bb.M09,bb.M10,bb.M11,bb.M12,
NVL(bb.M12,NVL(bb.M11,NVL(bb.M10,NVL(bb.M09,NVL(bb.M08,NVL(bb.M07,NVL(bb.M06,NVL(bb.M05,NVL(bb.M04,NVL(bb.M03,NVL(bb.M02,NVL(bb.M01,0)))))))))))) AS X12,
NVL(bb.M11,NVL(bb.M10,NVL(bb.M09,NVL(bb.M08,NVL(bb.M07,NVL(bb.M06,NVL(bb.M05,NVL(bb.M04,NVL(bb.M03,NVL(bb.M02,NVL(bb.M01,0))))))))))) AS X11,
NVL(bb.M10,NVL(bb.M09,NVL(bb.M08,NVL(bb.M07,NVL(bb.M06,NVL(bb.M05,NVL(bb.M04,NVL(bb.M03,NVL(bb.M02,NVL(bb.M01,0)))))))))) AS X10,
NVL(bb.M09,NVL(bb.M08,NVL(bb.M07,NVL(bb.M06,NVL(bb.M05,NVL(bb.M04,NVL(bb.M03,NVL(bb.M02,NVL(bb.M01,0))))))))) AS X09,
NVL(bb.M08,NVL(bb.M07,NVL(bb.M06,NVL(bb.M05,NVL(bb.M04,NVL(bb.M03,NVL(bb.M02,NVL(bb.M01,0)))))))) AS X09,
NVL(bb.M07,NVL(bb.M06,NVL(bb.M05,NVL(bb.M04,NVL(bb.M03,NVL(bb.M02,NVL(bb.M01,0))))))) AS X07,
NVL(bb.M06,NVL(bb.M05,NVL(bb.M04,NVL(bb.M03,NVL(bb.M02,NVL(bb.M01,0)))))) AS X06,
NVL(bb.M05,NVL(bb.M04,NVL(bb.M03,NVL(bb.M02,NVL(bb.M01,0))))) AS X05,
NVL(bb.M04,NVL(bb.M03,NVL(bb.M02,NVL(bb.M01,0)))) AS X04,
NVL(bb.M03,NVL(bb.M02,NVL(bb.M01,0))) AS X03,
NVL(bb.M02,NVL(bb.M01,0)) AS X02,
NVL(bb.M01,0) AS X01
FROM (
SELECT aa.ID, aa.YY,
SUM(CASE aa.MM WHEN '01' THEN aa.Qty ELSE NULL END) AS M01,

Albert iT邦高手 1 級 ‧ 2012-03-08 17:52:59 檢舉

你這不像學生作業
因為這題目老師也不會
這是企業用 ?
我們顧問很樂意為你服務
請 Skype: Adempiere/Compiere

我要發表回答

立即登入回答