iT邦幫忙

0

SQL 金額平均分配

問題如下, 公司有4個員工 Tech1,Tech2,Tech3,Tech4 , 每天需要做工程。但並非每次都4 人同行, 請教各位大大,如何在 SQL 平均分配工程金額.如下圖

https://ithelp.ithome.com.tw/upload/images/20210304/20125845XVwqInkadH.png

ID 是工程單號
AMOUNT 是工程總工資
Tech1, Tech2,Tech3,Tech4 是員工

player iT邦大師 1 級 ‧ 2021-03-04 17:24:24 檢舉
甘特圖的應用嗎?
https://zh.wikipedia.org/wiki/%E7%94%98%E7%89%B9%E5%9B%BE

這類的應該都是根據實際工時去攤提成本吧?
3
rogeryao
iT邦大師 4 級 ‧ 2021-03-04 18:15:52
最佳解答

依版主的描述可視為 : 一個工程單號的工程總工資等於 900 元,而且一天做完.
基於以上原則分成兩種狀況 :
A : 單一 Table

-- 工程總工資及參與人員
CREATE TABLE AttendanceFull (
ID int NULL ,             -- 工程單號
Amount int NULL ,         -- 工程總工資
Tech1 nvarchar(20) NULL , -- 員工:Tech1
Tech2 nvarchar(20) NULL , -- 員工:Tech2
Tech3 nvarchar(20) NULL , -- 員工:Tech3
Tech4 nvarchar(20) NULL); -- 員工:Tech4

INSERT INTO AttendanceFull (ID,Amount,Tech1,Tech2,Tech3,Tech4)
VALUES 
(1,900,NULL,NULL,NULL,NULL),
(2,900,NULL,NULL,NULL,N'無同行'),
(3,900,NULL,NULL,N'無同行',N'無同行'),
(4,900,NULL,N'無同行',N'無同行',N'無同行'),
(5,900,N'無同行',N'無同行',N'無同行',N'無同行');
SELECT ID,Amount,ManCounter AS '參與人數',
CASE WHEN Tech1 IS NULL THEN CAST(Amount/ManCounter AS VARCHAR(20)) ELSE N'無同行' END AS 'Tech1',
CASE WHEN Tech2 IS NULL THEN CAST(Amount/ManCounter AS VARCHAR(20)) ELSE N'無同行' END AS 'Tech2',
CASE WHEN Tech3 IS NULL THEN CAST(Amount/ManCounter AS VARCHAR(20)) ELSE N'無同行' END AS 'Tech3',
CASE WHEN Tech4 IS NULL THEN CAST(Amount/ManCounter AS VARCHAR(20)) ELSE N'無同行' END AS 'Tech4'
FROM (
SELECT *,
CASE WHEN Tech1 IS NULL THEN 1 ELSE 0 END +
CASE WHEN Tech2 IS NULL THEN 1 ELSE 0 END +
CASE WHEN Tech3 IS NULL THEN 1 ELSE 0 END +
CASE WHEN Tech4 IS NULL THEN 1 ELSE 0 END AS 'ManCounter'
FROM AttendanceFull
) AS TEMP
ORDER BY ID,Amount

Demo

B : 兩個 Table

--各工程總工資表
CREATE TABLE PrjAmount (
ID int NULL ,     -- 工程單號
Amount int NULL); -- 工程總工資

INSERT INTO PrjAmount (ID,Amount)
VALUES 
(1,900),
(2,900),
(3,900),
(4,900),
(5,900);
--各工程參與人員表
CREATE TABLE Attendance (
ID int NULL ,            -- 工程單號
Eng nvarchar(20) NULL);  -- 參與員工

INSERT INTO Attendance (ID,Eng)
VALUES 
(1,'Tech1'),
(1,'Tech2'),
(1,'Tech3'),
(1,'Tech4'),
(2,'Tech1'),
(2,'Tech2'),
(2,'Tech3'),
(3,'Tech1'),
(3,'Tech2'),
(4,'Tech1');
SELECT ID,Amount,ManCounter AS '參與人數',
MIN(CASE WHEN Eng='Tech1' THEN CAST(AmountAvg AS VARCHAR(20)) ELSE N'無同行' END) AS 'Tech1',
MIN(CASE WHEN Eng='Tech2' THEN CAST(AmountAvg AS VARCHAR(20)) ELSE N'無同行' END) AS 'Tech2',
MIN(CASE WHEN Eng='Tech3' THEN CAST(AmountAvg AS VARCHAR(20)) ELSE N'無同行' END) AS 'Tech3',
MIN(CASE WHEN Eng='Tech4' THEN CAST(AmountAvg AS VARCHAR(20)) ELSE N'無同行' END) AS 'Tech4'
FROM (
SELECT A.ID,A.Amount,B.Eng,
CASE WHEN Eng IS NULL THEN 0 ELSE
COUNT(1) OVER(PARTITION BY A.ID) END AS ManCounter,
A.Amount/COUNT(1) OVER(PARTITION BY A.ID) AS AmountAvg
FROM PrjAmount AS A
LEFT JOIN Attendance AS B ON B.ID=A.ID
) AS TempA
GROUP BY ID,Amount,ManCounter
ORDER BY ID,Amount

