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 : 2086 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

0	thanks pandian.. i have single table only. its working fine now. Gopal_nivas, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts