Grouping by days??

[venerable bede]

Hi

I have the following query :

select count(*) as Produced from prodTable where DateCreated between @startdate and @enddate) Produced,

which returns a count of records created between 2 dates. These dates are @startdate = 1 week ago @enddate = today

What I now need is a result set which gives me a count by day and returns the days name

e.g

Tuesday, 24
Wednesday, 12,
Thursday, 34
Friday, 2 .....

Any ideas ?

[FunkyDexter]

Is DateCreate a Date or a DateTime? I'm going to assume it's a date.

Your basic query is

Select DateCreated, Count(*)
from prodTable 
where DateCreated between @startdate and @enddate
group by DateCreated

You can then use the DateName function to return the date in a freindlier format.

[TysonLPrice]

Here is a basic example to show you what you need to do. MS SQL Server 2005

Create table ##VBForums(MyDate datetime)

insert into ##VBForums(MyDate) values(GetDate())
insert into ##VBForums(MyDate) values(GetDate())

insert into ##VBForums(MyDate) values(GetDate() + 1)
insert into ##VBForums(MyDate) values(GetDate() + 1)

insert into ##VBForums(MyDate) values(GetDate() + 2)
insert into ##VBForums(MyDate) values(GetDate() + 2)

insert into ##VBForums(MyDate) values(GetDate() + 3)
insert into ##VBForums(MyDate) values(GetDate() + 3)

select datename(dw, MyDate), count(MyDate)
from ##VBForums
group by datename(dw, MyDate)

drop table ##VBForums

FunkyDexter beat me to it. Between the two examples you should have what you need.