Find missing rows in sql?

Question

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.

Gordon Linoff · Accepted Answer · 2014-10-20 11:07:46Z

2

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;

answered Oct 20, 2014 at 11:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jay Over a year ago

Thanks a lot for your help!! 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.

Gordon Linoff Over a year ago

@jay . . . I believe the above query answers the question that you asked. If you have another question, then it might be better to ask another question.

Collectives™ on Stack Overflow

Find missing rows in sql?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related