How to groupby week from a given date range SQL Server and return count 0 if no data found for a week

Question

I want the selected data in below format.

TOTAL_FEE                WEEK
+---------------+--------------+
  24000                 WEEK_1
  24000                 WEEK_2
  24000                 WEEK_3

My query that give me result groupby date is :

select  count(*) * 24000, CAST(p.submited_date  AS DATE)
from Form_For_Business_Name_Registration p 
where p.STATUS !='NS' 
and CAST(p.submited_date  AS DATE)>='2016-01-22' 
and CAST(p.submited_date  AS DATE)<='2016-03-04'   
GROUP BY CAST(p.submited_date  AS DATE)

What i want is to get results groupby weeks but weeks should be calculated from the given date range.

Note : if there is no data for the week it should return 0 counts for that week.

Will somebody guide me to write a SQL query to get desired output.

Thank you!

Possible duplicate of [GROUP BY WEEK with SQL](http://stackoverflow.com/questions/13278552/group-by-week-with-sql) — Tab Alleman, Mar 04 '16 at 13:54

score 1 · Accepted Answer · answered Mar 04 '16 at 12:53

1

You can use DATEPART, like:

DATEPART(week, submited_date)

I would advise using the week number and year number (similar to MySQL's WeekYear) in order to ensure the grouping is specific to that year's week.

answered Mar 04 '16 at 12:53

Anthony

36,459
25
97
163

Thank you Anthony.. it is what i was looking for . – Hemant Chhonkar Mar 06 '16 at 06:27
I more help... What is happening now is , it returns me 2 rows if data is found for 2 weeks . What i want is , if there is no data for the week it should return 0 counts for that week. – Hemant Chhonkar Mar 06 '16 at 07:15
This is a common goal that (generally) requires having a utility table that has every date ( a calendar table, as suggested in another answer ) that you can use to join your current table, allowing for those "empty dates" to be used for a 0 count row. I asked the exact same question (only for mysql) probably 5 or 7 years ago. – Anthony Mar 07 '16 at 10:22
http://stackoverflow.com/questions/2071595/generate-a-row-in-an-aggregation-query-for-a-date-with-no-results-against-it ; http://stackoverflow.com/questions/3120196/how-can-i-create-a-series-of-months-to-join-sparse-data-to ; http://stackoverflow.com/questions/6310839/sql-group-by-including-empty-rows ; http://stackoverflow.com/questions/9575271/generate-empty-rows-even-if-empty-between-2-dates ; http://stackoverflow.com/questions/1122510/mysql-query-to-get-non-data-for-unassigned-dates – Anthony Mar 07 '16 at 10:27

score 0 · Answer 2 · answered Mar 04 '16 at 12:50

You can take the difference to calculate weeks:

select count(*) * 24000,
       (DATEDIFF(day, CAST(p.submited_date AS DATE), '2016-01-22') / 7) as weeknum
from Form_For_Business_Name_Registration p 
where p.STATUS <> 'NS' and
      CAST(p.submited_date  AS DATE) >= '2016-01-22' and
      CAST(p.submited_date  AS DATE) <= '2016-03-04'   
group by (DATEDIFF(day, CAST(p.submited_date AS DATE), '2016-01-22') / 7);

score 0 · Answer 3 · answered Mar 04 '16 at 12:56

0

Try this (if I understood what you want)

GROUP BY DATEPART(wk, p.submited_date)

answered Mar 04 '16 at 12:56

knagaev

2,897
16
20

TheGameiswar · Answer 4 · 2016-03-04T13:32:51.800

I recommed using calendar table...Below is the sample query output from calendar table..once you have the table,all you need to do is join two tables and filter rows...

EX:

create table 
#test
(
tt datetime
)

insert into #test
select dateadd(day,-n,getdate())
from numbers 
where n<10


select 
* from 
#test

---now if i want to find which week those fall,all i have to do is join calendar table

select 
tt,wkno
from 
#test t1
join 
calendar c
on c.date=t1.tt

How to groupby week from a given date range SQL Server and return count 0 if no data found for a week

4 Answers4