We have a table where we keep rows with vanilla settings for products, and customized rows per product per customer as they see fit to override. When a customer does not override, the vanilla rows with vanilla settings are selected but when customized rows exists for products and customers, the customized rows must be returned and the vanilla rows thrown out.
I have the sample code and my solution using ROW_NUMBER() to guide the row selection. This ROW_NUMBER seems to make our query run longer so I am asking if anyone see better options to achieve the same results.
Thank you!
IF OBJECT_ID('tempdb..#TItems') IS NOT NULL DROP TABLE #TItems IF OBJECT_ID('tempdb..#TAttrib') IS NOT NULL DROP TABLE #TAttrib CREATE TABLE #TItems (ItemID int, Name varchar(50)) CREATE TABLE #TAttrib (AttribID int, ClinicID int , ItemID int, AutoReorder bit, isFavorite bit, IsControlled bit ) Insert into #TItems (ItemId, Name) Select 1 as ItemID, 'Item1' as Name UNION Select 2 as ItemID, 'Item2' as Name UNION Select 3 as ItemID, 'Item3' as Name ; INSERT INTO #TAttrib (AttribID , ClinicID, ItemID, AutoReorder , isFavorite , IsControlled ) SELECT 10, NULL, 1,1,1,1 UNION SELECT 20, NULL, 2,1,1,1 UNION SELECT 30, NULL, 3,1,1,1 UNION SELECT 40, 200, 1, 0,0,1 UNION SELECT 50, 500, 2,1,0,0 UNION SELECT 60, 500, 3,1,1,0 ; -- Clinics 100, 300, 400 do not have Attrib. They will use the vanilla attrib rows for items 1-3. -- Clinic 200 has customized item 1, clinic 500 has customized item 3. -- expected Attrib result set for clinic 100: AttribIDs 10, 20, 30 -- expected Attrib result set for clinic 200: AttribIDs 40, 20, 30 -- expected Attrib result set for clinic 500: AttribIDs 10, 50, 60 Declare @ClinicID int = 500 SELECT * FROM ( SELECT AttribID, ClinicID , ItemID, ROW_NUMBER() OVER ( PARTITION BY ItemID ORDER BY ClinicID DESC ) RN FROM #TAttrib where ClinicID is NULL or ClinicID = @ClinicID ) RS WHERE RN = 1
Advertisement
Answer
Try using a correlated subquery and union all
:
select a.* from #TAttrib a where a.ClinicId = (select max(a2.ClinicId) from #TAttrib a2 where a2.ItemID = a.ItemId ) union all select a.* from #TAttrib a where not exists (select 1 from #TAttrib a2 where a2.ItemID = a.ItemId and a2.ClinicId is not null );
The first gets the clinicid
when there is a non-NULL
value. The second gets the rest.
Now, very importantly, you want an index on #TAttrib(ItemID, ClinicID)
on the able for these queries:
create index idx_tattrib_itemid_clinicid on #TAttrib(ItemID, ClinicID);
This would also benefit your query.