adding rows with 0 values

Question

I have a result set like:

fullDate            service          Monthlybilled
-----------------------------------------------------
2015-08-01          wash car         300.00
2015-10-01          brake test       500.00
2015-12-01          oil change       200.00

I need to show another one like:

fullDate            service          Monthlybilled
-----------------------------------------------------
....

2015-07-01          none             0.00
2015-08-01          wash car         300.00
2015-09-01          none             0.00
2015-10-01          brake test       500.00
2015-11-01          none             0.00
2015-12-01          oil change       200.00       
2016-01-01          none             0.00
...

i.e. for those months where no activity was "detected" I need to add a row that says none and 0.00

Any suggestions, please? Thanks

score 1 · Answer 1 · answered Apr 10 '17 at 18:35

Create a temp table containing all the month values you need.

Then use LEFT OUTER JOIN to the table with your service dates.

SELECT
  alldates.fullDate,
  COALESCE(service.service, 'none') AS service,
  COALESCE(service.MonthlyBilled, 0.00) AS MonthlyBilled
FROM alldates
LEFT OUTER JOIN service
  ON alldates.fullDate = service.fullDate

score 1 · Accepted Answer · edited May 23 '17 at 12:10

There are 3 ways to do this:

Write a query that generated all the dates between the range (as explained in this SO answer) and perform a LEFT JOIN with it to get all the dates with zero entries.
Create a calendar table which maintains all the dates and LEFT JOIN it with current query to get all the records.
Write the logic in your application/service layer to fill the missing dates with 0 values.

I would recommend option 3.

adding rows with 0 values

2 Answers2