CODEWITHSUNDEEP
sql-server
sylanter
sylanter

Reputation: 49

SQL Server : count date with biweekly date

I having trouble to count items biweekly (end at Friday)

Table is 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 |
+-------+------------+

Upvotes: 1

Views: 1246

Answers (2)

pedram bashiri
pedram bashiri

Reputation: 1376

SELECT anyColumn, …, dateadd(week, datediff(week, 0, dateColumn) / 2 * 2 , 0) as biweekly
FROM table
WHERE condition
GROUP BY anyColumn, …, dateadd(week, datediff(week, 0, dateColumn) / 2 * 2 , 0)

This calculates how many weeks dateColumn is far from date 0, then the quotient of dividing this difference by 2 is multiplied by 2 which gives you 2 week intervals. By adding these 2 week intervals to date 0, you will get 2 week periods.

Upvotes: 0

James Z
James Z

Reputation: 12317

If I understood your problem correctly, something like this should work:

select 
  sum(1), 
  dateadd(day, ceiling(datediff(day,4, [Date]) / 14.0) * 14, 4) 
from
  yourtable
group by 
  dateadd(day, ceiling(datediff(day,4, [Date]) / 14.0) * 14, 4)

This calculates the date difference in days to "day 4" aka. 5.1.1900, divides it with 14 (rounding up) and multiplies by 14 to get biweeks and then adds that to the "day 4".

Upvotes: 4

Related Questions