SQL Server:如果表中没有条件,则选择范围内的每一天
我在SQL Server中有一个表RRHH.ReportAssistence
。所有数据都是从我的软件中自动完成的,用于标记,并且该软件完成该表。SQL Server:如果表中没有条件,则选择范围内的每一天
cod_mark (int auto increment)
cod_personal (int)
cod_schedule (int)
date_mark (date)
check_in (time(0))
check_out (time(0))
查询:
select * from RRHH.ReportAssistence
cod_mark/cod_personal/cod_schedule/date_mark/check_in/check_out
--------------------------------------------------------------------------
1 /39 / 1 /2017-03-02/NULL /18:10:00
2 /39 / 1 /2017-03-05/NULL /18:02:00
3 /39 / 1 /2017-03-08/09:20:00/NULL
4 /39 / 1 /2017-03-10/NULL /18:04:00
5 /39 / 1 /2017-03-20/08:56:00/18:53:00
6 /39 / 1 /2017-03-21/08:52:00/18:10:00
7 /39 / 1 /2017-03-22/08:56:00/18:09:00
8 /39 / 1 /2017-03-23/NULL /18:05:00
我需要一个存储过程,列出了一系列的所有天并完成所有天范围内。如果不存在任何“date_mark”显示在时间栏“条件” = LEFT,如果时间在“check_in”到09:15:00显示“CONDITION”= LATE,如果小于09:15:00显示“CONTIDION”= OK,如果没有check_in“CONDITION”=左输入, 如果没有check_out没有问题“CONDITION”= OK
预期结果:
SP_showMeReportAssistance (cod_personal), (startDate), (endDate)
execute SP_showMeReportAssistance 39, '01/03/2017', '23/03/2017'
cod_personal/cod_schedule/date_mark/check_in/check_out/CONDITION
39 / 1 /2017-03-01/NULL /NULL /LEFT
39 / 1 /2017-03-02/NULL /18:10:00 /LEFT IN
39 / 1 /2017-03-03/NULL /NULL /LEFT
39 / 1 /2017-03-04/NULL /NULL /LEFT
39 / 1 /2017-03-05/NULL /18:02:00 /LEFT IN
39 / 1 /2017-03-06/NULL /NULL /LEFT
39 / 1 /2017-03-07/NULL /NULL /LEFT
39 / 1 /2017-03-08/09:20:00/NULL /LATE
39 / 1 /2017-03-09/NULL /NULL /LEFT
39 / 1 /2017-03-10/NULL /18:04:00 /LEFT IN
39 / 1 /2017-03-11/NULL /NULL /LEFT
39 / 1 /2017-03-12/NULL /NULL /LEFT
39 / 1 /2017-03-13/NULL /NULL /LEFT
39 / 1 /2017-03-14/NULL /NULL /LEFT
39 / 1 /2017-03-15/NULL /NULL /LEFT
39 / 1 /2017-03-16/NULL /NULL /LEFT
39 / 1 /2017-03-17/NULL /NULL /LEFT
39 / 1 /2017-03-18/NULL /NULL /LEFT
39 / 1 /2017-03-19/NULL /NULL /LEFT
39 / 1 /2017-03-20/08:56:00/18:53:00 /OK
39 / 1 /2017-03-21/08:52:00/18:10:00 /OK
39 / 1 /2017-03-22/08:56:00/18:09:00 /OK
39 / 1 /2017-03-23/NULL /18:05:00 /LEFT IN
您需要先根据传递给过程的日期生成日期列表。并做LEFT JOIN
到您的实际表。通过向CASE
提供您的条件来获得您的CONDITION栏。
模式:
CREATE TABLE ReportAssistence (
cod_mark INT IDENTITY
,cod_personal INT
,cod_schedule INT
,date_mark DATE
,check_in TIME(0)
,check_out TIME(0)
)
INSERT INTO ReportAssistence
SELECT 39 , 1 ,'2017-03-02' , NULL , '18:10:00'
UNION ALL
SELECT 39 , 1 ,'2017-03-05' , NULL , '18:02:00'
UNION ALL
SELECT 39 , 1 ,'2017-03-08' , '09:20:00' , NULL
UNION ALL
SELECT 39 , 1 ,'2017-03-10' , NULL , '18:04:00'
UNION ALL
SELECT 39 , 1 ,'2017-03-20' , '08:56:00' , '18:53:00'
UNION ALL
SELECT 39 , 1 ,'2017-03-21' , '08:52:00' , '18:10:00'
UNION ALL
SELECT 39 , 1 ,'2017-03-22' , '08:56:00' , '18:09:00'
UNION ALL
SELECT 39 , 1 ,'2017-03-23' , NULL , '18:05:00'
而且你的代码会
DECLARE @cod_personal INT = 39
,@startDate DATE = '2017/03/01'
,@endDate DATE = '2017/03/23'
;WITH CTE AS (
SELECT DATEADD(DD, number, @startDate) AS DATES
FROM master.dbo.spt_values
WHERE TYPE = 'P'
AND DATEADD(DD, number, @startDate) <= @endDate
)
SELECT ISNULL(RA.cod_personal, @cod_personal) AS cod_personal
,cod_schedule
,DATES AS date_mark
,check_in
,check_out
,CASE
WHEN date_mark IS NULL
THEN 'LEFT'
WHEN date_mark IS NOT NULL AND check_in IS NULL
THEN 'LEFT IN'
WHEN date_mark IS NOT NULL AND check_in IS NOT NULL AND check_out IS NOT NULL
THEN 'OK'
WHEN date_mark IS NOT NULL AND check_in IS NOT NULL AND check_in > '09:15:00'
THEN 'LATE'
ELSE 'LEFT'
END AS CONDITION
FROM CTE C
LEFT JOIN ReportAssistence RA ON C.DATES = date_mark
WHERE ISNULL(RA.cod_personal, @cod_personal) = @cod_personal
而其结果将是
+--------------+--------------+------------+----------+-----------+-----------+
| cod_personal | cod_schedule | date_mark | check_in | check_out | CONDITION |
+--------------+--------------+------------+----------+-----------+-----------+
| 39 | NULL | 2017-03-01 | NULL | NULL | LEFT |
| 39 | 1 | 2017-03-02 | NULL | 18:10:00 | LEFT IN |
| 39 | NULL | 2017-03-03 | NULL | NULL | LEFT |
| 39 | NULL | 2017-03-04 | NULL | NULL | LEFT |
| 39 | 1 | 2017-03-05 | NULL | 18:02:00 | LEFT IN |
| 39 | NULL | 2017-03-06 | NULL | NULL | LEFT |
| 39 | NULL | 2017-03-07 | NULL | NULL | LEFT |
| 39 | 1 | 2017-03-08 | 09:20:00 | NULL | LATE |
| 39 | NULL | 2017-03-09 | NULL | NULL | LEFT |
| 39 | 1 | 2017-03-10 | NULL | 18:04:00 | LEFT IN |
| 39 | NULL | 2017-03-11 | NULL | NULL | LEFT |
| 39 | NULL | 2017-03-12 | NULL | NULL | LEFT |
| 39 | NULL | 2017-03-13 | NULL | NULL | LEFT |
| 39 | NULL | 2017-03-14 | NULL | NULL | LEFT |
| 39 | NULL | 2017-03-15 | NULL | NULL | LEFT |
| 39 | NULL | 2017-03-16 | NULL | NULL | LEFT |
| 39 | NULL | 2017-03-17 | NULL | NULL | LEFT |
| 39 | NULL | 2017-03-18 | NULL | NULL | LEFT |
| 39 | NULL | 2017-03-19 | NULL | NULL | LEFT |
| 39 | 1 | 2017-03-20 | 08:56:00 | 18:53:00 | OK |
| 39 | 1 | 2017-03-21 | 08:52:00 | 18:10:00 | OK |
| 39 | 1 | 2017-03-22 | 08:56:00 | 18:09:00 | OK |
| 39 | 1 | 2017-03-23 | NULL | 18:05:00 | LEFT IN |
+--------------+--------------+------------+----------+-----------+-----------+
是的,但我认为,而不是循环和递归CTE,最好使用存在数据库中的表。 –
是的,我只是想知道,因为这似乎是某种教育数据库的任务,是否可以安全地假定作者可以/应该查询系统数据库。 –
你是对的。 :) @MaxSzczurek –
@ShakeerMirza的答案是正确的 - 这里是一个替代CTE是不需要查询master数据库来构建表o f连续日期。 (因为格式是很重要的添加这作为一个答案,而不是评论。)
;with cod (dates)
as
(
select cast(@startdate as date) as dates
union all
select dateadd(day, 1, dates) as next_dt
from cod
where DATEADD(day, 1, dates) < @enddate
)
select * from cod
存储过程DONE!
这是必要的日期在我的情况下,可以作为文本输入, 因为我使用Java和我有问题的铸造date.util到date.sql我POO和接口
USE [DB_Demo1]
GO
/****** Object: StoredProcedure [dbo].[showMeReportAssistance] Script Date: 5/04/2017 12:36:46 a. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[showMeReportAssistance]
@cod_personal INT,
@startDate VARCHAR(10),
@endDate VARCHAR(10)
as
BEGIN
;WITH CTE AS (
SELECT DATEADD(DD, number, CONVERT(datetime, @startDate, 103)) AS DATES
FROM master.dbo.spt_values
WHERE TYPE = 'P'
AND DATEADD(DD, number, CONVERT(datetime, @startDate, 103)) <= CONVERT(datetime, @endDate, 103)
)
SELECT ISNULL(RA.cod_personal, @cod_personal) AS cod_personal
,cod_schedule
,DATES AS date_mark
,check_in
,check_out
,CASE
WHEN date_mark IS NULL
THEN 'LEFT'
WHEN date_mark IS NOT NULL AND check_in IS NULL
THEN 'LEFT IN'
WHEN date_mark IS NOT NULL AND check_in IS NOT NULL AND check_out IS NOT NULL
THEN 'OK'
WHEN date_mark IS NOT NULL AND check_in IS NOT NULL AND check_in > '09:15:00'
THEN 'LATE'
ELSE 'LEFT'
END AS CONDITION
FROM CTE C
LEFT JOIN ReportAssistence RA ON C.DATES = date_mark
WHERE ISNULL(RA.cod_personal, @cod_personal) = @cod_personal
END
如何给ReportAssistence中不存在的日期提供cod_schedule? –
我显示的第一个表格是真实的,这些信息由软件自动填充,关于“cod_schedule”是分配给与人员代码相匹配的工作人员的时间表代码 –
备注:您应该**不要**使用存储过程的'sp_'前缀。微软已经保留了这个前缀以供自己使用(参见*命名存储过程*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx),以及你将来有可能冒着名字冲突的风险。 [这对你的存储过程性能也是不利的](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix)。最好只是简单地避免使用'sp_'并使用别的东西作为前缀 - 或者根本没有前缀! –