用于显示特定月份的相应工作日，旅行日和休假日的脚本 [英] Script for displaying respective WORK days, TRAVEL days and LEAVE days for a particular month
本文介绍了用于显示特定月份的相应工作日，旅行日和休假日的脚本的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这是可用数据的方式
EmployeeName | EmployeeNo |输入| StartDate | EndDate
Dumitru | M1975012200006 |旅行| 06/03/2013 | 06/03/2013
Mottaghi | M1975040500009 |旅行| 06/17/2013 | 06/17/2013
Alvarado | M1986092400012 |旅行| 06/03/2013 | 06/03/2013
Dehghan | M1975111100029 |旅行| 06/15/2013 | 06/15/2013
Dumitru | M1975012200006 |工作| 06/04/2013 | 07/15/2013
Mottaghi | M1975040500009 |工作| 06/18/2013 | 09/01/2013
Alvarado | M1986092400012 |工作| 06/04/2013 | 07/15/2013
Dehghan | M1975111100029 |工作| 06/16/2013 | 07/25/2013
Dumitru | M1975012200006 |离开| 07/17/2013 | 09/01/2013
Mottaghi | M1975040500009 |离开| 09/03/2013 | 10/15/2013
Alvarado | M1986092400012 |离开| 07/17/2013 | 09/01/2013
Dehghan | M1975111100029 |离开| 07/27/2013 | 09/02/2013
我希望逐列显示一个月的所有日子。
预期产量如下
六月月时间表
员工姓名 - 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
Dumitru，Georg - TWWWWWWWWWWWWWWWWWWWLLLLLLLLLLLLLLLLLLLL $ L $ T - 代表TRAVEL天; W - 代表工作日; L - 代表LEAVE天;
我很感激这方面的任何帮助。提前谢谢解决方案
看看下面的例子：
- 声明临时表以存储示例数据
DECLARE @ tmp TABLE （EmployeeName VARCHAR （ 30 ），EmployeeNo VARCHAR （ 30 ），[类型] VARCHAR （ 30 ），StartDate DATETIME ，EndDate DATETIME ）
- 将数据插入临时表
INSERT INTO @ tmp （EmployeeName，EmployeeNo，[ Type ]，StartDate，EndDate）
SELECT ' Dumitru'， ' M1975012200006'，' TRAVEL'，' 06/03/2013'， ' 06/03/2013'
UNION ALL SELECT ' Mottaghi '，' M1975040500009'，' TRAVEL'，' 06/17/2013 ，' 06/17/2013'
UNION ALL SELECT ' Alvarado'，' M1986092400012' ，' TRAVEL'，' 06/03/2013'，' 06/03/2013'
UNION ALL SELECT ' Dehghan'，' M1975111100029'，' TRAVEL'，< span class =code-string>' 06/15/2013'，' 06/15 / 2013'
UNION ALL SELECT ' Dumitru'，' M1975012200006'，' WORK'，' 06/04/2013'，' 07/15/2013'
UNION ALL SELECT ' Mottaghi'，' M1975040500009'，' WORK'，' 06/18/2013'，' 09/01/2013'
UNION 所有 SELECT ' Alvarado' ，' M1986092400012'，' WORK'，' 06/04/2013' ，' 2013年7月15日'
UNION ALL SELECT ' Dehghan'，' M1975111100029'，' WORK'，' 06/16/2013' ，' 07/25/2013'
UNION ALL SELECT ' Dumitru'，' M1975012200006'，' LEAVE'，' 07/17/2013'，' 09/01 / 2013'
UNION 所有 SELECT ' Mottaghi'，' M1975040500009 '，' LEAVE'，' 09/03/2013'，' 10 / 15/2013'
UNION ALL SELECT ' Alvarado'， ' M1986092400012'，' LEAVE'，' 07/17/2013'， ' 09/01/2013'
UNION ALL SELECT ' Dehghan '，' M1975111100029'，' LEAVE'， ' 07/27/2013'，' 09/02/2013'
IF OBJECT_ID （N ' ＃pvtsrc'，N ' U'） IS NOT NULL
DROP TABLE #pvtsrc
- 目的地表：枢轴来源
CREATE TABLE #pvtsrc（EmployeeName VARCHAR （ 30 ），EmployeeNo VARCHAR （ 30 ）， TypeID VARCHAR （ 1 ），CurrDate DATETIME ）
- 每天和员工的枚举类型
- 插入目标表
; WITH 天 AS
（
- 初始
SELECT EmployeeName，EmployeeNo， LEFT （[ 类型]， 1 ） AS TypeID， StartDate，EndDate，StartDate AS CurrDate
FROM @ tmp
UNION ALL
SELECT EmployeeName，EmployeeNo，TypeID，StartDate，EndDate，DATEADD（dd， 1 ，CurrDate） AS CurrDate
FROM 天数
< span class =code-keyword> WHERE DATEADD（dd， 1 ，CurrDate）< = EndDate
）
< span class =code-keyword> INSERT INTO #pvtsrc（EmployeeName，EmployeeNo，TypeID，CurrDate）
SELECT EmployeeName，EmployeeNo，TypeID，CurrDate
FROM 天数
ORDER BY EmployeeName，CurrDate
- 声明需要的变量for pivot
DECLARE @cols VARCHAR （ 1000 ）
DECLARE @dt VARCHAR （ 2000 ）
DECLARE @ pt VARCHAR （ 4000 ）
DECLARE @ ft VARCHAR （MAX）
DECLARE @ startDay < span class =code-keyword> DATETIME
DECLARE @ endDay < span class =code-keyword> DATETIME
DECLARE @ currDay DATETIME
- 设置日期范围
SET @ startDay = ' 2013-06-01'
SET @ endDay = ' 2013-06-30'
SET @ currDay = @ startDay
- set columns collection（ 1-28 / 31）
SET @cols = ' '
WHILE （ @currDay < = @ endDay ）
BEGIN
< span class =code-keyword> SET @cols = @cols + ' [' + CONVERT （ VARCHAR （ 2 ），DAY（ @ currDay ））+ ' ]，'
SET @ currDay = DATEADD（dd， 1 ， @ currDay ）
END
SET @cols = LEFT （ @cols ，LEN（ @ COLS ） - 1）
- 设置数据源
SET @ dt = N ' SELECT EmployeeName，EmployeeNo，TypeID，DAY（CurrDate）AS xDay
FROM #pvtsrc
WHERE CurrDate BETWEEN''' + CONVERT （ VARCHAR （ 10 ）， @ startDay ， 121 ）+ ' ''AND''' + CONVERT （ VARCHAR （< span class =code-digit> 10 ）， @ endDay ， 121 ）+ ' '''
- set pivot query
SET @ pt = N ' SELECT EmployeeName，EmployeeNo，' + @ cols + ' ' +
' FROM（' + @ dt + ' ）AS DT' +
' PIVOT（MAX（TypeID）FOR xDay IN（' + @cols + ' ））AS PT'
SET @cols = ' '
SET @ currDay = @ startDay
WHILE （ @ currDay < = @ endDay ）
BEGIN
SET @cols = @cols + ' ISNULL（[' + CONVERT （ VARCHAR （ 2 ），DAY（ @ currDay ））+ ' ]，''L''）AS [' + CONVERT （ VARCHAR （ 2 ），DAY（ @ currDay ））+ ' ]，'
SET @ currDay = DATEADD（dd， 1 ， @ currDay ）
END
SET @ cols = LEFT （ @cols ，LEN（ @cols ） - 1）
- SELECT @cols
SET @ ft = N ' SELECT EmployeeName，EmployeeNo，' + @cols + ' ' +
< span class =code-string>' FROM（' + @ pt + < span class =code-str ing>' ）AS FT'
- PRINT @ft
- 执行查询
EXEC （ @ ft ）
- drop temp。表
DROP 表 #pvtsrc
结果：
EmpName EmpNo 1 2 3 4 ... 30
Dumitru M1975012200006 L L T W ... W
Mottaghi M1975040500009 L L L W ... W
Dehghan M1975111100029 L L L L ... W
Alvarado M1986092400012 L L T W ... W
This is how the available data is
EmployeeName | EmployeeNo | Type | StartDate | EndDate
Dumitru | M1975012200006 | TRAVEL | 06/03/2013 | 06/03/2013
Mottaghi | M1975040500009 | TRAVEL | 06/17/2013 | 06/17/2013
Alvarado | M1986092400012 | TRAVEL | 06/03/2013 | 06/03/2013
Dehghan | M1975111100029 | TRAVEL | 06/15/2013 | 06/15/2013
Dumitru | M1975012200006 | WORK | 06/04/2013 | 07/15/2013
Mottaghi | M1975040500009 | WORK | 06/18/2013 | 09/01/2013
Alvarado | M1986092400012 | WORK | 06/04/2013 | 07/15/2013
Dehghan | M1975111100029 | WORK | 06/16/2013 | 07/25/2013
Dumitru | M1975012200006 | LEAVE | 07/17/2013 | 09/01/2013
Mottaghi | M1975040500009 | LEAVE | 09/03/2013 | 10/15/2013
Alvarado | M1986092400012 | LEAVE | 07/17/2013 | 09/01/2013
Dehghan | M1975111100029 | LEAVE | 07/27/2013 | 09/02/2013
I want to display all the days of a month column wise.
The expected output is as follows
June Month Timeline
EmployeeName - 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
Dumitru,Georg - T W W W W W W W W W W W W W W L L L L L L L L L L L L L L L L L L L L L L L L L
Employee Name2 - W W W W W W W W W W W W W W W W W L L L L L.... etc.
T - represents TRAVEL days; W - represents WORK days; L - represents LEAVE days;
I appreciate any help in this regard. Thank you in advance
解决方案 Have a look at below example:
--declare temporary table to store example data
DECLARE @tmp TABLE (EmployeeName VARCHAR(30), EmployeeNo VARCHAR(30), [Type] VARCHAR(30), StartDate DATETIME, EndDate DATETIME)
--insert data into temporary table
INSERT INTO @tmp (EmployeeName, EmployeeNo, [Type] , StartDate, EndDate)
SELECT 'Dumitru', 'M1975012200006', 'TRAVEL', '06/03/2013', '06/03/2013'
UNION ALL SELECT 'Mottaghi', 'M1975040500009', 'TRAVEL', '06/17/2013', '06/17/2013'
UNION ALL SELECT 'Alvarado', 'M1986092400012', 'TRAVEL', '06/03/2013', '06/03/2013'
UNION ALL SELECT 'Dehghan', 'M1975111100029', 'TRAVEL', '06/15/2013', '06/15/2013'
UNION ALL SELECT 'Dumitru', 'M1975012200006', 'WORK', '06/04/2013', '07/15/2013'
UNION ALL SELECT 'Mottaghi', 'M1975040500009', 'WORK', '06/18/2013', '09/01/2013'
UNION ALL SELECT 'Alvarado', 'M1986092400012', 'WORK', '06/04/2013', '07/15/2013'
UNION ALL SELECT 'Dehghan', 'M1975111100029', 'WORK', '06/16/2013', '07/25/2013'
UNION ALL SELECT 'Dumitru', 'M1975012200006', 'LEAVE', '07/17/2013', '09/01/2013'
UNION ALL SELECT 'Mottaghi', 'M1975040500009', 'LEAVE', '09/03/2013', '10/15/2013'
UNION ALL SELECT 'Alvarado', 'M1986092400012', 'LEAVE', '07/17/2013', '09/01/2013'
UNION ALL SELECT 'Dehghan', 'M1975111100029', 'LEAVE', '07/27/2013', '09/02/2013'
IF OBJECT_ID(N'#pvtsrc',N'U') IS NOT NULL
DROP TABLE #pvtsrc
--destination table: source for pivot
CREATE TABLE #pvtsrc (EmployeeName VARCHAR(30), EmployeeNo VARCHAR(30), TypeID VARCHAR(1), CurrDate DATETIME)
--enum type for each day and employee
--insert into destination table
;WITH Days AS
(
-- initial
SELECT EmployeeName, EmployeeNo, LEFT([Type],1) AS TypeID, StartDate, EndDate, StartDate AS CurrDate
FROM @tmp
UNION ALL
SELECT EmployeeName, EmployeeNo, TypeID, StartDate, EndDate, DATEADD(dd,1,CurrDate) AS CurrDate
FROM Days
WHERE DATEADD(dd,1,CurrDate)<=EndDate
)
INSERT INTO #pvtsrc (EmployeeName, EmployeeNo, TypeID, CurrDate)
SELECT EmployeeName, EmployeeNo, TypeID, CurrDate
FROM Days
ORDER BY EmployeeName, CurrDate
--declare varables needed for pivot
DECLARE @cols VARCHAR(1000)
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(4000)
DECLARE @ft VARCHAR(MAX)
DECLARE @startDay DATETIME
DECLARE @endDay DATETIME
DECLARE @currDay DATETIME
--set range of dates
SET @startDay = '2013-06-01'
SET @endDay = '2013-06-30'
SET @currDay = @startDay
--set columns collection (1-28/31)
SET @cols = ''
WHILE (@currDay <= @endDay)
BEGIN
SET @cols = @cols + '[' + CONVERT(VARCHAR(2),DAY(@currDay)) + '],'
SET @currDay = DATEADD(dd,1,@currDay)
END
SET @cols = LEFT(@cols, LEN(@cols)-1)
--set data source for pivot
SET @dt = N'SELECT EmployeeName, EmployeeNo, TypeID, DAY(CurrDate) AS xDay
FROM #pvtsrc
WHERE CurrDate BETWEEN ''' + CONVERT(VARCHAR(10),@startDay,121) + ''' AND ''' + CONVERT(VARCHAR(10),@endDay,121) + ''''
--set pivot query
SET @pt = N'SELECT EmployeeName, EmployeeNo, ' + @cols + ' ' +
'FROM(' + @dt + ') AS DT ' +
'PIVOT(MAX(TypeID) FOR xDay IN(' + @cols + ')) AS PT'
SET @cols = ''
SET @currDay = @startDay
WHILE (@currDay <= @endDay)
BEGIN
SET @cols = @cols + 'ISNULL([' + CONVERT(VARCHAR(2),DAY(@currDay)) + '],''L'') AS [' + CONVERT(VARCHAR(2),DAY(@currDay)) + '],'
SET @currDay = DATEADD(dd,1,@currDay)
END
SET @cols = LEFT(@cols, LEN(@cols)-1)
--SELECT @cols
SET @ft = N'SELECT EmployeeName, EmployeeNo, ' + @cols + ' ' +
'FROM (' + @pt + ') AS FT '
--PRINT @ft
--execute query
EXEC(@ft)
--drop temp. table
DROP TABLE #pvtsrc
Result:
EmpName EmpNo 1 2 3 4 ... 30
Dumitru M1975012200006 L L T W ... W
Mottaghi M1975040500009 L L L W ... W
Dehghan M1975111100029 L L L L ... W
Alvarado M1986092400012 L L T W ... W
这篇关于用于显示特定月份的相应工作日，旅行日和休假日的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
