I have the Below Data in my Table.
| Id | FeeModeId |Name | Amount|
---------------------------------------------
| 1 | NULL | NULL | 20 |
| 2 | 1 | Quarter-1 | 5000 |
| 3 | NULL | NULL | 2000 |
| 4 | 2 | Quarter-2 | 8000 |
| 5 | NULL | NULL | 5000 |
| 6 | NULL | NULL | 2000 |
| 7 | 3 | Quarter-3 | 6000 |
| 8 | NULL | NULL | 4000 |
How to write such query to get below output...
| Id | FeeModeId |Name | Amount|
---------------------------------------------
| 1 | NULL | NULL | 20 |
| 2 | 1 | Quarter-1 | 5000 |
| 3 | 1 | Quarter-1 | 2000 |
| 4 | 2 | Quarter-2 | 8000 |
| 5 | 2 | Quarter-2 | 5000 |
| 6 | 2 | Quarter-2 | 2000 |
| 7 | 3 | Quarter-3 | 6000 |
| 8 | 3 | Quarter-3 | 4000 |
Since you are on SQL Server 2012... here is a version that uses that. It might be faster than other solutions but you have to test that on your data.
sum() over()
will do a running sum ordered by Id
adding 1
when there are a value in the column and keeping the current value for null
values. The calculated running sum is then used to partition the result in first_value() over()
. The first value ordered by Id
for each "group" of rows generated by the running sum has the value you want.
select T.Id,
first_value(T.FeeModeId)
over(partition by T.NF
order by T.Id
rows between unbounded preceding and current row) as FeeModeId,
first_value(T.Name)
over(partition by T.NS
order by T.Id
rows between unbounded preceding and current row) as Name,
T.Amount
from (
select Id,
FeeModeId,
Name,
Amount,
sum(case when FeeModeId is null then 0 else 1 end)
over(order by Id) as NF,
sum(case when Name is null then 0 else 1 end)
over(order by Id) as NS
from YourTable
) as T
SQL Fiddle
Something that will work pre SQL Server 2012:
select T1.Id,
T3.FeeModeId,
T2.Name,
T1.Amount
from YourTable as T1
outer apply (select top(1) Name
from YourTable as T2
where T1.Id >= T2.Id and
T2.Name is not null
order by T2.Id desc) as T2
outer apply (select top(1) FeeModeId
from YourTable as T3
where T1.Id >= T3.Id and
T3.FeeModeId is not null
order by T3.Id desc) as T3
SQL Fiddle
Please try:
select
a.ID,
ISNULL(a.FeeModeId, x.FeeModeId) FeeModeId,
ISNULL(a.Name, x.Name) Name,
a.Amount
from tbl a
outer apply
(select top 1 FeeModeId, Name
from tbl b
where b.ID<a.ID and
b.Amount is not null and
b.FeeModeId is not null and
a.FeeModeId is null order by ID desc)x
OR
select
ID,
ISNULL(FeeModeId, bFeeModeId) FeeModeId,
ISNULL(Name, bName) Name,
Amount
From(
select
a.ID , a.FeeModeId, a.Name, a.Amount,
b.ID bID, b.FeeModeId bFeeModeId, b.Name bName,
MAX(b.FeeModeId) over (partition by a.ID) mx
from tbl a left join tbl b on b.ID<a.ID
and b.FeeModeId is not null
)x
where bFeeModeId=mx or mx is null
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With