CODEWITHSUNDEEP
sqlsql-serversql-server-2008
jay
jay

Reputation: 3869

Find missing rows in sql?

Please help me to fix the below issues, I have below table, A column is the quarter no and b as the currency and c as amount column. For e.g. quarter 1 has 1, 2 &3 currency, but quarter 2 and 3 has 2 &3 and 3 currency only. In this scenario I want to find out missing currency and insert the previous quarter amount.

 Quarter  currency Amount    
1   1   45.000
1   2   425.000
1   3   145.000
3   2   400.000
3   3   145.000
4   3   145.000

Output:

   Quarter  currency Amount 
1   1   45.000
1   2   425.000
1   3   145.000
2   1   45.000
2   2   425.000
2   3   145.000
3   1   45.000
3   2   425.000
3   3   145.000
4   1   45.000
4   2   425.000
4   3   145.000

Quarter is sequences number, Some quarter is not present in the sequence want to copy previous quarter values and While doing we have to check if missing quarter previous and next quarter should be available.

Upvotes: 0

Views: 290

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

This has two important pieces to it. The first is getting the right rows out. You can do that with a cross join. Then you need to get the previous quarter's value. Here is one method for that, using a correlated subquery:

select q.quarter, c.currency,
       (select top 1 amount
        from table t
        where t.currency = c.currency and t.quarter <= q.quarter
        order by quarter desc
       ) amount
from (select distinct quarter from table) q cross join
     (select distinct currency from table) c;

Upvotes: 2

Related Questions