0

excel 年資人數統計計算問題

3CBrian iT邦新手 1 級 ‧ 2019-08-16 11:59:45 檢舉
...
3CBrian iT邦新手 1 級 ‧ 2019-08-16 12:02:20 檢舉
...

This transfer is no longer available.
The transfer "2019-08-16 - My Transfer"is no longer available either because it has expired, has reached the maximum number of downloads, or was deleted.

These files have been deleted from our servers. If you still need these files, please contact the original sender and request another transfer.

https://mega.nz/#!tI4ACSjA!T9ytpyE3OjCMMLWvo-9eXKZveTB_ab1aim-v_biEub0

jasonlin268 iT邦研究生 5 級 ‧ 2019-08-16 15:58:19 檢舉

3 個回答

3
jasonlin268
iT邦研究生 5 級 ‧ 2019-08-16 14:57:44

3CBrian iT邦新手 1 級 ‧ 2019-08-16 15:28:20 檢舉

3
japhenchen
iT邦高手 1 級 ‧ 2019-08-16 12:17:59

``````CREATE  PROCEDURE [dbo].[SP_GetEmpYears]
AS
BEGIN
DECLARE @TOYEAR AS VARCHAR(10)
SET @TOYEAR = CAST(YEAR(GETDATE()) AS VARCHAR)+ '/4/30'  -- 實際當年的4月30日止
DECLARE @LASTDAY AS DATETIME
SET @LASTDAY = CONVERT(VARCHAR(10), @TOYEAR ,111)

DECLARE @MINYEARS AS INT
SET @MINYEARS = 5   --最少五年以上，五一勞動箱時要頒資深員工獎用的

SELECT
EMPNO 工號,
EMPCNAME 姓名,
HIREDDATE 到職日 ,
floor(CAST(DATEDIFF(MONTH, HIREDDATE,@LASTDAY) AS FLOAT) /12) 實際年資,
floor(CAST(DATEDIFF(MONTH, HIREDDATE,@LASTDAY) AS FLOAT) /60)*5 五的倍數
FROM HR.DBO.EMPLOYEE
WHERE
QUITDATE IS  NULL AND DATEDIFF(YEAR, HIREDDATE,@LASTDAY)>@MINYEARS
and CAST(floor(CAST(DATEDIFF(MONTH, HIREDDATE,@LASTDAY) AS FLOAT) /12) AS INT) %5 = 0
ORDER BY HIREDDATE
END

``````

`Datedif`還是可以用。
`Datedif`算是隱藏版函數，Excel內建的說明中找不到，要去微軟的網站https://support.office.com/zh-tw/article/datedif-函數-25dba1a4-2812-480b-84dd-8b32a451b35c 才有說明。

djhs115z4 iT邦新手 5 級 ‧ 2019-08-19 09:31:14 檢舉

Y、M、D 要加上""
"Y"

A1格是 =now()

djhs115z4 iT邦新手 5 級 ‧ 2019-08-19 10:39:20 檢舉

DATEDIF(start_date,end_date,unit)
Start_date大於End_date，則結果為#NUM ！

2