Looping through dates in sql server

I have a sql code like this:

declare @currentDate datetime,@enddate datetime;

select @currentDate = '06/1/2015';
select @enddate='06/05/2015'
while @currentDate <= @enddate

begin

-- do whatever is needed

select @currentDate = dateadd(DAY,1,@currentDate);
select @currentDate as currentdate
end

in result am not getting the current date. am getting date from 06/02/2015.i want to get records from 06/1/2015 to 06/05/2015. total 5 records..but now i am getting only 4 records

how i can get full records

June 21st, 2015 8:16am

with tmp(plant_date) as
(
   select cast('20150601' as datetime)
   union all
   select plant_date + 1
     from tmp
    where plant_date < '20150605'
)
select*
  from  tmp
option (maxrecursion 0)
Free Windows Admin Tool Kit Click here and download it now
June 21st, 2015 8:21am

sir this is working..but my exact problem is this..can u please check on this link

http://stackoverflow.com/questions/30962125/how-to-use-loop-in-stored-procedure-to-copy-data-from-one-table-to-another-in-sq

June 21st, 2015 8:23am

try this..

also, what do you mean updating the transdetails table.. because sometimes the employee might not take the same no of days off, so, you cannot do update correctly, you may have to rethink on this..may be better delete the old ones and insert the new ones.... think again on this...

declare @tranleave table (EMPLOYEE_NUMBER int,NAME varchar(20),DATE_START date, DATE_END date)

insert into @tranleave values 
(1,'annual leav','2015-06-01','2015-06-10')
,(2,'sick leav','2015-06-01','2015-06-03')

select EMPLOYEE_NUMBER, a.name,dateadd(day,number,DATE_START) as[leavedate],cast(getdate() as date) as [createdate]
 from @tranleave a inner join master.dbo.spt_values  on datediff(day,date_start,date_end)>=number and type='P'


Free Windows Admin Tool Kit Click here and download it now
June 21st, 2015 8:37am

Also this should be working as well

declare @currentDate datetime,@enddate datetime

select @currentDate = DATEADD(d,-1,'06/1/2015');
select @enddate='06/05/2015'
while @currentDate  < @enddate

begin

-- do whatever is needed

select @currentDate =   dateadd(DAY,1,@currentDate)  ;
select @currentDate as currentdate
end

June 21st, 2015 8:43am

Are you using @currentDate in "-- do whatever is needed"?

If it's the case, so everything is right, you're getting the right values.

You are getting dates from 2 to 6/6/2015 because you are selecting the date after adding to it one day.

You can correct your code:

select @currentDate as currentdate

select @currentDate = dateadd(DAY,1,@currentDate);

You will get the dates from 1 to 6/5/2015.

Hope this helps!

Free Windows Admin Tool Kit Click here and download it now
June 21st, 2015 9:41am

Hi Jaseem0712,

Based on the description you posed in the stackoverflow, it seems your are looking for a MERGE statement.

  • "first i want to check whethar that empId is already existing in Trans_details table within that date,,if existing then i want to update particular data.if not exist then only i want to inseart"

To achieve your requirement, please reference below sample.

CREATE TABLE Trans_Details
(
empId INT,
transDateTime DATE,
Created_Date DATE,
LeaveStatus VARCHAR(99)
);
 
CREATE TABLE Trans_Leave
(
EMPLOYEE_NUMBER INT,
NAME VARCHAR(99),
DATE_START DATE,
DATE_END DATE
);

INSERT INTO Trans_Leave
VALUES
(1,'annual leave','20150601','20150610');


;WITH Cte AS
(
SELECT CAST('20150601' AS DATE) DT
UNION ALL
SELECT DATEADD(DAY,1,DT) FROM Cte
WHERE DT<'20150610'
)
,Cte2 AS
(
SELECT * FROM Cte c JOIN Trans_Leave t
				ON c.DT BETWEEN t.DATE_START AND t.DATE_END
)  
MERGE Trans_Details Tar
	USING Cte2 Src
		ON Tar.empId=Src.Employee_number
		AND Tar.transDateTime=Src.DT
    WHEN MATCHED THEN
		  UPDATE SET Tar.LeaveStatus= Src.Name
    WHEN NOT MATCHED THEN
		  INSERT(empId,transDateTime,Created_Date,LeaveStatus)
		  VALUES (Src.Employee_number,Src.DT,GETDATE(),Src.Name);

SELECT * FROM Trans_Details    

If you have any question, feel free to let me know.
June 23rd, 2015 2:42am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics