sql server缺少日期 [英] sql server missing dates

问题描述

大家好



我有这样的数据

Hi all

I have the data like this

Empid   Datevalue
anil    5/1/2013
anil    5/2/2013
anil    5/3/2013
anil    5/5/2013
anil    5/7/2013
anil    5/8/2013
anil    5/9/2013
anil    5/10/2013
mahesh  5/1/2013
mahesh  5/2/2013
mahesh  5/3/2013
mahesh  5/4/2013
mahesh  5/5/2013
mahesh  5/6/2013
mahesh  5/8/2013
mahesh  5/9/2013

现在我想得到的输出如

now I want to get the output like

Empid   Datevalue
anil    5/1/2013
anil    5/2/2013
anil    5/3/2013
anil    5/4/2013
anil    5/5/2013
anil    5/6/2013
anil    5/7/2013
anil    5/8/2013
anil    5/9/2013
anil    5/10/2013
mahesh  5/1/2013
mahesh  5/2/2013
mahesh  5/3/2013
mahesh  5/4/2013
mahesh  5/5/2013
mahesh  5/6/2013
mahesh  5/7/2013
mahesh  5/8/2013
mahesh  5/9/2013
mahesh  5/10/2013

< br $> b $ b

任何人都可以帮我拿到这个





问候，

prakash.ch

could any one help me out to get this


Regards,
prakash.ch

推荐答案

假设你的表名是 empdata 。

Assuming that the name of your table is empdata.

Declare Cursor_EmpIds CURSOR LOCAL FORWARD_ONLY FOR Select distinct empid from empdata;
Declare @CurrentEmpId varchar(80);
Declare @LoopController bit = 0;
Declare @CompareDate date;
Declare @TemporaryTable TABLE (empid varchar(80),DateValue date);
OPEN Cursor_EmpIds;
FETCH NEXT FROM Cursor_EmpIds into @CurrentEmpId;
While @@FETCH_STATUS=0 
BEGIN
	SET @LoopController=0;
	SET @CompareDate=CAST('2013-05-01' as date);
	WHILE @LoopController=0
	BEGIN
		IF (SELECT COUNT(*) from empdata where empid=@CurrentEmpId AND DateValue=@CompareDate)=0
		BEGIN
			INSERT INTO @TemporaryTable VALUES (@CurrentEmpId,@CompareDate);
		END
		Set @CompareDate=DATEADD(day,1,@CompareDate);
		If @CompareDate=CAST('2013-05-11' as date)
		BEGIN
		    SET @LoopController=1;
		END
	END
FETCH NEXT FROM Cursor_EmpIds into @CurrentEmpId;
END
Close Cursor_EmpIds;
SELECT empid,datevalue from EmpData UNION SELECT empid,datevalue from @TemporaryTable order by empid,datevalue;

在Microsoft SQL Server Express 2012上测试

填充日期表然后左边连接你的桌子



CREATE TABLE DateTable（DateVale DATE）

DECLARE @Mindate DATE ='2013-01-01'

DECLARE @Maxdate DATE ='2013-01-15'



;与日期（DATEPARAM）AS

（

SELECT @Mindate AS DATE

UNION ALL

SELECT DATEADD（DAY，1，DATEPARAM）from Dates WHERE DATEPARAM< @Maxdate

）

INSERT DateTable

SELECT * FROM日期

Populate date table and then left join with your table

CREATE TABLE DateTable(DateVale DATE)
DECLARE @Mindate DATE = '2013-01-01'
DECLARE @Maxdate DATE = '2013-01-15'

;WITH Dates(DATEPARAM) AS
(
SELECT @Mindate AS DATE
UNION ALL
SELECT DATEADD(DAY, 1, DATEPARAM) FROM Dates WHERE DATEPARAM < @Maxdate
)
INSERT DateTable
SELECT * FROM Dates

这篇关于sql server缺少日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！