Identiy missing rows and calculate them in SQL

Question

I have the follwing data. Need to identify the missing rows and calculate the average

LineNo Field1 Field2 Year Average
1 France Paris 2019 100
2 France Paris 2017 300
3 France Nice 2018 200
4 Italy Rome 2019 50
5 Italy Rome 2018 500
6 Italy Rome 2017 250

Need to calculate the average for all years (2019,2018,2017).

So for France and Paris combination - Year 2018 is missing.
So for France and Nice - Year 2019 and 2017 is missing. For Italy and Rome combination, we have all records.

So for missing combination need to calculate the average like below ( formula, sum/3 in all cases, irrespective of number of years present)

The final data looks like below.

LineNo Field1 Field2 Year Value
1 France Paris 2019 100
2 France Paris 2017 300
3 France Nice 2017 200
4 Italy Rome 2019 50
5 Italy Rome 2018 500
6 Italy Rome 2017 250
*7 France Paris 2018 133
*8 France Nice 2018 66
*9 France Nice 2019 66

Line 7,8,9 are the new ones

What defines the complete list of years you need to evaluate? Why isn't 2016 missing from all of them? — xQbert, May 23 '19 at 14:15

score 1 · Answer 1 · answered May 23 '19 at 14:12

You can use something like below to get the blank row and after that an update statement can update the '0' values to average value

SELECT DISTINCT
        [year]
 INTO   #allyears
 FROM   #temp
 -- this is to get all the years in a lookup  
 ;WITH   cte_t
          AS ( SELECT   place1 ,
                        place2 ,
                        CASE WHEN a.year = t.year THEN val
                             ELSE 0
                        END AS val ,
                        a.year ,
                        ROW_NUMBER() OVER ( PARTITION BY place1, place2,
                                            a.year ORDER BY CASE
                                                              WHEN a.year = t.year
                                                              THEN val
                                                              ELSE 0
                                                            END DESC ) rn
               FROM     #allyears AS a
                        CROSS JOIN #temp AS t
             )
    SELECT  place1 ,
            place2 ,
            year ,
            val 
    FROM    cte_t 
    WHERE rn = 1

Identiy missing rows and calculate them in SQL

1 Answers1