Finding Missing Items

Question

Finding Missing Items

Vicar

SSC Journeyman

Points: 79
More actions
March 4, 2010 at 11:24 am

#217480

I have a table With Clients and another table with Client Charges. Each client is supposed to have one entry in the Charges table for each of 3 different charge categories. I need to find which clients have less than 10 charges and which are the missing charges.
In the Clients table:
Client ID =1, Client Name = Joe, Total Charges $100
In the Charges Table
Client ID Charge TypeChargeAmount
1Fertilizer $40
2Equipment$60
In the example above Jo is missing the charge for Herbizide, the third of the required charge entries. Alhough the sum of his individual charges adds up to the total charges in the Clients table, another record needs to exist in the Charges table with an amount of $0.00. Please do not ask why it was done this way!!!
Another table exists with a list of the valid charge categories. I am trying to figure out a way to write a statement that returns a list of the clients that are missing at least one charge and the charge category missing.
Everything I have tried leads to a point where there is no relationship between the list of patients and the list of valid charges…

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

if you can't infer the missing item due to the sum(0 of the dollar amounts not matching, is there some other logic to help infer something is missing?

for example, if you buy fertilizer, the herbicide is always included?

You'll have to show us the logic so we can help you define it, i think.

can you show the CREATE TABLe statmenets for the master/detail?

Vicar (3/4/2010)
I have a table With Clients and another table with Client Charges. Each client is supposed to have one entry in the Charges table for each of 3 different charge categories. I need to find which clients have less than 10 charges and which are the missing charges.
In the Clients table:
Client ID =1, Client Name = Joe, Total Charges $100
In the Charges Table
Client ID Charge TypeChargeAmount
1Fertilizer $40
2Equipment$60
In the example above Jo is missing the charge for Herbizide, the third of the required charge entries. Alhough the sum of his individual charges adds up to the total charges in the Clients table, another record needs to exist in the Charges table with an amount of $0.00. Please do not ask why it was done this way!!!
Another table exists with a list of the valid charge categories. I am trying to figure out a way to write a statement that returns a list of the clients that are missing at least one charge and the charge category missing.
Everything I have tried leads to a point where there is no relationship between the list of patients and the list of valid charges…

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dugi SSCoach Points: 17998 More actions · Answer 2

To have the correct results that you are looking for, post your table structure with sample data and the resultset that you want to achieve ...hope that we will help you in the best way that we can and with many possible alternatives!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Vicar SSC Journeyman Points: 79 More actions · Answer 3

This is an old system and I have no control over the existing tables, I cannot ask for changes to the procedures that import that data. What we are trying to do is find if any client is missing an entry for a charge and which is the missing entry. I simplified the description by saying there only 3 charge types when in fact there are many more. There are no rules as to if one is entrered the other also needs to be entered. The rule is ALL charges must be entered even if the amount is zero ($0).

There are 3 tables (simplified example):

Clients: ID, Name, TotalCharges

Charges: ClientID, ChargeTypeID and ChargeAmount

ChargeTypes: ChargeTypeID, Description

Required results:

ClientID, Client Name, Description of missing charge type

From my original example, the expected result should be:

1, Joe, Herbizide

Vicar SSC Journeyman Points: 79 More actions · Answer 4

Solution found!!!

SELECT C.clientid, T.charge_type

FROM Clients AS C

CROSS JOIN ChargeCategories AS T

EXCEPT

SELECT clientid, charge_type

FROM Charges;