Get Distinct Date and pass the date and get Count value

Posted by Gopal_nivas under Sql Server on 6/27/2011 | Points: 10 | Views : 1122 | Status : [Member] | Replies : 4
hi..

i want to get distinct dates in my table. in my database table the dates are stored with time(same date will come with different time). so i want to get the distinct date only

my database table date format mentioned below:
2011-06-14 17:52:21.623.
2011-06-24 21:17:38.467
2011-06-20 19:34:18.840
2011-06-10 16:04:49.983
2011-06-17 17:47:24.670
2011-06-09 21:30:51.780
2011-06-08 20:21:58.700
2011-06-07 19:23:58.607
after getting the distinct dates , i will pass the dates and i want to get count value for each date.

i.e)
if i get 10 distinct date value then i want to get 10 rows for date count.

how to get the distinct dates and also i want to get count for those dates.

i need to handle this in a stored procedure.

how to handle this.
need ur suggetsions with examples..
regards
gopal.s




Responses

Posted by: PandianS on: 6/27/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

Please give the table structure for exact solution.... (What are all the tables involve)
(or)
To get Distinct Date from your table...
Select [Dist.Date],Count(1) [Records] From 

(
Select Convert(Varchar,<YourDateColumn>,111) [Dist.Date] From <YourTableName> With(Nolock)
) as Dt Group By [Dist.Date]
Cheers
www.sqlserverbuddy.blogspot.com


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Gopal_nivas, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gopal_nivas on: 6/27/2011 [Member] Starter | Points: 25

Up
0
Down
hi pandian..

thanks for ur reply..
its working fine.
now i want to get the count value of each date. how to get the count based on the distinct date.

how to get the count value.
need ur suggestions
regards
gopal.s

Gopal_nivas, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 6/27/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

Find the Updated script in my first Reply....

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Gopal_nivas, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gopal_nivas on: 6/27/2011 [Member] Starter | Points: 25

Up
0
Down
thanks pandian..

i have single table only.
its working fine now.

Gopal_nivas, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response