获取2个日期之间的日期并填写每条记录 [英] get dates between 2 dates and populate for each record

问题描述

您好。我想知道我能否做到这一点。我将使用此案例为每位员工设置时间表。我有一个名为tblEmployee的表。

EmployeeNo	EmpName
00002	Tiamsic，Juan
00001	Dela Cruz，Jane
00003	Cruz，Ely

如果我有两个约会日期：

日期1： 08/01/2015

日期2： 08/05/2015



怎么样我创建一个查询，显示每个员工的所有日期，例如

EmployeeNo	EmpName	Date _
00002	Tiamsic，Juan	08/01/2015
00002	Tiamsic，Juan	08/02/2015
00002	Tiamsic，Juan	08/03/2015
00002	Tiamsic，Juan	08/04/2015
00002	Tiamsic，Juan	08/05/2015
00001	Dela，Jane	08/01/2015
00001	Dela，Jane	08/02/2015
00001	Dela，Jane	08/03/2015
00001	Dela，Jane	08/04/2015
00001	Dela，Jane	08/05/2015
00003	Cruz，Ely	08/01/2015
00003	Cruz，Ely	08/02/2015
00003	Cruz，Ely	08/03/2015
00003	Cruz，Ely	08/04/2015
00003	Cruz，Ely	08/05/2015

Hi. I am wondering if I can do this. I am going to use this case for setting schedule for each employee. I have a table named tblEmployee.

EmployeeNo	EmpName
00002	Tiamsic, Juan
00001	Dela Cruz, Jane
00003	Cruz, Ely

If I have two date given:
Date 1: 08/01/2015
Date 2: 08/05/2015

How can I create a query that will display all dates for each employees like

EmployeeNo	EmpName	Date_
00002	Tiamsic, Juan	08/01/2015
00002	Tiamsic, Juan	08/02/2015
00002	Tiamsic, Juan	08/03/2015
00002	Tiamsic, Juan	08/04/2015
00002	Tiamsic, Juan	08/05/2015
00001	Dela, Jane	08/01/2015
00001	Dela, Jane	08/02/2015
00001	Dela, Jane	08/03/2015
00001	Dela, Jane	08/04/2015
00001	Dela, Jane	08/05/2015
00003	Cruz, Ely	08/01/2015
00003	Cruz, Ely	08/02/2015
00003	Cruz, Ely	08/03/2015
00003	Cruz, Ely	08/04/2015
00003	Cruz, Ely	08/05/2015

推荐答案

这很简单：

It's quite simple:

--temp table - because it's just an example, you need to refer to existing table
DECLARE @tblEmployee TABLE (EmployeeNo VARCHAR(10),	EmpName VARCHAR(255))

INSERT INTO @tblEmployee (EmployeeNo, EmpName)
VALUES('00002', 'Tiamsic, Juan'),
('00001', 'Dela Cruz, Jane'),
('00003', 'Cruz, Ely')

--dates in format which is accepted by my Sql Server, change it to your needs
DECLARE @startDate DATE = '2015-01-08'
DECLARE @endDate DATE = '2015-05-08'

--declate temp table for dates
DECLARE @dates TABLE (aDate DATE)
--insert data using CTE
;WITH CTE AS
(
	SELECT @startDate AS myDate
	WHERE  @startDate< @endDate
	UNION ALL
	SELECT DATEADD(MM,1,myDate)
	FROM CTE
	WHERE DATEADD(MM,1,myDate)<@endDate 
)
INSERT INTO @dates (aDate)
SELECT myDate
FROM CTE

--cross join data
SELECT t1.EmployeeNo, t1.EmpName , t2.aDate 
FROM @tblEmployee AS t1, @dates AS t2
ORDER BY t1.EmployeeNo, t2.aDate 

如需了解更多信息，请参阅：

使用公用表表达式 [ ^ ]

使用common_table_expression（Transact-SQL） [ ^ ]

SQL连接的可视化表示 [ ^ ]

For further information, please see:
Using Common Table Expressions[^]
WITH common_table_expression (Transact-SQL)[^]
Visual Representation of SQL Joins[^]

这篇关于获取2个日期之间的日期并填写每条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！