CODEWITHSUNDEEP
sqlsql-servert-sql
Afer
Afer

Reputation: 19

SQL Show 0 Values when no values between date range

i have some problems with my sql query. Im selecting some values with a specific date range. for example 2018-05-01 to 2018-05-13 this is the output.

SUM  CalendarWeek
8    18
5    19

If the user will select now a date between 2018-04-01 and 2018-05-13 i want to show a 0 instead when there are no values.

For example:

SUM  CalendarWeek
0    13
0    14
0    15
0    16
0    17
8    18
5    19

My Query:

SELECT SUM(Codes) AS 'Sum', CW FROM(
SELECT Count(*) AS 'Codes', DATEPART(wk, ScanDate) AS 'CW',
FROM [Table]
WHERE CONVERT(date, ScanDate, 102) >= '2018-01-01' AND CONVERT(date, ScanDate, 102) <= '2018-05-13' 
GROUP BY ScanDate,  DATEPART(wk, ScanDate) 
UNION ALL 
SELECT Count(*) AS 'Codes', DATEPART(wk, ScanDate) AS 'CW', ScanDate
FROM [Table_Archive] 
WHERE CONVERT(date, ScanDate, 102) >= '2018-01-01' AND CONVERT(date, ScanDate, 102) <= '2018-05-13'
GROUP BY ScanDate, DATEPART(wk, ScanDate)) test 
GROUP BY CW, ScanDate
ORDER BY CW ASC

any ideas how to solve this? Thanks

Upvotes: 0

Views: 1811

Answers (3)

Abhishek Awasthi
Abhishek Awasthi

Reputation: 1

You can use IF in MySQL:

Select  IF(sum=null, 0,sum) as sum from table_name

If you don't find solution now from this you can comment again I will give the exact query

Upvotes: -1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

First, you have to maintain calender table for such kind of task. If, so then use them. else would need to use recursive cte

declare @stardate date, @enddate date

set @stardate = '2018-04-01' 
set @enddate = '2018-05-13'

with t as (
    select DATEPART(ISO_WEEK, @stardate) as CalendarWeek
    union all
    select CalendarWeek+1
    from t
    where CalendarWeek < DATEPART(ISO_WEEK, @enddate)
)
select t1.sum, coalesce(t.CalendarWeek, 0) CalendarWeek
from t
left join table t1 on t1.CalendarWeek = t.CalendarWeek

Upvotes: 2

addohm
addohm

Reputation: 2455

SELECT ISNULL(SUM, 0), CalendarWeek

Upvotes: 0

Related Questions