SQL Server : count date with biweekly date -
i having trouble count items biweekly (end @ friday) table like: +------------+------------+ | itemnumber | date | +------------+------------+ | 235 | 2016-03-02 | | 456 | 2016-03-04 | | 454 | 2016-03-08 | | 785 | 2016-03-10 | | 123 | 2016-03-15 | | 543 | 2016-03-18 | | 863 | 2016-03-20 | | 156 | 2016-03-26 | +------------+------------+ result: +-------+------------+ | total | biweek | +-------+------------+ | 4 | 2016-03-11 | | 3 | 2016-03-25 | | 1 | 2016-04-08 | +-------+------------+ if understood problem correctly, should work: select sum(1), dateadd(day, ceiling(datediff(day,4, [date]) / 14.0) * 14, 4) yourtable group dateadd(day, ceiling(datediff(day,4, [date]) / 14.0) * 14, 4) this calculates date difference in days "day 4" aka. 5.1.1900, divides 14 (rounding up) , multiplies 14 biweeks , adds "day 4".