Complex Queries

[dpatfield66]

In Access (and SQL), I was able to create a UNION Query from two similar tables (one involving Triage Admissions and another involving Admit Admissions)

Basically, I'd create the first query (the UNION) and get a total count of Encounters from both tables.

But some of the encounters were identical and only one was needed.
ex: An Admit record was created from a Triage record. I only needed the Admit, not the triage in the count.

Now, in Access or SQL, I could simply save the first UNION query and then create another query that looked for these Triage duplicates and excluded them from the final count (using a basic NOT IN statement for criteria)
-------------------------------------------------------------------
The PROBLEM is...I'm in .NET using these pesky DataSets, and I'm not sure how they work.

I don't want to save a query in SQL and use it. Not as a view, not as a stored procedure, nothing. I have to work in .NET and only .NET.

So, my first union query is easy. It creates an obixAdmit.cAdmitTable (data table in .NET), and that's great.

So how do I create my second query (eliminating the Triage dupes) using the obixAdmit.cAdmitTable as my reference?

 

[cjard]

I dont quite understand what you want to do...

Do you mean fill a datatable with all rows from Admit that are not also in triage? How are the two tables related.

Given that you UNION them, did you not consider it would be wiser to have one table and a flag column to say whether a row is triage, admit or both?

 

[cjard]

