各位好,想請問大家 最近在幫忙做 銷貨比率的計算,但卡住了想上來詢問一下
目前我做出來的查詢長這樣,我想要在這其中在加入一欄的銷貨比率,不過這個銷貨比率需要我算出來的總計去做運算 銷貨金額/銷貨總計金額(銷貨金額的最後一個值) ,想請問有沒有什麼方法可以去抓到銷貨金額那一欄的最後一個值好方便去做運算的動作,謝謝各位。
在這邊附上目前所使用的程式碼:
declare @todayYM varchar(6)
set @todayYM = convert(varchar(6),getdate()-8,112) /* 到當月8號前都只可以查詢到上個月的資料 */
SELECT 客戶簡稱
,銷貨數量
,銷貨金額
,銷貨成本
,銷貨毛利
,銷貨毛利率
,排名
FROM (
SELECT *
FROM (
SELECT [MA002] AS 客戶簡稱
,convert(int,SUM([TH008])) AS 銷貨數量
,convert(int,SUM([TH037])) AS 銷貨金額
,convert(int,(SUM([TH008] * [LA012]))) AS 銷貨成本
,convert(int,(SUM([TH037]) - SUM([TH008] * [LA012]))) AS 銷貨毛利
,(SUM([TH037]) - SUM([TH008] * [LA012])) / SUM([TH037]) AS 銷貨毛利率
,RANK() OVER(order by SUM([TH037]) DESC) AS 排名
FROM (
(
... AS G
FULL JOIN ...
)
FULL JOIN ...
)
FULL JOIN ...
WHERE ...
GROUP BY ...
) t
UNION ALL
SELECT '總計'
,NULL
,SUM(銷貨金額)
,SUM(銷貨成本)
,SUM(銷貨毛利)
,NULL
,NULL
FROM (
SELECT [MA002] AS 客戶簡稱
,convert(int,SUM([TH008])) AS 銷貨數量
,convert(int,SUM([TH037])) AS 銷貨金額
,convert(int,(SUM([TH008] * [LA012]))) AS 銷貨成本
,convert(int,(SUM([TH037]) - SUM([TH008] * [LA012]))) AS 銷貨毛利
,(SUM([TH037]) - SUM([TH008] * [LA012])) / SUM([TH037]) AS 銷貨毛利率
,RANK() OVER(order by SUM([TH037]) DESC) AS 排名
FROM (
(
... AS G
FULL JOIN ...
)
FULL JOIN ...
)
FULL JOIN ...
WHERE ...
GROUP BY ...
) s
) l
上面程式碼做出來的查詢就是我第一張
CREATE TABLE #table(
客戶簡稱 NVARCHAR(20)
,銷貨數量 INT
,銷貨金額 DECIMAL(15,2)
,銷貨成本 DECIMAL(15,2)
,銷貨毛利 DECIMAL(15,2)
,銷貨毛利率 DECIMAL(15,6)
,排名 INT
)
INSERT INTO #table(客戶簡稱,銷貨數量,銷貨金額,銷貨成本,銷貨毛利,銷貨毛利率,排名)
VALUES('AA','978',2990000,1930000,1060000,0.353532,1)
INSERT INTO #table(客戶簡稱,銷貨數量,銷貨金額,銷貨成本,銷貨毛利,銷貨毛利率,排名)
VALUES('AA','978',2990000,1930000,1060000,0.353532,1)
INSERT INTO #table(客戶簡稱,銷貨數量,銷貨金額,銷貨成本,銷貨毛利,銷貨毛利率,排名)
VALUES('BB','978',2990000,1930000,1060000,0.353532,1)
SELECT
客戶簡稱
,銷貨數量
,銷貨金額
,銷貨成本
,銷貨總計金額
,銷貨金額/銷貨總計金額 AS 銷貨金額比率
FROM (
SELECT
客戶簡稱
,SUM(銷貨數量) AS 銷貨數量
,SUM(銷貨金額) AS 銷貨金額
,SUM(銷貨成本) AS 銷貨成本
,(
SELECT
SUM(銷貨金額)
FROM #table
) AS 銷貨總計金額
FROM #table
GROUP BY 客戶簡稱,銷貨數量,銷貨金額,銷貨成本
)AS TEMP
DROP TABLE #table
大概類似這樣...可以參考看看。
不好意思文章忘記補充,我的銷貨金額其實是
SELECT [MA002] AS 客戶簡稱
,convert(int,SUM([TH008])) AS 銷貨數量
,convert(int,SUM([TH037])) AS 銷貨金額
,convert(int,(SUM([TH008] * [LA012]))) AS 銷貨成本
,convert(int,(SUM([TH037]) - SUM([TH008] * [LA012]))) AS 銷貨毛利
,(SUM([TH037]) - SUM([TH008] * [LA012])) / SUM([TH037]) AS 銷貨毛利率
FROM (
這樣所先做出來的,所以可能沒有辦法一開始就直接做一個
SUM起來的銷貨總計金額.
文章有附上完整的程式碼了
可以的話麻煩再幫我檢查一下有沒有哪裡錯誤嗎.
感謝您!!
declare @todayYM varchar(6)
set @todayYM = convert(varchar(6),getdate()-8,112) /* 到當月8號前都只可以查詢到上個月的資料 */
CREATE TABLE #table(
客戶簡稱 NVARCHAR(20)
,銷貨數量 INT
,銷貨金額 DECIMAL(15,2)
,銷貨成本 DECIMAL(15,2)
,銷貨毛利 DECIMAL(15,2)
,銷貨毛利率 DECIMAL(15,6)
,排名 INT
)
INSERT INTO #table (客戶簡稱,銷貨數量,銷貨金額,銷貨成本,銷貨毛利,銷貨毛利率,排名)
SELECT 客戶簡稱
,銷貨數量
,銷貨金額
,銷貨成本
,銷貨毛利
,銷貨毛利率
,排名
FROM (
SELECT
*
FROM (
SELECT [MA002] AS 客戶簡稱
,convert(int,SUM([TH008])) AS 銷貨數量
,convert(int,SUM([TH037])) AS 銷貨金額
,convert(int,(SUM([TH008] * [LA012]))) AS 銷貨成本
,convert(int,(SUM([TH037]) - SUM([TH008] * [LA012]))) AS 銷貨毛利
,(SUM([TH037]) - SUM([TH008] * [LA012])) / SUM([TH037]) AS 銷貨毛利率
,RANK() OVER(order by SUM([TH037]) DESC) AS 排名
FROM (
(
... AS G
FULL JOIN ...
)
FULL JOIN ...
)
FULL JOIN ...
WHERE ...
GROUP BY ...
) t
UNION ALL
SELECT '總計'
,NULL
,SUM(銷貨金額)
,SUM(銷貨成本)
,SUM(銷貨毛利)
,NULL
,NULL
FROM (
SELECT [MA002] AS 客戶簡稱
,CONVERT(int,SUM([TH008])) AS 銷貨數量
,CONVERT(int,SUM([TH037])) AS 銷貨金額
,CONVERT(int,(SUM([TH008] * [LA012]))) AS 銷貨成本
,CONVERT(int,(SUM([TH037]) - SUM([TH008] * [LA012]))) AS 銷貨毛利
,(SUM([TH037]) - SUM([TH008] * [LA012])) / SUM([TH037]) AS 銷貨毛利率
,RANK() OVER(order by SUM([TH037]) DESC) AS 排名
FROM (
(
... AS G
FULL JOIN ...
)
FULL JOIN ...
)
FULL JOIN ...
WHERE ...
GROUP BY ...
) s
) l
SELECT
客戶簡稱
,銷貨數量
,銷貨金額
,銷貨成本
,銷貨總計金額
,銷貨金額/銷貨總計金額 AS 銷貨金額比率
FROM (
SELECT
客戶簡稱
,SUM(銷貨數量) AS 銷貨數量
,SUM(銷貨金額) AS 銷貨金額
,SUM(銷貨成本) AS 銷貨成本
,(
SELECT
SUM(銷貨金額)
FROM #table
) AS 銷貨總計金額
FROM #table
GROUP BY 客戶簡稱,銷貨數量,銷貨金額,銷貨成本
)AS TEMP
DROP TABLE #table
大概猜個八成這樣寫
CREATE TABLE #table
↑將你目前算完的結果,先存在這個資料表中
在去做運算 銷貨總計金額的動作。
給的資料比較有限,目前暫時只能想到這個辦法。
銷貨金額/SUM(銷貨金額) 然侯gorup by 進去~~?
我有這樣下過設定,不過他會出錯,錯誤是我沒有對銷貨金額GROUP BY到,可是我對銷貨金額 GROUP BY 的時候,他會把每筆銷貨金額除以自己的銷貨金額,所以會有銷貨比率都會是1的情況發生。
文章有附上完整的程式碼了
可以的話麻煩再幫我檢查一下有沒有哪裡錯誤嗎.
感謝您!!
嗯...基本上我會用left join 你下面那一大串建出來的虛表
大概像這樣
left join
(SELECT '總計'as 客戶簡稱,NULL as qq1,SUM(銷貨金額) as 銷貨金額總計,NULL as qq2,SUM(銷貨成本)as qq3,SUM(銷貨毛利)as qq4,NULL as qq6,NULL as qq7 from #table)a2
on a2.客戶簡稱 = '總計'
然後上面select 銷貨金額/a2.銷貨金額總計