Complie a list of dates and count of how many within these dates

like this?

the standard master.dbo.spt_values type=4 has a range of 0-2047  is that sufficient?

Select rangename as [date],count|(*) as requiredcount
  from youtable as A
 Inner Join (
select dateadd(d,7*v.number,'20110207') as startdate
      ,dateadd(d,(7*v.number)+6,'20110207 23:59:59.997')
        as enddate
      ,convert(char(10),dateadd(d,7*v.number,'20110207'),101) as rangename
  from master.dbo.spt_values as v
 where v.type='p'
   and v.number between 0 and 12
             ) as x
   on a.daterequired 
      between x.startdate and x.enddate
group by rangename
order by 1

Select allOpen in new window

Apologies but I do not know what master.dbo.spt_values refers to but if this refers to number rows outputed there should not be more than 1 year.

I'll test the code.

master.dbo.spt_values is a system table in the master database of sql server...
it can be used as a source of a list of numbers ...

Select rangename as [date],count|(*) as requiredcount
  from youtable as A
 Inner Join (
select dateadd(d,7*v.number,'20110207') as startdate
      ,dateadd(d,(7*v.number)+6,'20110207 23:59:59.997')
        as enddate
      ,convert(char(10),dateadd(d,7*v.number,'20110207'),101) as rangename
  from master.dbo.spt_values as v
 where v.type='p'
   and v.number between 0 and 12  --<  set you max number of weeks (max value 2047)
             ) as x
   on a.daterequired 
      between x.startdate and x.enddate
group by rangename
order by 1

Select allOpen in new window

Hi lowspeed, I've tried the code as:

Select daterequired as [date],count(*) as requiredcount
  from tbl_test as A
 Inner Join (
select dateadd(d,7*v.number,'20110207') as startdate
      ,dateadd(d,(7*v.number)+6,'20110207 23:59:59.997')
        as enddate
      ,convert(char(10),dateadd(d,7*v.number,'20110207'),101) as daterequired
  from master.dbo.spt_values as v
 where v.type='p'
   and v.number between 0 and 12  --<  set you max number of weeks (max value 2047)
             ) as x
   on a.daterequired
      between x.startdate and x.enddate
group by tbl_test.daterequired
order by 1

but recieve an error:
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "tbl_test.daterequired" could not be bound.
Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'daterequired'.

I'm not I've added the column name in the correct [places.

That is because daterequired belongs to x and not to tbl_test.  In other words, change:
group by tbl_test.daterequired
for:
group by x.daterequired

aceperkins,

I have changed this as suggested:

Select datecompleted as [date],count(*) as requiredcount
  from tbl_test as A
 Inner Join (
select dateadd(d,7*v.number,'20110207') as startdate
      ,dateadd(d,(7*v.number)+6,'20110207 23:59:59.997')
        as enddate
      ,convert(char(10),dateadd(d,7*v.number,'20110207'),101) as daterequired
  from master.dbo.spt_values as v
 where v.type='p'
   and v.number between 0 and 12  --<  set you max number of weeks (max value 2047)
             ) as x
   on a.datecompleted
      between x.startdate and x.enddate
group by group by x.daterequired
order by 1

but now recieve the following error:

Msg 8120, Level 16, State 1, Line 2
Column 'tbl_test.datecompleted' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Sharath_123,

that's excellent - working exactly as needed! thanks

Glad I could help you.