Demo

bs512 iT邦新手 5 級 ‧ 2021-03-16 10:49:46 檢舉

多謝 Royeryao 大大於分配工資之解答。

現在建成一個
Databases Name : dbEE
Table Name : SB_HEADER
https://ithelp.ithome.com.tw/upload/images/20210316/20125845M75PKS4dDQ.jpg

使用大大之解答, 並測試可成功得到所需結果。

SELECT WORK_ORDER_NO,TECH1_NAME,TECH2_NAME,TECH3_NAME,TECH4_NAME,JOB_POINT_FINAL_POINT,MANCOUNTER AS 'No of Tech',
CASE WHEN TECH1_NAME IS NOT NULL THEN CAST(JOB_POINT_FINAL_POINT/MANCOUNTER AS VARCHAR(20)) ELSE NULL END AS JOB_POINT_TECH1_POINT,
CASE WHEN TECH2_NAME IS NOT NULL THEN CAST(JOB_POINT_FINAL_POINT/MANCOUNTER AS VARCHAR(20)) ELSE NULL END AS JOB_POINT_TECH2_POINT,
CASE WHEN TECH3_NAME IS NOT NULL THEN CAST(JOB_POINT_FINAL_POINT/MANCOUNTER AS VARCHAR(20)) ELSE NULL END AS JOB_POINT_TECH3_POINT,
CASE WHEN TECH4_NAME IS NOT NULL THEN CAST(JOB_POINT_FINAL_POINT/MANCOUNTER AS VARCHAR(20)) ELSE NULL END AS JOB_POINT_TECH4_POINT
FROM ( SELECT *,
CASE WHEN TECH1_NAME IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN TECH2_NAME IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN TECH3_NAME IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN TECH4_NAME IS NOT NULL THEN 1 ELSE 0 END AS 'MANCOUNTER'
FROM SB_HEADER ) AS TEMP

現想用以 SQL Triggers 觸發程序來做,請教應如何撰寫。

rogeryao iT邦大師 4 級 ‧ 2021-03-16 11:22:55 檢舉
1
fuzzylee1688
iT邦新手 1 級 ‧ 2021-03-04 17:46:04

參考看看..

# 建立臨時表單
create table #saltable (
   A char(1)  -- 1:同行
  ,B char(1)
  ,C char(1)
  ,D char(1)
  ,Amt dec(18,2)
)

# 填入同行資訊及 工程總工資
insert into #saltable 
values (1,1,1,1,900)
values (1,null,1,1,800)
values (1,null,null,1,700)

# 取得同行平均工資
select convert(decimal(18,1), A*(Amt/TotalNum)) A_Sal
      ,convert(decimal(18,1), B*(Amt/TotalNum)) B_Sal
	  ,convert(decimal(18,1), C*(Amt/TotalNum)) C_Sal
	  ,convert(decimal(18,1), D*(Amt/TotalNum)) D_Sal
	  ,Amt
	  from (
       select coalesce(A,0) + coalesce(B,0) + coalesce(C,0) + coalesce(D,0) TotalNum, A.* 
         from #saltable A
  ) BB

https://ithelp.ithome.com.tw/upload/images/20210304/20107201CdBAhXEnD0.png

2
純真的人
iT邦高手 1 級 ‧ 2021-03-04 17:54:28

練練手..@@

declare @tmp table(
	ID int
	,AMOUNT int
	,Tech1 int
	,Tech2 int
	,Tech3 int
	,Tech4 int 
)

insert into @tmp
values(1,900,1,1,1,1)
,(2,900,1,1,1,0)
,(3,900,1,1,0,0)

select AMOUNT
,(case when Tech1 = 1 then AMOUNT / (Tech1+Tech2+Tech3+Tech4) else 0 end) Tech1
,(case when Tech2 = 1 then AMOUNT / (Tech1+Tech2+Tech3+Tech4) else 0 end) Tech2
,(case when Tech3 = 1 then AMOUNT / (Tech1+Tech2+Tech3+Tech4) else 0 end) Tech3
,(case when Tech4 = 1 then AMOUNT / (Tech1+Tech2+Tech3+Tech4) else 0 end) Tech4
from @tmp

https://ithelp.ithome.com.tw/upload/images/20210304/200613699qpsBTWXDk.png

我要發表回答

立即登入回答