Query not displaying the week value as 0

Question

SELECT datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, EntryDate), 0)), 0), EntryDate - 1) + 1 as week,
count(UserID) as balance
FROM table_points 
where right(convert(varchar, EntryDate, 106), 8)='Mar 2015'
GROUP BY datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, EntryDate), 0)), 0), EntryDate - 1) + 1

When i execute this query i get result for 4th and 5th week values but not for others week. Like

week balance
4    80
5    100

In first 3 weeeks there is no values in database so it will not showing the values . Can any one bring 0 value for 1st , 2nd and 3rd week.

I want result as

week balance
1    0
2    0
3    0  
4    80
5    100

can any one help me out.

If there are no dates during week 1 - 3, your result will not include those weeks. (Add a separate week-table if 0 balance weeks are required.) — jarlh, May 13 '15 at 12:56
Assume In DB from Day 1 to Day 31 for a month there is a points ...IF am selecting March month , From Day 23 to Day 31 am having point value but day 1-22 am having 0 value.. While execute this query am getting result as ------------------------- Week Points ------------------------- 4 520 ------------------------- 5 500 ________________ But i want 1st,2nd and 3rd week points as 0....jarlh — IRSHAD SAHEB, May 13 '15 at 13:09

PSVSupporter · Answer 1 · 2015-05-13T13:14:40.923

0

Create a calendar/date table, including all possible dates/weeks, and from there left join the table_points table. If no data is present in table_points, it will return 0 for those weeks.

If no dates are present in a table, it's impossible to select these and get a zero.

Example:

SELECT d.week, count(t.UserID)
FROM dates d left join table_points t on
     d.date = t.EntryDate
where right(convert(varchar, d.date, 106), 8)='Mar 2015'
group by d.week

A date table can be very useful for many applications and could be filled like:

date       week year month WeekOfMonth DayOfWeek Day     Month
2015-03-01 18   2015 3     1           5         Friday  March
2015-03-02 18   2015 3     1           6         Friday  March
2015-03-03 18   2015 3     1           7         Sunday  March
2015-03-04 19   2015 3     2           1         Monday  March

edited May 13 '15 at 13:14

answered May 13 '15 at 12:57

PSVSupporter

248
3
16

1

this should be a comment my friend. – Chiragkumar Thakar May 13 '15 at 13:03
Why? It's answering his question, isn't it? – PSVSupporter May 13 '15 at 13:04
this is kind of suggestion, not an answer. – Chiragkumar Thakar May 13 '15 at 13:05
1

I changed it, better now? – PSVSupporter May 13 '15 at 13:16
If u choose a particular month means there will be only 5 weeks in a month or less than 5 ok. In my code after day 23-31 day from march month am getting the value as 4th week and 5th week but getting value for 1st,2nd and 3rd week as 0 if there is '0' value for days means it will show as 0 for weeks. --- PSVSupporter-- – IRSHAD SAHEB May 13 '15 at 13:23
What are you trying to say? Did you try my solution? If you want to count weeks within your selection, the date table only should have dates and in your selection you can calculate the weeks, like you did in your query. – PSVSupporter May 13 '15 at 13:31

score 0 · Answer 2 · edited May 23 '17 at 11:58

This works like PSVSupporter's solution but uses a CTE to get a date list instead of using another table:

;WITH Dates AS (
        SELECT
         [Date] = CONVERT(DATETIME,'03/01/2015')
        UNION ALL SELECT
         [Date] = DATEADD(DAY, 1, [Date])
        FROM
         Dates
        WHERE
         Date < '03/31/2015'
)
SELECT 
datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, ds.Date), 0)), 0), ds.Date - 1) + 1 as week,
count(tp.UserID) as balance
FROM Dates ds
left join table_points tp on tp.EntryDate = ds.Date
WHERE right(convert(varchar, ds.Date, 106), 8)='Mar 2015'
GROUP BY datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, ds.Date), 0)), 0), ds.Date - 1) + 1

I took CTE from https://stackoverflow.com/a/7824893/3309109

Query not displaying the week value as 0

2 Answers2