DISTINCT and CASE statements

[bdragoo]

I'm working on a report that needs to return information from clients. If they have a policy #, then it needs to return their demographic information along with that #. If they do not have a policy number, then it needs to simply return their demographic info.

Currently, it "doubles up" on clients with Policy #s, returning two entries: one with a policy # and demographics, one simply with demographics.


here's what I have so far
SELECT DISTINCT
A.COMPANY
A.NOTES
B.PHONE
CASE WHEN C.TYPE = 'T' AND C.PRIMARY <> 'I' THEN (C.POLICY#) ELSE (NULL) END AS [POLICY]

Then I have some Joins, Where Clauses, and some Order by statements.

The problem is this: I want the distinct to return only distinct records, but the Case statement returns a clients results with either the C.Policy# OR as a Null value. Naturally, the DISTINCT clause sees this as two different clients (one with a policy# attached, one with a null value). I'm trying to get it so the clients with the NULL value simply dont show up.

 

[jbenson001]

SELECT DISTINCT
A.COMPANY
A.NOTES
B.PHONE
from <some table>
where where c.type <> 't' AND c.primary <> 'I'

 

[bdragoo]

Well, I need it to bring back information on two types of clients. Those with certain Policy #s, and those without the policy numbers. That WHERE clause will just bring info back on those with, correct?

 

[jbenson001]

No, that is not what the case is for. The Where clause is used to filter what you want, along with Joins etc. Maybe you can elaborate on what it is exaclty you are trying to do, and perhaps provide some sameple data and desired output. Then we can work on a solution.

 

[bdragoo]

Jbenson-
Thanks for helping out. If I'm being cagey about things, it is because this report deals with a HIPAA compliant database, so I thought it best to play it safe.

Here's what I'm trying to do:

Drivers for our company pick up clients. This report gives them their demographic information, as well as Medicaid policy numbers, if the clients have them. Currently, our report returns the demographic information on those clients without Medicaid. For clients WITH medicaid, it returns two results: one detailing their demographic information, and another detailing their demographic information along with their Medicaid policy number.

I think that this:
where where c.type = 't' AND c.primary <> 'I'
T and I would define those clients that need "T"ransport and have "I"ncomplete Medicaid records.

Does this make sense? Is there any way to tie distinct directly to the case statement, rather than the entire report statement?

 

[jbenson001]

No problem. I deal with the same type of information.

So I am still unclear what you want to do.

If the patient does not have Medicaid.. show the policy# and demographic info.

If the patient has Medicaid.. (1)show their demographics(for medicaid) and
(2) show ins policy# and demographics..

Is this correct in what you want to do?

 

[bdragoo]

Jbenson-

Sorry, I'll try to be more clear

If patient does not have Medicaid: Show demographic information. (The Medicaid policy # field will be left blank)

If patient DOES have Medicaid: Show demographics information (the Medicaid policy # field will show their policy #)

Thanks for your patience!

 

[jbenson001]

Ok that is more clear. But why the DISTINCT? Is it possible for the patient to have medicaid and a regular policy, or is it just one type or the other?

 

[bdragoo]

The distinct is there because some patients have multiple policies (in addition to their "T"ransport policies). Distinct is there to make sure that no one gets "doubled up" (remember this is a report concerning transport/picking people up).

The problem I'm running into is that the CASE statement, by its nature, splits a patient into two distinct records (one containing their profile with their Medicaid Policy, the other not containing it)

 

[jbenson001]

The CASE should not be giving you 2 rows. It has to do most likely with your JOIN. Please post all of the SQL code.

 

[bdragoo]

Ask and ye shall....

SELECT DISTINCT
A.COMPANY,
A.WORKNOTE,
B.HPHONE,
CASE WHEN C.COVTYPE = 'T' AND C.IPRIMARY <> 'I' THEN (C.POLICYNO) ELSE (NULL) END AS [POLICY],
C.ACCOUNT,
rtrim(B.PFNAME) + ' ' + rtrim(B.PLNAME) AS NAME,
rtrim(B.ADDRESS) + ', ' + rtrim(B.CITY) + ', ' + rtrim(B.STATE) + ' ' + rtrim(B.ZIP) + ' ' + rtrim(B.PROVINCE) AS ADDRESS,
CASE WHEN A.COMPANY = 'MAIN' THEN '________'END AS [TRIP],
CASE WHEN A.COMPANY = 'MAIN' THEN '______________________________' END AS [SIG]
FROM
CLPATWORK AS A
INNER JOIN CLMASTER AS B
ON A.ACCOUNT = B.ACCOUNT AND
A.COMPANY = B.COMPANY
INNER JOIN CLNAME AS C
ON A.ACCOUNT = C.ACCOUNT AND
A.COMPANY = C.COMPANY
<WHERE>
WHERE
A.LISTTYPE = <VAR>VAN_LIST</VAR>
</WHERE>
ORDER BY
A.WORKNOTE ASC,
NAME ASC

 

[jbenson001]

it seems that possibly one of your joins produces more than one row per criteria.. Also if one column's data is not exactly the same in your distinct, it will spit out all the rows that don't match. I would take the query piece by piece by piece and run it and see if you get the desired output. This way you can find where the code is going wrong or if it is just a logic thing.