April 30, 2009 at 6:01 pm
I need help writing a query. I attached an excel file with sample data.
Essentially I have 2 tables.
Table 1 contains the following....
PricingDrugForecastCountryID | PricingDrugID
=====================================
22, 1
23, 1
24, 1
25, 2
26, 2
Table 2 contains the following
RegimenID | PricingDrugForecastCountryID
=====================================
1, 22
1, 23
2, 22
What I need to happen is write an insert query that will insert the PricingDrugForecastCountryID's that are missing for a regimenid where it's apart of that pricing drug.
so the following records would be inserted
RegimenID | PricingDrugForecastCountryID
===================================
1, 24
2, 23
2, 24
No records will be inserted from pricingDrugID=2 because there is no mapping for that in table2 (i.e. there is no regimen mapped to PricingDrugForecastCountryID = 25 or 26.
I managed to do this, but it took me 3 queries.
April 30, 2009 at 8:15 pm
hi,
What are the three queries you wrote? We can probably help optimise them or improve them.
April 30, 2009 at 8:20 pm
patrick ryan (4/30/2009)
I need help writing a query. I attached an excel file with sample data.
Hi Patrick,
It really helps all of the volunteers that help out on this web site if you post your sample code like this:
declare @PricingDrugForecastCountry TABLE (
PricingDrugForecastCountryID int,
PricingDrug int,
PRIMARY KEY CLUSTERED (PricingDrugForecastCountryID))
declare @RegimenMapping TABLE (
RegimenID int,
PricingDrugForecastCountryID int,
PRIMARY KEY CLUSTERED (RegimenID, PricingDrugForecastCountryID))
insert into @PricingDrugForecastCountry
select 22,1 UNION
select 23,1 UNION
select 24,1 UNION
select 25,1 UNION
select 26,2 UNION
select 27,2 UNION
select 28,2
insert into @RegimenMapping
select 99,22 UNION
select 99,23 UNION
select 99,24 UNION
select 100,24 UNION
select 100,25 UNION
select 101,26
In this way, we can just copy and paste your code directly into our query window, and we're ready to help you. It saves us a considerable amount of time, and since we're all volunteers, it gives us more time to help others. If we have to do this, it also usually means that there is more setup code (as in this case) than actual code that shows you how to handle your issue. Additionally, this positively identifies table structures, indexes, etc. You end up getting tested code. For more information on how to do this, see the link in my signature.
Now, try this code to see how it works out for you:
;with CTE AS
( -- get all possible combinations
select distinct r.RegimenID, p.PricingDrugForecastCountryID, p.PricingDrug
from @PricingDrugForecastCountry p
cross join @RegimenMapping r
)
insert into @RegimenMapping
select CTE.RegimenID, CTE.PricingDrugForecastCountryID
from CTE -- get just the valid combinations from all of them
INNER JOIN @RegimenMapping r ON r.RegimenID = CTE.RegimenID
where CTE.PricingDrug IN (select PricingDrug from @PricingDrugForecastCountry
where PricingDrugForecastCountryID = r.PricingDrugForecastCountryID)
EXCEPT -- then exclude the ones already in the file
select r.RegimenID, p.PricingDrugForecastCountryID
from @PricingDrugForecastCountry p
INNER JOIN @RegimenMapping r ON r.PricingDrugForecastCountryID
= p.PricingDrugForecastCountryID
select * from @RegimenMapping order by RegimenID, PricingDrugForecastCountryID
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 3, 2009 at 6:18 pm
WayneS (4/30/2009)
patrick ryan (4/30/2009)
I need help writing a query. I attached an excel file with sample data.Hi Patrick,
It really helps all of the volunteers that help out on this web site if you post your sample code like this:
Patrick, for future posts, take a look at the link in my signature below for some easy ways to post data to get bettere answers quicker.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply