Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Unanswered: Query design help needed with null values

    Good day all,

    Thanks for your help in advance! I need some advice on how I might construct a query from the following information. I have table of accounts and names, etc. that looks like this:

    AcctNum.......Name
    234567.......Joe Cool
    567891.......Jane Doe

    I am going to join it to an Insurance table that contains all the insurances and the order of those insurances applicable to the account. The order of the insurance is by number with "1" being primary insurance, "2" secondary, etc. I want to use a left join on the two tables so I get all the records from the account table so that those with no insurance are shown as well. This would look like the following if nothing else was needed:

    AcctNum.....Name......Fin Class......Insurance......InsOrder......Total Charge
    234567.....Joe Cool.....MDCR ..........MDCR.A...........1................1300
    234567.....Joe Cool.....MDCR ..........MDCD.SC.........2................1300
    234567.....Joe Cool.....MDCR ..........BC.SC.............3................1300
    567891.....Jane Doe....MG.CASE......BC.PPO...........1............ ......250
    567891.....Jane Doe....MG.CASE......MCCR.A...........2............ .....250
    345678.....Bill Jones....SP.AP.................................... .............1450

    I would like the query to pull the record set so that one record per account displays as in the following:

    AcctNum....Name.....Fin Class.....PriIns......SecIns.....OtherIns.....Tota lCharge
    234567....Joe Cool.....MDCR.......MDCR.A....MDCD.SC....BC.SC.... .......1300
    567891....Jane Doe....MG.CASE..BC.PPO.....MCCR.A................. ..........250
    345678....Bill Jones ....SP.AP......................................... ................1450

    I understand that in order to get this output I will need to drop the insurance table into the designer 3 times in order to get the insurances in there horizontally instead of vertically... my problem is how to do the criteria so I don't filter out records without insurance based on criteria (ie. Insorder = 1... 2.. etc.) and how to get the null values to show where there are no secondary or other insurances as above. Do I use IIF function somehow in all this? I hope this is clear... and thanks again in advance for your help!

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This looks like a school assignment. We don't do people's homework on this forum.

    Can you show us what you came up so far? Once you show that you are willing to work on it yourself, we'll be happy to help you further.

    I understand that in order to get this output I will need to drop the insurance table into the designer 3 times in order to get the insurances in there horizontally instead of vertically...
    my problem is how to do the criteria so I don't filter out records without insurance based on criteria (ie. Insorder = 1... 2.. etc.) and
    how to get the null values to show where there are no secondary or other insurances as above.
    Yes, you understand correctly.

    First start by creating a LEFT OUTER JOIN statement as you wrote before. Your line of thought is correct. Once that works correct, try to figure out how to add another Insurance table (look how you did it right before). Then figure out how to add the Insurance table a third time (exactly as the previous time).

    Look how a LEFT OUTER JOIN handles missing data. No IIF is needed at all (IIF is an Access thing. In SQL land we use CASE WHEN ... THEN ... ELSE ... END)

    Your line of thoughts is correct. The next step is to write the SQL script.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Oct 2011
    Posts
    2
    Hello Wim and thank you for the compliment (Ha Ha)... no it's not a school assignment I assure you. I'm 56 years old and trying to pull some data according to a 3rd party vendor's request. Here is the SQL for what I have that isn't working right... it obviously doesn't display records with null values where there is no secondary or tertiary insurances and I can see why hence my question about how to get those to show. It also eliminates any records from the account table that have no insurance or are self pay. I need to see those as well. I have never used the case statement before. I'm not a rookie but also not a seasoned professional either. I'm used to working with the GUI (design mode) but can muddle through the SQL. Thanks for helping. Hope I'm helping...

    SELECT TSG_PatientInformation.AccountNumber, TSG_PatientInformation.Name, TSG_PatientInformation.FinancialClass, TSG_BarInsurances.InsuranceBARPAT AS PrimInscode, MisIns.Name AS PrimInsName, TSG_BarInsurances.InsuredPolicyNumber AS PrimInsPolNum, TSG_BarInsurances_1.InsuranceBARPAT AS SecInsCode, MisIns_1.Name AS SecInsname, TSG_BarInsurances_1.InsuredPolicyNumber AS SecInsPolnum, TSG_BarInsurances_2.InsuranceBARPAT AS TertiaryInsCode, MisIns_2.Name AS TertiaryInsName, TSG_BarInsurances_2.InsuredPolicyNumber AS TertiaryInsPolNum, TSG_BarAccountData.TotalCharge
    FROM ((((((TSG_PatientInformation LEFT JOIN TSG_BarAccountData ON TSG_PatientInformation.UrnABS = TSG_BarAccountData.UrnABS) LEFT JOIN TSG_BarInsurances ON TSG_PatientInformation.UrnABS = TSG_BarInsurances.UrnABS) LEFT JOIN TSG_BarInsurances AS TSG_BarInsurances_1 ON TSG_PatientInformation.UrnABS = TSG_BarInsurances_1.UrnABS) LEFT JOIN TSG_BarInsurances AS TSG_BarInsurances_2 ON TSG_PatientInformation.UrnABS = TSG_BarInsurances_2.UrnABS) LEFT JOIN MisIns ON TSG_BarInsurances.InsuranceBARPAT = MisIns.MnemonicMISINSDICT) LEFT JOIN MisIns AS MisIns_1 ON TSG_BarInsurances_1.InsuranceBARPAT = MisIns_1.MnemonicMISINSDICT) LEFT JOIN MisIns AS MisIns_2 ON TSG_BarInsurances_2.InsuranceBARPAT = MisIns_2.MnemonicMISINSDICT
    WHERE (((TSG_BarInsurances.InsuranceOrderBARPAT)="1") AND ((TSG_BarInsurances_1.InsuranceOrderBARPAT)="2") AND ((TSG_BarInsurances_2.InsuranceOrderBARPAT)="3"));

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    In SQL Server, you can use the Row_Number() function in a Common Table Expression to assign an ordinal value to your records partitioned by Acct Num. You can then pivot that data using case statements to show the top three Insurance entries for each account in a single row.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Hello Wim and thank you for the compliment (Ha Ha)... no it's not a school assignment I assure you. I'm 56 years old and
    err, how can I possibly talk me out of this situation. You have a young, dynamic writing style. That must have put me on the wrong foot.

    Those GUIs are great to do simple joins in an incredible fast and correct (no typo's) way. But they tend to be useless for complex queries.

    Try this:
    Code:
    SELECT TSG_PatientInformation.AccountNumber, 
    	TSG_PatientInformation.Name, 
    	TSG_PatientInformation.FinancialClass, 
    	
    	TSG_BarInsurances.InsuranceBARPAT AS PrimInscode, 
    	MisIns.Name AS PrimInsName, 
    	TSG_BarInsurances.InsuredPolicyNumber AS PrimInsPolNum, 
    	
    	TSG_BarInsurances_1.InsuranceBARPAT AS SecInsCode, 
    	MisIns_1.Name AS SecInsname, 
    	TSG_BarInsurances_1.InsuredPolicyNumber AS SecInsPolnum, 
    	
    	TSG_BarInsurances_2.InsuranceBARPAT AS TertiaryInsCode, 
    	MisIns_2.Name AS TertiaryInsName, 
    	TSG_BarInsurances_2.InsuredPolicyNumber AS TertiaryInsPolNum, 
    	
    	TSG_BarAccountData.TotalCharge
    FROM 
    	TSG_PatientInformation 
    	LEFT OUTER JOIN TSG_BarAccountData ON 
    		TSG_PatientInformation.UrnABS = TSG_BarAccountData.UrnABS
    
    	LEFT OUTER JOIN TSG_BarInsurances ON 
    		TSG_PatientInformation.UrnABS = TSG_BarInsurances.UrnABS AND
    		TSG_BarInsurances.InsuranceOrderBARPAT = "1"
    	LEFT OUTER JOIN MisIns ON 
    		TSG_BarInsurances.InsuranceBARPAT = MisIns.MnemonicMISINSDICT
    
    	LEFT OUTER JOIN TSG_BarInsurances AS TSG_BarInsurances_1 ON 
    		TSG_PatientInformation.UrnABS = TSG_BarInsurances_1.UrnABS AND
    		TSG_BarInsurances_1.InsuranceOrderBARPAT = "2"
    	LEFT OUTER JOIN MisIns AS MisIns_1 ON 
    		TSG_BarInsurances_1.InsuranceBARPAT = MisIns_1.MnemonicMISINSDICT
    
    	LEFT OUTER JOIN TSG_BarInsurances AS TSG_BarInsurances_2 ON 
    		TSG_PatientInformation.UrnABS = TSG_BarInsurances_2.UrnABS  AND
    		TSG_BarInsurances_1.InsuranceOrderBARPAT = "3" 
    	LEFT OUTER JOIN MisIns AS MisIns_2 ON 
    		TSG_BarInsurances_2.InsuranceBARPAT = MisIns_2.MnemonicMISINSDICT
    ;
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •