here is one way.; with daterange as( SELECT frDate = convert(date, '2013-01-01'), toDate = convert(date, '2013-01-05') union all SELECT frDate = '2013-01-06', toDate = '2013-01-12' union all SELECT frDate = '2013-01-13', toDate = '2013-01-19' union all SELECT frDate = '2013-01-20', toDate = '2013-01-26' union all SELECT frDate = '2013-01-27', toDate = '2013-01-31')SELECT count(distinct IAV) as IavCount, convert(varchar(10), frDate, 101) + ' - ' + convert(varchar(10), toDate, 101) as EachWeekFROM IavInfo i INNER JOIN daterange d ON i.ScanDate BETWEEN frDate AND toDateWHERE IAV <> 'N/A'GROUP BY frDate, toDateORDER BY frDate
KH[spoiler]Time is always against us[/spoiler]