SQLTeam.com | Weblogs | Forums

SQL Query to populate the null records with upper actual value

sampledata

I have attached the sample source and tartget data. I need to write a simple sql query to get the required output.

Thanks in advance.

Regards,
Vipin Jha

What is not clear from your screenshot is why the rows are ordered the way they are. By definition, a table is an unordered set. So unless you have some criterion for ordering the data, there is no guarantee that the data is stored in the order you think it is, or will be returned in the order you expect/want.

If you had something that you can use to order the data by, for example an ID column, then you can write the query something like this (I am making the assumption that YRMO represents year and month, and that the Id will order it by the YRMO).

SELECT
    NewYRMO = MAX(YRMO) OVER (ORDER BY ID),
    Measure, 
    mship,
    mship2
FROM
    YourTable
ORDER BY
    NewYRMO;

How did you determine that the row with Measure DEF and mship of 78 and mship2 of 202 should be assigned the YRMO of 202001? Why isn't the row with measure DEF, mship of 986 and mship2 of 78 assigned the YRMO of 202001?

How did you generate the data for the first table?