How to Count between Date? - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to Count between Date?

Author

Topic

anabelle
Starting Member

19 Posts

Posted - 2009-02-04 : 04:26:24

Hellow Everyone...i got some delimma here i wud like to retrieve the records of logged users for an specific between dates.I have an example here shown:tablename:Log
LOGID LOGDATETIME
1111 11/16/2007 11:04:00 AM
2222 11/16/2007 11:04:00 AM
3333 12/16/2007 11:04:00 AM
1111 12/19/2007 11:04:00 AM
1111 12/26/2007 11:04:00 AM
2222 12/27/2007 11:04:00 AM
2222 12/30/2007 11:04:00 AM
I wud like to have an output TotallogID = ?; and an Output per LOGID like: 1111=3,How will i write the script on that?and when logdatetime will display it will not include the time, i dont necessarly for need the time...I hope someone cud help me...
Thank You in Advance

ceng_mustafa
Starting Member

1 Post

Posted - 2009-02-04 : 04:52:00

hi friend ,
this is a sample case
you can use aggregate functions ( COUNT, MAX )
SELECT LOGID, COUNT(LOGDATETIME)
FROM Log
GROUP BY LOGID

returns;
1111 3
2222 3
3333 1

IF you want all count then dont use GROUP BY
SELECT COUNT(LOGDATETIME)
FROM Log

returns ;
7
that's it

Mustafa Sahin

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-04 : 04:56:21

declare @table table(LOGID int ,LOGDATETIME datetime)
insert into @table select
1111, '11/16/2007 11:04:00 AM' union all select
2222, '11/16/2007 11:04:00 AM' union all select
3333, '12/16/2007 11:04:00 AM' union all select
1111, '12/19/2007 11:04:00 AM' union all select
1111, '12/26/2007 11:04:00 AM' union all select
2222, '12/27/2007 11:04:00 AM' union all select
2222, '12/30/2007 11:04:00 AM'

declare @startdate datetime, @enddate datetime
select @startdate = '11/16/2007' , @enddate = '12/26/2007'
select distinct logid, max(dateadd(dd,datediff(dd,0,logdatetime),0)) as logdatetime, count(logid) from @table
where logdatetime between @startdate and @enddate
group by logid

anabelle
Starting Member

19 Posts

Posted - 2009-02-04 : 04:59:07

Thank YOU Mustafa and Bklr..i will try it tomorrow i am in hurry to go home....i will let u know i it was a success..have a blissfuld day ahead...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 08:36:22

quote:
Originally posted by anabelle

Hellow Everyone...i got some delimma here i wud like to retrieve the records of logged users for an specific between dates.I have an example here shown:tablename:Log
LOGID LOGDATETIME
1111 11/16/2007 11:04:00 AM
2222 11/16/2007 11:04:00 AM
3333 12/16/2007 11:04:00 AM
1111 12/19/2007 11:04:00 AM
1111 12/26/2007 11:04:00 AM
2222 12/27/2007 11:04:00 AM
2222 12/30/2007 11:04:00 AM
I wud like to have an output TotallogID = ?; and an Output per LOGID like: 1111=3,How will i write the script on that?and when logdatetime will display it will not include the time, i dont necessarly for need the time...I hope someone cud help me...
Thank You in Advance


SELECT LOGID, COUNT(*)
FROM Log
WHERE LOGDATETIME >= @StartDate
AND LOGDATETIME < @EndDate+1
GROUP BY LOGID

@StartDate and @EndDate are datetime values you pass

anabelle
Starting Member

19 Posts

Posted - 2009-02-05 : 04:18:18

Mustafah it was easy..thank u it works... i just finish my report and thank visahk16 and bklr...i was able to try it and it really works...thank u so much...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources