需要日期作为日期范围的行 [英] need date as row wise for the range of dates

问题描述

GO
/****** Object:  Table [dbo].[Tbl_User_Vacation]    Script Date: 11/04/2014 18:22:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Tbl_User_Vacation](
	[User_Vacation_ID] [int] IDENTITY(1,1) NOT NULL,
	[User_ID] [int] NULL,
	[Leave_Date] [datetime] NULL,
	[Leave_Reason] [varchar](2550) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Expected_Return] [datetime] NULL,
	[Actual_return] [datetime] NULL,
 CONSTRAINT [PK_Tbl_User_Vacation] PRIMARY KEY CLUSTERED 
(
	[User_Vacation_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

这个表中包含从预期收益中输入休假日期的字段，其中包含离开我需要一个行上有日期的结果，例如。

需要用户名1从30-10-14离开到5-11-14

结果应该是是



[User_Vac ation_ID] |日期

======================= | ================= ===========

1 | 30-10-14

1 | 31-10-14

1 | 1-11-14

1 | 2-11-14

1 | 3-11-14

1 | 4-11-14

It is the table which have the fields to enter leave date from expected return that gives an range for the leave I need an result that has a date on rows eg.
Need userid "1" leave from 30-10-14 to 5-11-14
the result should be

[User_Vacation_ID] | Date
=======================|============================
1 | 30-10-14
1 | 31-10-14
1 | 1-11-14
1 | 2-11-14
1 | 3-11-14
1 | 4-11-14

推荐答案

示例代码：

sample code:

SET @CurrentDate = @startDate

WHILE @CurrentDate <= @endDate
BEGIN
    INSERT INTO TableOfDates(User_Vacation_ID,DateValue) VALUES (@ID, @CurrentDate)

    SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate)
END

我更喜欢CTE [ ^ ]而不是 WHILE 循环;）

I prefer CTE[^] rather than WHILE loop ;)

DECLARE @startDate DATETIME = '2014-10-30'
DECLARE @finishDate DATETIME = '2014-11-04' 
;WITH MyDates AS
(
    SELECT @startDate AS MyDate
    UNION ALL 
    SELECT DATEADD(DD,1,MyDate) AS MyDate
    FROM MyDates
    WHERE DATEADD(DD,1,MyDate)<=@finishDate
)
INSERT INTO TableName (User_Vacation_ID, [Date])
SELECT 1 AS User_Vacation_ID, MyDate AS [Date]
FROM MyDates

这篇关于需要日期作为日期范围的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！