(using a basic NOT IN statement

NOT IN sometimes has serious performance implications. A rule of thumb for its use is generally never to use it for lists longer than you are willing to type by hand..

 

[dpatfield66]

I agree with the one table idea, but the company wants two. Due to marketing module reasons, although I still think you could get away with one table even in that scenario.

However, I've learned to let that go. The Admit and Triage tables are very similar. Sometimes we create a new admit or a new triage. This is considered one "encounter". Sometimes we create a triage record "from" an admit or triage. Or we create an admit from an admit. These are all new encounters each.

But sometimes we create an admit from triage in what's called a "same" encounter. In other words, the patient is triaged, and then admitted on the same day. So most of the Triage data flows to the Admit record. It's almost as if they are one and the same. But I can't merge them due to process. You know how that goes, right?

So, when I do a count of ALL encounters, this same encounter triage-admit combo needs to be considered as one encounter. The only way I can do that is to UNION query on both tables to get the full count, and then come back and eliminate the triage records where an admit was created off of the triage record as a same encounter.

So, I have to create one dataset, or datatable that includes all the records, and then create another dataset that excludes the triage same encounter records.

I DID do a CREATE VIEW and then use that as my first query, but I really didn't want to do that. I (and my supervisor) seem to think that you could simply create one query that includes the UNION query as well as the records that need to get excluded (in a WHERE statement). The UNION query has it's OWN separate WHERE statement obviously. And then I need to look at that UNION statement and produce another SELECT query that excludes the same encounter Triage records.

Can it be done in ONE Query? If I have two datatables:

obixAdmit.cAdmitTable
obixTriage.cTriageTable

Couldn't I effectively FILL the cAdmitTable with the UNION query data?
And then FILL the cTriageTable with the same data only excluding the Triage records that are same encounter?

I'm doing that now, but with a VIEW. Because I don't know how to reference the obixAdmit.cAdmitTable in my second query.

I know it sounds complex, but someone out there hopefully knows what I'm talking about here...

 

[cjard]

I know it sounds complex

Oh, it's relatively simple, it's just a bit backwards and is intensive on resources for calculating.

SELECT COUNT(*) FROM(
 
 SELECT primary_key FROM admit
 UNION
 SELECT primary_key FROM triage
 
)

 

[dpatfield66]

I'm not sure I follow your example. Can you elaborate.
I know how to create the UNION query, and then create another query that takes the UNION query and eliminates certain records, but I can't figure out how to do it in one single query.

In other words...a simple UNION query produces the following set.

SELECT triageID as RecordID,'T' as Type,MRNumber,Encounter
FROM OBTriage WHERE RecordComplete = True
UNION select admitID as RecordID, 'A' as Type,MRNumber,Encounter
FROM OBAdmit WHERE RecordComplete = True
----------------------------------------------------------------

Produces:

RecordID Type MRNumber Encounter
111 T 555555 1
230 A 555555 1
098 T 666666 1
099 T 666666 2
800 A 666666 3
-----------------------------------------
Basically, now I want to eliminate Triage Same Encounters
In other words, The triage record with 111 as it's ID.

I would normally take the UNION query (Let's call it qryUnion), and perform another query on it:

SELECT * FROM qryUNION
WHERE qryUnion.MRNumber NOT IN (SELECT MRNumber FROM qryUnion as Tmp GROUP BY MRNumber,Encounter HAVING Count(*)>1 And Encounter = qryUnion.Encounter And qryUnion.Type = 'T'
-------------------------------------------------
Basically, I get the same set as qryUnion MINUS the Triage Record with triageID of 111.

My issue is, how do I combine these 2 queries into one, so I don't have to save the union query as qryUnion?

Something like:

SELECT (something here) FROM (I'm not sure what here)
WHERE blah blah not in blah blah...

Any suggestions.

OR...

I fill a dataset (or datatable?) called obixAdmit.cAdmitTable with the results from the Union query.

I'd like to fill my obixTriage.cTriageTable with the same results minus the Triage same encounters, but I don't know how to reference the data set in my query.

I tried:

SELECT * from obixAdmit.cAdmitTable, but that didn't work.

 

[cjard]

I agree with the one table idea, but the company wants two. Due to marketing module reasons, although I still think you could get away with one table even in that scenario.

I'd have done both our needs:
Create a single table
Create 2 views, one that SELECT triage_fields FROM table WHERE triaged = True, one that SELECT admit_fields FROM table WHERE admit = true

Then we can leave them to think 2 tables exist, and do real work on just one table.. Those views should be updatable too


Anyways, I'm still not seeing what is the difficult problem here:

SELECT COUNT(*) FROM (
  SELECT MRNumber, encounterID FROM admit
  UNION
  SELECT MRNumber, encounterID FROM triage
)

UNION automatically removes duplicates in oracle.. UNION ALL keeps any dupes.
if for some reason you think your RDBMS works differnetly, do:

SELECT COUNT(DISTINCT MRNumber, encounterID) FROM (
  SELECT MRNumber, encounterID FROM admit
  UNION
  SELECT MRNumber, encounterID FROM triage
)

 

[dpatfield66]

Difficult Problem...

Thanks for the code advice, I'll check it out.
As for single tables and alternatives, I have to go by what the boss wants, but thanks for the ideas in that area, no problem.

As for not seeing what the difficult problem is, sometimes as programmers we're so versed in what we do that we tend to forget that the answer, that we see so clearly, is not always right in front of another programmers face.

Although, you're right, this is probably a very simple solution that I overlooked, and I've been programming for 10 years, however not formally. All my programming has been trial and error and pieces parts here and there. There are still pieces of code out there that I've never even heard of.

But thanks for the solution, I'll check it out...

 

[cjard]

Do make a strong case to your boss, that this is a very bad way of doing things.. By having separate tables for essentially 1:1 related infotrmation, you are creating a developmental problem because effectively every time you want to do a count like this then the database has to create a temporary table then count it, and it represents a major break to the way an RDBMS likes to work.. i.e. it's fitting bicycle tyres to a ferrari

It cannot easily use indexes or other performance enhancements..


As a developer who knows the technical stuff, I routinely override my boss, because he simply doesnt know how things work. If you can realise the same situation, it will allow you to create far better apps (I occasonally refuse to develop applications that I know will perform poorly due to a bad design decision, simply for the fact that it reflects badly on me -> It looks like I write slow code )

 

[dpatfield66]

Select Count,etc...

I tried the following code:

SELECT COUNT(DISTINCT MRNumber, PregnancyID,Encounter) FROM
(SELECT MRNumber,PregnancyID,Encounter,'T' AS Type FROM OBTriage
Where RecordComplete = 1
UNION SELECT MRNumber, PregnancyID,Encounter,'A' AS Type FROM OBAdmit
Where RecordComplete = 1)
--------------------------------------------------
And I got the following error message:

Msg 102, Level 15,State 1,Line 2
Incorrect syntax near ','.

I even removed the Where statements and 'A' AS Type/'T' AS Type, but got the same error:

SELECT COUNT(DISTINCT MRNumber,PregnancyID,Encounter) FROM
(SELECT MRNumber,PregnancyID,Encounter FROM OBTriage
UNION SELECT MRNumber,PregnancyID,Encounter FROM OBAdmit)

Msg 102, Level 15,State 1,Line 2
Incorrect syntax near ','.

Any thoughts?

If I remove the SELECT COUNT(...ETC)

And just use the UNION Query, it works (With, of course, the exception of still having the dupe Triage record I need to remove.)

 

[dpatfield66]

In my previous email and code example, it looks like SELECTCOUNT is one word, but I copied it wrong. It's actually two words in the code.

SELECT COUNT

Just an FYI

 

[dpatfield66]

I got this to work by setting an alias:

SELECT DISTINCT MRNumber,PregnancyID,Encounter FROM (SELECT 'T' AS Type,MRNumber,PregnancyID,Encounter FROM OBTriage UNION SELECT 'A' AS Type,MRNumber,PregnancyID,Encounter FROM OBAdmit) as uquery
ORDER BY MRNUMBER,PREGNANCYID,ENCOUNTER

If I remove <as uquery> I get the error again.

The only problem is...this is good for getting counts, but I need to display more than just the distinct information.

As soon as I add anything else in the first statement, It gives me ALL records again.

Example:


SELECT DISTINCT Type,MRNumber,PregnancyID,Encounter FROM (SELECT 'T' AS Type,MRNumber,PregnancyID,Encounter FROM OBTriage UNION SELECT 'A' AS Type,MRNumber,PregnancyID,Encounter FROM OBAdmit) as uquery ORDER BY MRNUMBER,PREGNANCYID,ENCOUNTER

Now, instead of 8 records, I get 9. Obviously because one type says T and the other says A, so it's not a dupe with that consideration. Or even if I added other fields from both tables that weren't duped (ex: BirthDate).

So, how do I include these fields?

 

[cjard]

Different approach required here.

Tell me the schema of OBAdmit
Tell me the schema of OBTriage
Tell me the schema of the results you want
Tell me the row data of the results that youre expecting

 

[dpatfield66]

The Schema of the Admit and Triage tables are not exactly alike, so and asterick would not work.

But there are many similar fields in both tables.
To simplify I'll say this:

Both tables have MRNumber, PregnancyID, Encounter, RecordComplete,AdmitDate, and let's say Field1 and Field2 (to represent all the other similar fields I'll want in the final result).

I want to union query these 2 tables where Admit Date between Jan 01 2007 and Jan 31 2007 and RecordComplete = 1.

Once I get this result, I want to eliminate all the Triage records that have a cooresponding Admit Record with the same MRNumber, PregnancyID, and Encounter.

ex:

Admit Record: MRNumber = 1, PregnancyID = 4, Encounter = 7
Triage Record: MRNumber = 1, PregnancyID = 4, Encounter = 7

Both records had an AdmitDate in the daterange specified, and the records were both complete.

But I don't want the Triage record in this case, because it's a dupe of the admit record.

So, my final results will be a table with these fields:

MRNumber,PregnancyID, Encounter, AdmitDate,Field1,Field2.

 

[cjard]

I dont think we should be unioning at all given that you want data.

Try:

SELECT
  MRNumber,
  PregnancyID,
  Encounter,
  a.Field1,
  t.Field1,
  a.Field2,
  t.Field2
FROM
  OBAdmit a
  FULL OUTER JOIN
  OBTriage t
  USING
    (MRNumber, PregnancyID, Encounter)
WHERE
  (a.admitDate >= #01-Jan-2007# AND a.admitDate < #01-Feb-2007# AND
  t.admitDate >= #01-Jan-2007# AND t.admitDate < #01-Feb-2007#) OR
  (a.admitDate >= #01-Jan-2007# AND a.admitDate < #01-Feb-2007# AND
  t.admitDate IS NULL) OR
  (t.admitDate >= #01-Jan-2007# AND t.admitDate < #01-Feb-2007# AND
  a.admiteDate IS NULL)

note that you havent said whether field1 and 2 are the same in both tables
also note that i have modified your date ranges to guarantee all of january. previously your query would have dropped 31 jan 2007 results if the time part of the date was anything after midnight (even one millisecond)
also note that SQL Server might not support the USING shortcut for columns named the same. You might have to use the normal ON a.MR... = t.MR...