asked on

SQL SELECT statement logic when item number appears twice

Hi all.

I have a select statement (see below) that shows accountnumber, itemnumber, contracttype, pricing (I have attached a sample of the data).

SELECT DISTINCT 
                     ContractAssignmentDetail.accountnumber,  [Contract Pricing].dbo.tblAll_ContractPricing.ItemNumber,[Contract Pricing].dbo.[tblPM_Contracts-All Contracts].contracttype, 
                      [Contract Pricing].dbo.tblContractPricing.pricing
FROM         ContractAssignmentHeader INNER JOIN
                      ContractAssignmentDetail ON ContractAssignmentHeader.CANumber = ContractAssignmentDetail.CANumber INNER JOIN
                      [Contract Pricing].dbo.tblContractPricing INNER JOIN
                      [Contract Pricing].dbo.tblAll_ContractPricing ON 
                      [Contract Pricing].dbo.tblContractPricing.ContractID = [Contract Pricing].dbo.tblAll_ContractPricing.All_ContractPricingID INNER JOIN
                      [Contract Pricing].dbo.[tblPM_Contracts-All Contracts] ON 
                      [Contract Pricing].dbo.tblContractPricing.PMContractID = [Contract Pricing].dbo.[tblPM_Contracts-All Contracts].PM_Contract_ID AND 
                      [Contract Pricing].dbo.tblAll_ContractPricing.GPO_Contract_ID = [Contract Pricing].dbo.[tblPM_Contracts-All Contracts].[GPO Contract ID] ON 
                      ContractAssignmentHeader.PMContractID = [Contract Pricing].dbo.[tblPM_Contracts-All Contracts].PM_Contract_ID
WHERE     (ContractAssignmentHeader.Status = N'Active') AND (ContractAssignmentDetail.accountnumber = N'5-00111') AND (ContractAssignmentDetail.Status = N'Active')
ORDER BY [Contract Pricing].dbo.tblAll_ContractPricing.ItemNumber

Open in new window

There are times when an account is assigned to 2 contracts, there are two types of contracts: Basic and Independent. If an itemnumber appears on both contracts then the "Independent" contract type overrides/takes precedence over the Basic contract type and therefore its pricing.

So using the sample data I have attached the SELECT statement should show the following results:

accountnumber--itemnumber--contracttype--pricing
5-00111--1000033--Basic--19.345
5-00111--1148473--Basic--7.00625
5-00111--2549637--Independent--0.9345
5-00111--3564452--Basic--2.455
5-00111--3579426--Independent--0.7014
5-00111--3584414--Basic--13.683

Item 2549637 appeared on both Basic and Independent contracts but the Independent contract trumps the Basic contract, the same for item 3579426. So, if an item appears only once then no logic required, but if the item appears more than once then the instance where the contract is Independent is the one that is showed in the results.

Any help is appreciated. Thank you in advance!
SampleData.xlsx

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

tip:

for simple amounts of sample data. a text table is easier to use then having to use an Excel attachment. I suggest using this URL https://ozh.github.io/ascii-tables/ where you can just paste your sample in the upper edit area and it can then produce many styles of text tables, such as this default style:

+---------------+------------+--------------+---------+
| accountnumber | itemnumber | contracttype | pricing |
+---------------+------------+--------------+---------+
| 5-00111       |    1000033 | Basic        |  19.345 |
| 5-00111       |    1148473 | Basic        | 7.00625 |
| 5-00111       |    2549637 | Independent  |  0.9345 |
| 5-00111       |    2549637 | Basic        |  1.2507 |
| 5-00111       |    3564452 | Basic        |   2.455 |
| 5-00111       |    3579426 | Independent  |  0.7014 |
| 5-00111       |    3579426 | Basic        |  0.7579 |
| 5-00111       |    3584414 | Basic        |  13.683 |
+---------------+------------+--------------+---------+

Open in new window

Advantages:
You don't have to create and save an Excel file
You avoid the upload step (or forget to upload, which happens)
It is easier to read the whole question with data visible
Also easier for users of phones and tablets this way
For answering we avoid a truckload of download files
Some experts cannot (or will not) download for security reasons

printmedia

ASKER

Thank you PortletPaul!

Here's my final code as you suggested and it works wonderfully!

printmedia

ASKER

SELECT
   d.accountnumber, d.PMContractID, d.contracttype, 
                      d.ItemNumber, d.pricing, d.rn
FROM (
SELECT DISTINCT 
                      ContractAssignmentDetail.accountnumber,ContractAssignmentHeader.PMContractID,  [Contract Pricing].dbo.[tblPM_Contracts-All Contracts].contracttype, 
                      [Contract Pricing].dbo.tblAll_ContractPricing.ItemNumber, [Contract Pricing].dbo.tblContractPricing.pricing , ROW_NUMBER() OVER (PARTITION BY  [Contract Pricing].dbo.tblAll_ContractPricing.ItemNumber
                           ORDER BY CASE WHEN [Contract Pricing].dbo.[tblPM_Contracts-All Contracts].contracttype = 'Independent' then 1 else 2 end) AS rn
FROM         ContractAssignmentHeader INNER JOIN
                      ContractAssignmentDetail ON ContractAssignmentHeader.CANumber = ContractAssignmentDetail.CANumber INNER JOIN
                      [Contract Pricing].dbo.tblContractPricing INNER JOIN
                      [Contract Pricing].dbo.tblAll_ContractPricing ON 
                      [Contract Pricing].dbo.tblContractPricing.ContractID = [Contract Pricing].dbo.tblAll_ContractPricing.All_ContractPricingID INNER JOIN
                      [Contract Pricing].dbo.[tblPM_Contracts-All Contracts] ON 
                      [Contract Pricing].dbo.tblContractPricing.PMContractID = [Contract Pricing].dbo.[tblPM_Contracts-All Contracts].PM_Contract_ID AND 
                      [Contract Pricing].dbo.tblAll_ContractPricing.GPO_Contract_ID = [Contract Pricing].dbo.[tblPM_Contracts-All Contracts].[GPO Contract ID] ON 
                      ContractAssignmentHeader.PMContractID = [Contract Pricing].dbo.[tblPM_Contracts-All Contracts].PM_Contract_ID
WHERE     (ContractAssignmentHeader.Status = N'Active') AND (ContractAssignmentDetail.accountnumber = N'3-18371') AND (ContractAssignmentDetail.Status = N'Active')

) d
where rn =1
ORDER BY d.ItemNumber

Open in new window

PortletPaul

Thanks.

Please note you do not need to use "select distinct" as there can only be a single row where rn=1 for each "partition".

Cheers.