I got this query to fill down empty cells with values from a previous non-empty row but it's taking so long (there are almost 100.000 records).
Hope you can help me with a faster query to achieve this .
These are all the dll with a sample data:
CREATE TABLE #TEST_INSURANCE_PAYMENTS(
id_num int IDENTITY(1,1),[Provider] varchar(59), [Location] varchar(104),last_update datetime2, [Total_Charge] money
);
INSERT INTO #TEST_INSURANCE_PAYMENTS
([Provider], [Location],[last_update],[Total_Charge])
VALUES
('Vimalkumar Veerappan', 'Arizona Heart Specialists',CURRENT_TIMESTAMP,100.0),
(' ', 'Banner Boswell Medical Center - Inpatient',CURRENT_TIMESTAMP,102.0),
(' ', 'Arizona Heart Specialists WEST',CURRENT_TIMESTAMP,800.0),
('Akash Makkar', 'Arizona Heart Specialists WEST',CURRENT_TIMESTAMP,500.0),
(' ', 'Pinnacle Vein & Vascular Center Sun City',CURRENT_TIMESTAMP,500.0),
(' ', 'Abrazo Arizona Heart Hospital - Outpatient',CURRENT_TIMESTAMP,60.0),
(' ', 'Banner Boswell Medical Center - Inpatient',CURRENT_TIMESTAMP,60.0),
(' ', 'Banner Del E Webb Medical Center - Inpatient',CURRENT_TIMESTAMP,10.0)
select id_num,[Provider],[Location],[Total_Charge],[last_update]
from #TEST_INSURANCE_PAYMENTS where IsNull([Provider], '') <> ''
union
select y.id_num, x.[Provider], y.[Location],y.[Total_Charge],y.[last_update]
from #TEST_INSURANCE_PAYMENTS as x
join(
select t1.id_num,max(t2.id_num) as MaxID, t1.[Location],t1.[Total_Charge],t1.[last_update]
from (select * from #TEST_INSURANCE_PAYMENTS where IsNull([Provider], '') = '') as t1
join (select * from #TEST_INSURANCE_PAYMENTS where IsNull([Provider], '') <> '') as t2 on t1.id_num > t2.id_num
group by t1.id_num, t1.[Location],t1.[Total_Charge],t1.[last_update]
)as y on x.id_num = y.MaxID order by id_num;
3条答案
按热度按时间ifsvaxew1#
There are lot of ways of doing this:
Old skool:
This fetches the last non-empty provider from previous rows if current one is empty. Downside is extra join.
New hotness:
I don't know the name of this technique but what it does is creates a single frame of combination of id_num + provider, like: 0000000001Vimalkumar Veerappan. Where provider is empty, we change it to NULL, so value becomes NULL.
By taking
MAX(...) OVER (ORDER BY id_num)
call, we fetch the last value in that frame. Then, by doingSTUFF(...)
we remove the 0000000001 part, and what's left is what we need.This technique enables a single windowed call, which might generate best performance, downside is somewhat obscure code.
i34xakig2#
We won't be able to tell what's going on without a plan (as Dale suggests). In the mean time, maybe this will be a little better:
6bc51xsx3#
For Azure or SQL Server 2022, you can just use
LAG()
withIGNORE NULLS
This avoids JOINS correlated subqueries, etc, etc, and only scans the table once.
fiddle