生成每月出勤率 [英] Generate Monthly Attendance
本文介绍了生成每月出勤率的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
代码名称出席日期状态
---- ---- -------------- ------
A001 Abc Jan-01-2013 P
B001 Xyz Jan-01-2013 P
A001 Abc Jan-02-2013 A
B001 Xyz Jan-02-2013 L
A001 Abc Jan-03-2013 L
P：现在
A：缺席
L：晚了
每月生成这样的报告..
代码名称01 02 03 04 ...... 31
A001 abc PAL
B001 xyz PL
先谢谢..
解决方案
正如我所说，我试图让你输出检查这个从你的桌子我添加了一个新的columname作为relation_key
检查查询belo
- 表脚本
创建 TABLE [dbo]。[Temp]（[code] [ char ]（ 4 ） NULL ，[name] [ varchar ]（ 50 ） null ，[AttendanceDate] [ datetime ] NULL ，[Status] [ char ]（ 2 ）
< span class =code-keyword> COLLATE SQL_Latin1_General_CP1_CI_AS NULL ，[reason] [ varchar ]（ 50 ） COLLATE SQL_Latin1_General_CP1_CI_AS NULL ， relation_key int ） ON [ PRIMARY ]
insert 进入 Temp 值（' B001'，' Bbc'，' 2014-03-05 00：00：00.000'，' P'，' '， 1 ）
插入 进入 Temp 值（' A001'，' Abc'，' 2014-03- 05 00：00：00.000'，' P'，' '， 1 ）
透视查询。
DECLARE @ Start datetime = ' 2014-03-01'
， @ End datetime = ' 2014-03-31'，
@ DList varchar （ 2000 ），
@ Sql varchar （max）
IF OBJECT_ID （' tempdb ..＃TEMP_Everyday'） IS NOT < span class =code-keyword> NULL
DROP TABLE #TEMP_Everyday
SELECT DATEADD（dd，a.number， @ Start ） AS 日期， CONVERT （ VARCHAR （ 2 ），DATEADD（dd，a.number） ， @ Start ）， 6 ） as d， 1 as relation_key
into #TEMP_Everyday
FROM
master..spt_values a
WHERE
type = ' p' AND
DATEADD（dd，a.number， @ Start ）< = @ End
- - 从温度中选择*
- - select * from #TEMP_Everyday
IF OBJECT_ID （' tempdb ..＃TEMP_final'） IS NOT NULL
DROP 表 #TEMP_final
选择 distinct a.Code，a.name，
case 当 a.AttendanceDate = b。 date 然后 a.status else ' ' END as 状态
，a.reason，b。 date ，bd
into #TEMP_final
来自
#TEMP_Everyday b left outer join
temp a
ON b.relation_key = a.relation_key
group 按 a.Code，a.AttendanceDate，a.status，a.name， a.reason，b。 date ，bd
- 选择不同的代码，名称，状态，原因，日期，d
- 来自
- ＃TEMP_final
- group by
- 代码，名称，状态，原因，日期，d
DECLARE @cols AS NVARCHAR （MAX ），
@ query AS NVARCHAR （MAX）
选择 @cols = STUFF（（ SELECT distinct ' ，' + QUOTENAME（d）
来自 #TEMP_Everyday
FOR XML PATH（' '）， TYPE
）.value（' 。'，' NVARCHAR（MAX）'）
， 1 ， 1 ，' '）
set @ query = SELECT Code，name，' + @cols + < span class =code-string>'
来自#TEMP_final
pivot
（
min（status）
表示状态（' + @cols + ）
）p'
执行（ @ query ）
请注意这里的数据现在不是匹配你可以根据需要过滤和匹配数据
这里iam添加sql查询以显示星期日星期日来自和迄今为止示例开始日期为'2014-01-01'，结束日期为'2014-04-04'此查询将每周星期日作为输出日期返回：
2013-12-29
2014-01-05
2014-01-12
2014-01-19
2014-01-26
2014-02-02
2014-02-09
2014-02-16
2014-02-23
2014-03-02
2014-03-09
2014 -03-16
2014-03-23
2014-03-30
声明 @ FromDate VARCHAR （ 20 ）= ' 2014年1' 月1日
声明 @ ToDate VARCHAR （ 20 ）= ' 2014-04-04'
IF OBJECT_ID （' tempdb ..＃TEMP_EveryWk_Snday'） IS NOT NULL
DROP TABLE #TEMP_EveryWk_Snday
DECLARE @ TOTALCount INT
选择 @ TOTALCount = DATEDIFF（dd， @ FromDate ， @ ToDate ）; - DATEDIFF（DD，@ FromDate，@ ToDate）;
WITH d AS
（
SELECT top （ @ TOTALCount ）AllDays = DATEADD（DAY，ROW_NUMBER（）
OVER （ ORDER BY object_id），REPLACE（ @ FromDate ，' - '，' '））
FROM sys.al l_objects
）
SELECT distinct DATEADD（DAY， 1 - DATEPART（WEEKDAY，AllDays），CAST（AllDays AS DATE ））WkStartSundays
into #TEMP_EveryWk_Snday
FROM d
选择 WkStartSundays，DATENAME（dw，WkStartSundays）Day_Name 来自 #TEMP_EveryWk_Snday
Code Name AttendanceDate Status
---- ---- -------------- ------
A001 Abc Jan-01-2013 P
B001 Xyz Jan-01-2013 P
A001 Abc Jan-02-2013 A
B001 Xyz Jan-02-2013 L
A001 Abc Jan-03-2013 L
P: Present
A: Absent
L: Late
Generate Report like this for every month..
Code Name 01 02 03 04 ...... 31
A001 abc P A L
B001 xyz P L
Thanks in advance..
解决方案
Hi,
As i mention i have tried to make you output check this from your table i have added a new columname as relation_key
check the query belo
--Table Script CREATE TABLE [dbo].[Temp]( [code] [char](4) NULL, [name] [varchar](50) null, [AttendanceDate] [datetime] NULL, [Status] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [reason] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,relation_key int ) ON [PRIMARY] insert into Temp values('B001', 'Bbc', '2014-03-05 00:00:00.000', 'P','',1) insert into Temp values('A001', 'Abc', '2014-03-05 00:00:00.000', 'P','',1) pivot query. DECLARE @Start datetime ='2014-03-01' ,@End datetime='2014-03-31', @DList varchar(2000), @Sql varchar(max) IF OBJECT_ID('tempdb..#TEMP_Everyday') IS NOT NULL DROP TABLE #TEMP_Everyday SELECT DATEADD(dd,a.number,@Start) AS Date ,CONVERT(VARCHAR(2),DATEADD(dd,a.number,@Start),6) as d ,1 as relation_key into #TEMP_Everyday FROM master..spt_values a WHERE type='p' AND DATEADD(dd,a.number,@Start)<=@End ----select * from Temp ----select * from #TEMP_Everyday IF OBJECT_ID('tempdb..#TEMP_final') IS NOT NULL DROP TABLE #TEMP_final select distinct a.Code ,a.name, case when a.AttendanceDate=b.date then a.status else '' END as Status ,a.reason,b.date,b.d into #TEMP_final from #TEMP_Everyday b left outer join temp a ON b.relation_key=a.relation_key group by a.Code , a.AttendanceDate,a.status,a.name,a.reason,b.date,b.d --select distinct Code,name,status,reason,date,d --from --#TEMP_final --group by --Code,name,status,reason,date,d DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct ',' + QUOTENAME(d) from #TEMP_Everyday FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT Code,name,' + @cols + ' from #TEMP_final pivot ( min(status) for status in (' + @cols + ') ) p ' execute(@query)
Note here the data is now not matched you can filter and match the data as you needed
Hi,
here iam adding sql query to display ever week sunday date with in from and to date for example Start date as '2014-01-01' and end date as '2014-04-04' this query will return every week sunday as output date :
"2013-12-29 2014-01-05 2014-01-12 2014-01-19 2014-01-26 2014-02-02 2014-02-09 2014-02-16 2014-02-23 2014-03-02 2014-03-09 2014-03-16 2014-03-23 2014-03-30
Declare @FromDate VARCHAR(20) = '2014-01-01' Declare @ToDate VARCHAR(20) = '2014-04-04' IF OBJECT_ID('tempdb..#TEMP_EveryWk_Snday') IS NOT NULL DROP TABLE #TEMP_EveryWk_Snday DECLARE @TOTALCount INT Select @TOTALCount= DATEDIFF(dd,@FromDate,@ToDate);--DATEDIFF(DD,@FromDate,@ToDate); WITH d AS ( SELECT top (@TOTALCount) AllDays = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY object_id), REPLACE(@FromDate,'-','')) FROM sys.all_objects ) SELECT distinct DATEADD(DAY, 1 - DATEPART(WEEKDAY, AllDays), CAST(AllDays AS DATE))WkStartSundays into #TEMP_EveryWk_Snday FROM d select WkStartSundays,DATENAME(dw,WkStartSundays) Day_Name from #TEMP_EveryWk_Snday
这篇关于生成每月出勤率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
