counting the current date only

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)

counting the current date only

Author

Topic

newbietosql1221
Starting Member

25 Posts

Posted - 2010-09-19 : 06:22:31

Can someone please help me with a sql statement or query? I need to count the current date when a user submits a request? There are 3 conditions it has to satisfy i think before it can be resolved.

for example:

id date
--- -------------------
4556 9/19/2010 13:14:33
4556 9/19/2010 13:14:44
9834 9/19/2010 20:23:23
9834 9/19/2010 21:36:45
9834 9/19/2010 18:49:32
4475 9/17/2010 04:46:33
2345 9/16/2010 08:43:21
5032 9/16/2010 23:11:51

So with this the answer should be 2 total request, 4556 and 9834

I think there should be a count, a distinct and current date
with a getdate() function but nothing seems to work.
As you can see im not only getting a total current date im getting only a unique entry of each (the id field), Help and thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-09-19 : 06:47:34

You didn't mention what are the 3 condition and the required output.

Anyway this will give you the current day record.


select *
from   tbl
where  [date] >= dateadd(day, datediff(day, 0, getdate()), 0)
and    [date] <  dateadd(day, datediff(day, 0, getdate()), 1)

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-19 : 06:48:43

select id, count(*) as cnt
from your_table
where your_table.date >= dateadd(d,datediff(d,0,getdate()),0)
and your_table.date < dateadd(d,datediff(d,0,getdate()+1),0)
group by id

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-19 : 11:34:25

do you mean this?


SELECT id
FROM table
GROUP BY id
HAVING COUNT(CASE WHEN date >= dateadd(d,datediff(d,0,getdate()),0) 
AND date < dateadd(d,datediff(d,0,getdate()),1) THEN 1 ELSE NULL END) >0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

newbietosql1221
Starting Member

25 Posts

Posted - 2010-09-20 : 10:29:52

Thanks all i was able to figure it out with all of your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-21 : 12:18:42

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

newbietosql1221
Starting Member

25 Posts

Posted - 2010-09-22 : 15:47:03

what is the > symbol is it a greater or redirect

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-23 : 05:50:53

quote:
Originally posted by newbietosql1221

what is the > symbol is it a greater or redirect

"Greater than"

Madhivanan

Failing to plan is Planning to fail

newbietosql1221
Starting Member

25 Posts

Posted - 2010-09-23 : 09:00:19

Can someone please help me again wth removing 3 months of the current year. I have a query that list out everyone month in the table/db in order january to current month. Howerver i dont want to display January thru March.

This is what i have so far that shows every month in order;

so far i got this to display every month including january februar and march, i want to remove january february and march

my_date > DATEADD(yy,-1,GETDATE())
GROUP BY DATEPART(yy,my_Date),DATENAME(mm,my_Date), DATEPART(mm,my_Date)
ORDER BY DATEPART(mm,my_Date)

HELP please!

newbietosql1221
Starting Member

25 Posts

Posted - 2010-09-23 : 09:01:17

sorry i did not mean to say remove i mean dont display ignore the months, jan feb and march

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-23 : 13:15:17

my_date > DATEADD(yy,-1,GETDATE())
AND MONTH(my_date)>3
GROUP BY DATEPART(yy,my_Date),DATENAME(mm,my_Date), DATEPART(mm,my_Date)
ORDER BY DATEPART(mm,my_Date)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

newbietosql1221
Starting Member

25 Posts

Posted - 2010-09-23 : 14:34:29

WOw you are awesome visakh! thanks again. Ready for another, can you show me how you do pagination wtih sql server and php using odbc? yeah that may be a strectch but its worth asking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources