SQL Server : force results for all distinct values in aggregate -

I am trying to reveal the results for different values ​​of a column in my result set. In other words, I would like to show any particular value as zero, instead of leaving it, if there is no value in a given group.

For example, my data is as follows:

  id | Quality | Date | Dollar ------------------------------------- 1 | Good 02/28/15 | 1 2 | Better 02/28/15 | 2 3 | Best. 02/28/15 | 3 4 | * Fair * | 02/28/15 | 1 1 | Good 01/31/15 | 1 2 | Better 01/31/15 | 2 3 | Best. 01/31/15 | 3 1 Good 12/31/15 | 1 3 | Best. 12/31/15 | 3  

I've starred "fair" to draw attention to it, because as a result a specific value in the set should be "fair". However, "Fair" will not show for any other month if I group by "Date".

I find it harder than everything I've done to solve the problem, but I'm having trouble finding out which would be easy.

I did the following:

 
 
  Announce @currdt date = '02 / 28/2015 'Announcement @prevdt date = DATEADD (D, -1, DATEADD (mm, dATEDIFF (m, 0, @ currdt, 0)) - All dates of the month before date @ Date (date 1 date) declared; Insert in the date @ (date1) value (@currdt), (@prevdt); Select a specific quality, with 1 day from mytableA (choose * from date * (1 * 1), history (ID, quality, Add_Date, dollar) (ID, quality, Add_Date, Select dollar) from MytableA where Add_Date & lt; = @currdt and add_Date & gt; = @prevdt) q.quality, q.date, sum (h.Dollars) properties, q select history h h .quality = q.quality and h at history j. Date = q.date group by q.quality, q.date  
 
 The desired result should be based on the table shown above: 
 
  quality | Date | Dollar ------------------------------ Fair 02/28/15 | 1 good 02/28/15 | 1 better | 02/28/15 | 2 best 02/28/15 | 3 fair 01/31/15 | 0 good 01/31/15 | 1 better | 01/31/15 | 2 best 01/31/15 | 3  
 
 I should list some duplication properties in a month so that the money can be more interesting, but hopefully it is clear. 
 

If your goal is to reduce the length of the query then it should be as much as you have now Only this should show all the properties for both dates):

  Announce @currdt date = '02 / 28/2015 'Announcement @prevdt date = DATEADD (d, - 1, DATEADD (MM, DATEDIFF (m, 0, @ currdt), 0)) --last month before select a.quality, a.date, sum (isnull (b.dollars, 0)) In the form (select quality, d.tate from mytableA, (VALUES (@currdt), (@ Prevdt)) AS D (date) quality by group, d.date) join a left mytableA b on a.Date = b.date and a.Quality = b.Quality group a.quality, a.date command 2 desc, 3 asc  

This output will be from your sample data:

  quality date dollar fair 2015-02-28 1 good 2015-02-28 1 better 2015-02- 28 2 Best 2015-02-28 3 Fair 2015-01-31 0 Good 2015-01-31 1 Better 2015-01-31 2 Best 2015-01-31 3 If you want to keep order for quality ( fair - & gt; Good - & gt; Better - & gt; Best ) You can: choose 
 
  as an amount from a.quality, a.date, sum (isnull (b.Dollars, 0)) (quality (Value (1, 'fair'), (2, 'good'), (3, 'best'), (4, 'best'), (values ​​(as in sort, quality) @kard), (@prevd)) as d (date) group by q.quality, d.date, sort) a left mytableA b include a.Date = b.date and a.Quality = b Sort by sorted by quality group a.quality, A.date, date