Date Difference Between to dates

Question

Date Difference Between to dates

sqlfriends@sql.com

SSCommitted

Points: 1504
More actions
November 3, 2010 at 5:39 am

#242600

Dear All;
Any one help to solve this problem
CIDEmpId EDATETIMEs FREE DATE
10705400442010-11-16 14:00:00.00017,18
10705400452010-11-19 14:00:00.00020 AFTERNOON
10705400152010-11-20 09:30:00.00021
10705400492010-11-22 14:00:00.00023,24,25,26
10705400242010-11-27 09:30:00.000-
10705400072010-11-27 14:00:00.000-
I need the Sample output like this ,
The table having cid,Empid,Edatetimes , so we have the difference between the first date and second date the freedates are display the other column , how can i differ the dates like this.... sample output.
Thanks;
Faijur.
Coimbatore

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, sample data and expected results as per http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

faijurrahuman17 (11/3/2010)
Dear All;
Any one help to solve this problem
CIDEmpId EDATETIMEs FREE DATE
10705400442010-11-16 14:00:00.00017,18
10705400452010-11-19 14:00:00.00020 AFTERNOON
10705400152010-11-20 09:30:00.00021
10705400492010-11-22 14:00:00.00023,24,25,26
10705400242010-11-27 09:30:00.000-
10705400072010-11-27 14:00:00.000-
I need the Sample output like this ,
The table having cid,Empid,Edatetimes , so we have the difference between the first date and second date the freedates are display the other column , how can i differ the dates like this.... sample output.
Thanks;
Faijur.
Coimbatore

declare @temp table (CID int,EmpId varchar(20) ,EDATETIMEs datetime,dupedatetimes datetime)

declare @temp1 table (CID int,EmpId varchar(20) ,EDATETIMEs datetime,dupedatetimes datetime,Rowid int)

declare @temp2 table (CID int,EmpId varchar(20) ,EDATETIMEs datetime,dupedatetimes datetime,Rowid int,Newrowid int,Freedatetimes varchar(200))

Insert into @temp

Select 1, '070540044', '2010-11-16 14:00:00.000',''

union Select 1, '070540045', '2010-11-20 04:00:00.000',''

union Select 1, '070540015', '2010-11-20 09:30:00.000',''

union Select 1, '070540049', '2010-11-22 14:00:00.000',''

union Select 1, '070540024', '2010-11-27 09:30:00.000',''

union Select 1, '070540007', '2010-11-27 14:00:00.000',''

union Select 1, '070540008', '2010-11-28 14:00:00.000',''

-- inserting into temp table with row wise

insert into @temp1

select *,ROW_NUMBER() over (order by EDATETIMEs) from @temp

-- updating row wise to our needy

Update @temp1 set Rowid=1 where Rowid%2<>0

Update @temp1 set Rowid=2 where Rowid%2=0

-- Again inserting into temp table with row wise

insert into @temp2

Select *,ROW_NUMBER() over (partition by rowid order by EDATETIMEs) as Newrowid,'' from @temp1

--Updating duplicate row with our new value

declare @I int

Set @I=1

while @I <=(Select COUNT(*) from @temp2 where Rowid=1)

begin

update a set a.dupedatetimes=(Select B.EDATETIMEs from @temp2 as b where Rowid=2 and Newrowid=@I)

from @temp2 as a where Rowid=1 and Newrowid=@I

set @I=@I+1

end

--- Need to join with Tally table and looping them to get the different dates

-- Note Need Tally table for it you can have tally table from http://www.sqlservercentral.com/articles/T-SQL/62867/

declare @j-2 int

DECLARE @startDate datetime

DECLARE @endDate datetime

declare @coalesce varchar(4000)

set @coalesce=''

set @j-2 =1

while @j-2<=(Select COUNT(*) from @temp2 where Rowid=1 )

begin

SELECT @startDate = edatetimes, @endDate = dupedatetimes from @temp2 where Rowid=1 and Newrowid=@J

SELECT @coalesce =@COALESCE+convert(varchar,DATEADD(day, T.N, convert(varchar,@StartDate,101)))+','

FROM dbo.Tally T

WHERE (T.N > 0 AND T.N < DATEDIFF(day, @startDate, @endDate))

ORDER BY T.N

SELECT @coalesce =@COALESCE

Update @temp2 set Freedatetimes =@COALESCE where Rowid=1 and Newrowid=@J

set @coalesce =''

--Select * from @temp2 where Rowid=1 and Newrowid=@J

set @j-2=@J+1

end

/*Finally we are Getting */

Select CID,EmpId,Edatetimes,Freedatetimes from @temp2 where Rowid=1

I know we can have different way but i have tried in this way.And dont say that it is 'Nov 17 2010 12:00AM,Nov 18 2010 12:00AM,Nov 19 2010 12:00AM,' you try to convert to date filed

Again i am telling there will be differenct way try it out or find it out iif you got better solution than this post back it

Thanks

Parthi

Thanks
Parthi

shanu.hoosen SSCrazy Points: 2227 More actions · Answer 3

please check your scripts. you got 4 columns and inserting 3 values.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 4

shanu.hoosen (11/4/2010)
please check your scripts. you got 4 columns and inserting 3 values.

HI

Did u run that script and had the tally table with you.I dont find any issue in running the script.pls verfy once again.The script may not look good but script is running without error

Thanks

Parthi

Thanks
Parthi