Add multiple records for a date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Add multiple records for a date

Author

Topic

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-12-02 : 03:14:34

Hello Friends,
I have a stored procedure to get the employee details. The stored procedure looks like as follows:

declare @FromDate DATETIME
set @FromDate = '2009-08-01'

declare @ToDate DATETIME
set @ToDate = '2009-11-24'

declare @EmpId varchar(15)
set @EmpId = 'YR'

select LoginTime, LogOutTime,

right(('00' + convert(varchar,diffSeconds/(3600))),2) + ':'+right(('00'+ convert(varchar,diffSeconds%(3600)/60)),2)+':'+ right(('00'+convert(varchar,diffSeconds%60)),2) as 'Time Spent'
from logins
WHERE EMPID = @EmpId
and LoginTime >= @FromDate
AND LoginTime < @ToDate
order by loginTime

The above procedure is showing more than one entry on a date if an employee logs in for more than one time on that date. Is it possible to add multiple login time on a date and show only one record for a date (no matter how many times employee logs in on that date)

Can check the image : http://i789.photobucket.com/albums/yy178/amodi/TEMP.jpg

Thanks.

Sachin.Nand

2937 Posts

Posted - 2009-12-02 : 04:06:57

Should it be the minimum login time or the maximum login time for the day that you want to display having multiple login time?

PBUH

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-12-02 : 04:16:15

quote:
Originally posted by Idera

Should it be the minimum login time or the maximum login time for the day that you want to display having multiple login time?

PBUH

Hello Idera,
It should be maximum login time.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-02 : 05:03:33

this will sum up the time spend for each login-logout session


select 	EMPID,
	FirstLoginTime	= min(LoginTime),
	LastLogOutTime	= max(LogOutTime),
	[Time Spent]	= convert(varchar(10), dateadd(second, sum(datediff(second, LoginTime, LogOutTime)), 0), 108)
from 	logins
WHERE 	EMPID 		= @EmpId
and 	LoginTime 	>= @FromDate
AND 	LoginTime 	< @ToDate
group by EMPID
order by min(LoginTime)

KH
[spoiler]Time is always against us[/spoiler]

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-12-02 : 06:49:57

[quote]Originally posted by khtan
Thanks Khtan,
Can i just add the time spent for each day no matter if there are multiple logins on the same day.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-02 : 07:49:55

the query i posted did just that. It sum up the total time spent

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources