Loop required? 2

[LT2]

Hello, Using SQL Svr 2005.

I've got results from a basic query that if certain criteria are met, I'd like another query to run to give me specific results and I'm unsure of the proper syntax to get what I want. Can you help me?

Basic query:

Select c.client, p.name, a.admitdate, d.dischdate
From tClient c
inner join tAdmit on c.clientnum = a.clientnum
left join tDischarge d on a.admitnum = d.dischnum
inner join tProvider p on c.providernum = p.providernum
Where p.Test = 0
Order by c.client, a.admitdate

Results: client admit activity in sequential date order, used to manually determine if there is an overlapping admit.

Goal: Automate above manual portion specific to a particular Provider and dischdate, grouped by client.

IF a client had any admits with Provider B where the dischdate > 5/31/2008 in the basic query AND had any overlap where admitdate is less than the dischdate it follows regardless of Provider, display the client's sequential admit activity.

Select c.client, p.name, a.admitdate, d.dischdate
From tClient c
inner join tAdmit on c.clientnum = a.clientnum
left join tDischarge d on a.admitnum = d.dischnum
inner join tProvider p on c.providernum = p.providernum
Where p.Test = 0
Group by c.client, p.name, a.admitdate, d.dischdate
Order by c.client, a.admitdate

So, only clients with any overlapping admits that had any activity with Provider B and were discharged 6/1/2008 or after would display.

LT

 

[LT2]

ESquared,

An aside: why do you have separate admit and discharge tables? Every admit must eventually be paired with a discharge. The two tables properly belong together. Are they very narrow?
[/quote ESquared]

The admit and discharge tables are separate and by design. This is what I have to work with. I do not know what you mean by 'narrow'.

The original query was slightly modified for forum posting due to vendor contract compliance.

And I was going to help you, but the joins you've provided are flawed or you're not telling us enough, because assuming there is only one row in the tClient table per client, then you can only have one providernum linked to a client at a time, and thus you can't know who the provider was for any past visits if it was different than the current value.

Do you see?

CODE
tClient c
inner join tProvider p on c.providernum = p.providernum
This makes no reference to discharge dates or visits, thus you'll always have only one provider per client, thus your query can never return something like:

client provider admitdate dischdate
123 ABC 4/1/2008 6/1/2008
123 B 5/31/2008 8/31/2008
[/quote ESquared]

The query noted below does in fact render a client's sequential admission activity beyond a certain point in time (based on discharge date) so manually I can see overlapping admits between providers.

Please note, the client is matched to an admit by a client number which is unique to the client and those admits are matched to discharges by an an admit number and the provider is tied to the client by a provider number.

select c.clientid, p.providername, a.admitdate, d.dischdate
from tclient c
	inner join tadmit a on c.clientnum = a.clientnum
	left join tdischarge d on a.admitnum = d.admitnum
	inner join tprovider p on c.provnum = p.provnum
where d.dischdate > '5/31/2008' 
	and p.testagency = 0 --Eliminates Test Agency Data
group by c.clientid, p.providername, a.admitdate, d.dischdate
order by 1, 3

I believe I neglected to include the group by clause in the original post of the mocked query but all the rest is the same concept. I am not withholding information and the joins do work for our application. Does this make more sense?

[tt]
clientid provname admitdate dischdate
1 PK1 12/2/2002 7/9/2008
1 MH 7/8/2008 10/27/2008
2 PK1 6/1/2005 7/10/2008
2 MH 7/8/2008 10/27/2008
3 PK1 10/9/2002 7/2/2008
3 N 7/3/2008 7/12/2008
4 PN1 4/18/2008 5/29/2008
4 PN2 6/14/2008 11/17/2008
5 H1 7/4/2008 7/7/2008
5 H1 7/7/2008 7/21/2008
6 PK1 1/1/2000 7/9/2008
6 MH 7/8/2008 10/27/2008
[/tt]


The goal is to automate the manual portion of determining the overlapping admits across thousands of rows and then to further define a client's admit activity where a certain provider was involved.

End Result:
[tt]
clientid provname admitdate dischdate
1 PK1 12/2/2002 7/9/2008
1 MH 7/8/2008 10/27/2008
2 PK1 6/1/2005 7/10/2008
2 MH 7/8/2008 10/27/2008
6 PK1 1/1/2000 7/9/2008
6 MH 7/8/2008 10/27/2008
[/tt]

Looking at the first set of results you will notice clientid 3 is not in the end result because the discharge from PK1 is less than the following admit date of provider N. Clientid 4 having overlapping admits didn't have activity with Provider like 'PK%' therefore not in the end result. Clientid 5 didn't meet either criteria, therefore not in the end result.

While the goal is to determine overlapping admits with Provider like 'PK%', I will be using the overlapping portion across all Providers for another application.

I believe George was creating a table @Temp to have the discharges matched to the admits in one view so he could further filter against. Please correct me if I am wrong.

LT

 

[ESquared]

What you're telling me only makes sense if clientid is nonunique in the tClient table, but clientnum is unique. Is that true?

 

[LT2]

ESquared,

tClient clientID is unique. Clients belong to Providers, hence the clientnum. One client can belong to many Providers but each will have their own clientnum, different than a clientID. Think of clientID like a SSN. Providers basically own the admits and discharges, this is why clients are joined by ProvNum.

Does this make sense?

LT

 

[LT2]

ESquared and George,

Please don't drop the ball on this one. I really do need your help to finish my report. I am grateful for all the advice you provide.

Sincerely,
LT

 

[ESquared]

So I'm asking you to explicitly explain how a set of admit and discharge dates can be correlated to a particular provider? Unless there is some unshown relation between tprovider and tadmit or between clientnum and provnum, the table design I see doesn't make sense.

You said providers own the admits and discharges, but you're not joining to tadmit on a value found in the provider table, you're joining on a value found in the client table.

clientID can't be unique in the tclient table based on the information you've provided.

If you want more help, you need to provide some sample data. It doesn't have to be a lot. But it needs to show at least 2 providers for 1 client with at least 2 admits and discharges.

 

[LT2]

Hello ESquared,

Sincerely, listed below are the tables I'm using:
[ol]AgencyClient
[li]AgClientNum[/li]
[li]AgNum[/li]
[li]SSN[/li]
[li]ClientID[/li]
[li]RecDate[/li]
[li]RecUser[/li]
[li]RecAg[/li]
[li]RecActive[/li]
[/ol]
[ol]Agency
[li]AgNum[/li]
[li]RecDate[/li][li]RecUser[/li]
[li]RecActive[/li]
[li]Provider[/li]
[li]Test[/li]
[/ol]
[ol]Admission
[li]AdmitNum[/li]
[li]RecDate[/li]
[li]RecUser[/li]
[li]RecAg[/li]
[li]AgClientNum[/li]
[li]AdmitDate[/li]
[/ol]
[ol]Discharge
[li]DischNum[/li]
[li]RecDate[/li]
[li]RecUser[/li]
[li]RecAg[/li]
[li]AdmitNum[/li]
[li]AgClientNum[/li]
[li]DischDate[/li]
[/ol]

Below is the query I've used:

select a.clientid, ag.provider, adm.admitdate, dis.dischdate
from agencyclient a
	inner join agency ag on a.agnum = ag.agnum
	inner join admission adm on a.agencyclientnum = adm.agencyclientnum
	left join discharge dis on adm.admitnum = dis.admitnum
where dis.dischdate > '4/30/2008'  --'xx/xx/xxxx'
	and ag.test = 0 --Eliminates Test Agency Data
group by a.clientid, ag.provider, adm.admitdate, dis.dischdate
order by 1, 3

Listed below are my results:
[tt]
Client Agency Admit Disch
1 CSSMON 12/17/2007 6/30/2008
2 TGA 10/15/2008 11/24/2008
3 HCAN 11/14/2008 12/17/2008
4 TGA 1/9/2008 8/14/2008
5 RFC 3/14/2008 5/29/2008
6 CCSLP 3/17/2008 5/15/2008
7 RDC 10/30/2008 12/30/2008
8 PNLPLY 9/5/2007 8/7/2008
9 HCAN 5/28/2008 5/31/2008
10 SAEVAN 6/12/2008 8/5/2008
10 BFHP 8/14/2008 10/15/2008
10 BFHP 10/16/2008 1/13/2009
11 HCAN 8/30/2008 9/2/2008
11 HCAN 9/2/2008 9/5/2008
[/tt]

I've not withheld any information, only changed table and field names and dummied up the results so no actual data is published. Does this suffice?

LT

 

[ESquared]

The reason we've been having trouble helping you is that you provided incorrect information. Your original query joined from the Client table. Now you're using a join table between Client and Agency, which you're calling AgencyClient. (Though perhaps it would be better called Visit, eh?)

ClientID is NOT unique in the AgencyClient table. Is it.

I just spent well over an hour solving this for you (when it should have taken about 10 minutes), because instead of giving me sample data and perhaps even DDL script to run, you made me go and cobble those together, myself, from your final output. So please forgive this tongue-lashing, but I'm a little irritated with the way you've been putting the burden for help on others instead of making it easy to help you. I'm not sure if I expect gratitude from you for continuing to work on this even after my repeated explanations that something was wrong didn't get through, and after a final direct request for sample data went ignored (you provided example output, not sample source data). It probably would have been better for me to ignore you and chalk you up in the "Hopeless/Doesn't follow instructions well" category. So call me a glutton for punishment.

Here is code that does exactly what you asked for. If you want to understand why I used EXISTS in a couple of places instead of INNER JOIN, go read up on semi-joins and anti-semi joins, and consider that EXISTS:
• Stops when it finds one row that matches instead of returning every row
• Won't multiply rows out when multiple rows are present (the main query isn't joined so it just sits the way it is)
• Can achieve more efficient execution plans since none of the columns in the EXISTS clause will be in the final query.

DROP TABLE Discharge
DROP TABLE Admission
DROP TABLE AgencyClient
DROP TABLE Agency
DROP VIEW ManualList
DROP VIEW Visit

GO
CREATE TABLE Agency (
   AgNum int identity(101,1) primary key clustered,
   RecDate datetime,
   RecUser varchar(50),
   RecActive varchar(50),
   Provider varchar(50),
   Test bit default (0)
)

CREATE TABLE AgencyClient (
   AgClientNum int identity(201,1) primary key clustered,
   AgNum int not null foreign key references agency(agnum),
   SSN varchar(50),
   ClientID int,
   RecDate datetime,
   RecUser varchar(50),
   RecAg varchar(50),
   RecActive varchar(50)
)

CREATE TABLE Admission (
   AdmitNum int identity(301,1) primary key clustered,
   RecDate datetime,
   RecUser varchar(50),
   RecAg varchar(50),
   AgClientNum int not null foreign key references agencyclient(agclientnum),
   AdmitDate datetime,
)

CREATE TABLE Discharge (
   DischNum int identity(401,1) primary key clustered,
   RecDate datetime,
   RecUser varchar(50),
   RecAg varchar(50),
   AdmitNum int not null foreign key references admission (admitnum),
   AgClientNum int not null, -- to have this column in this table violates normalization rules
   DischDate datetime
)
GO
INSERT Agency (Provider) SELECT 'CSSMON' UNION ALL SELECT 'TGA' UNION ALL SELECT 'HCAN' UNION ALL SELECT 'RFC' UNION ALL SELECT 'CCSLP' UNION ALL SELECT 'RDC' UNION ALL
SELECT 'PNLPLY' UNION ALL SELECT 'SAEVAN' UNION ALL SELECT 'BFHP'

INSERT AgencyClient (agnum, clientid)
SELECT ag.agnum, x.clientid
FROM
   Agency ag
   inner join (
      SELECT 'CSSMON', 1
      UNION ALL SELECT 'TGA', 2
      UNION ALL SELECT 'HCAN', 3
      UNION ALL SELECT 'TGA', 4
      UNION ALL SELECT 'RFC', 5
      UNION ALL SELECT 'CCSLP', 6
      UNION ALL SELECT 'RDC', 7
      UNION ALL SELECT 'PNLPLY', 8
      UNION ALL SELECT 'HCAN', 9
      UNION ALL SELECT 'SAEVAN', 10
      UNION ALL SELECT 'BFHP', 10
      UNION ALL SELECT 'HCAN', 11
   ) X (Provider, ClientID) ON ag.provider = x.provider

INSERT Admission (AgClientNum, AdmitDate)
SELECT
   a.agclientnum, x.admitdate
FROM
   Agency ag
   inner join AgencyClient a on ag.agnum = a.agnum
   inner join (
      SELECT 'CSSMON', 1, '20071217', '20080630'
      UNION ALL SELECT 'TGA', 2, '20081015', '20081124'
      UNION ALL SELECT 'HCAN', 3, '20081114', '20081217'
      UNION ALL SELECT 'TGA', 4, '20080109', '20080814'
      UNION ALL SELECT 'RFC', 5, '20080314', '20080529'
      UNION ALL SELECT 'CCSLP', 6, '20080317', '20080515'
      UNION ALL SELECT 'RDC', 7, '20081030', '20081230'
      UNION ALL SELECT 'PNLPLY', 8, '20070905', '20080807'
      UNION ALL SELECT 'HCAN', 9, '20080528', '20080531'
      UNION ALL SELECT 'SAEVAN', 10, '20080612', '20080805'
      UNION ALL SELECT 'BFHP', 10, '20080814', '20081015'
      UNION ALL SELECT 'BFHP', 10, '20081016', '20090113'
      UNION ALL SELECT 'HCAN', 11, '20080830', '20080902'
      UNION ALL SELECT 'HCAN', 11, '20080902', '20080905'
   ) X (Provider, ClientID, admitdate, dischdate) ON ag.provider = x.provider and a.clientid = x.clientid

INSERT Discharge (AgClientNum, admitnum, DischDate)
SELECT DISTINCT
   a.agclientnum, adm.admitnum, x.dischDate
FROM
   Agency ag
   inner join AgencyClient a on ag.agnum = a.agnum
   inner join (
      SELECT 'CSSMON', 1, '20071217', '20080630'
      UNION ALL SELECT 'TGA', 2, '20081015', '20081124'
      UNION ALL SELECT 'HCAN', 3, '20081114', '20081217'
      UNION ALL SELECT 'TGA', 4, '20080109', '20080814'
      UNION ALL SELECT 'RFC', 5, '20080314', '20080529'
      UNION ALL SELECT 'CCSLP', 6, '20080317', '20080515'
      UNION ALL SELECT 'RDC', 7, '20081030', '20081230'
      UNION ALL SELECT 'PNLPLY', 8, '20070905', '20080807'
      UNION ALL SELECT 'HCAN', 9, '20080528', '20080531'
      UNION ALL SELECT 'SAEVAN', 10, '20080612', '20080805'
      UNION ALL SELECT 'BFHP', 10, '20080814', '20081015'
      UNION ALL SELECT 'BFHP', 10, '20081016', '20090113'
      UNION ALL SELECT 'HCAN', 11, '20080830', '20080902'
      UNION ALL SELECT 'HCAN', 11, '20080902', '20080905'
   ) X (Provider, ClientID, admitdate, dischdate) ON ag.provider = x.provider and a.clientid = x.clientid
   inner join admission adm on a.agclientnum = adm.agclientnum and x.admitdate = adm.admitdate
GO
create view ManualList
as
select a.clientid, ag.provider, adm.admitdate, dis.dischdate
from agencyclient a
   inner join agency ag on a.agnum = ag.agnum
   inner join admission adm on a.agclientnum = adm.agclientnum
   left join discharge dis on adm.admitnum = dis.admitnum
where
   dis.dischdate > '4/30/2008'  --'xx/xx/xxxx'
   and ag.test = 0 --Eliminates Test Agency Data
group by a.clientid, ag.provider, adm.admitdate, dis.dischdate
GO
create view Visit
as
select
   a.*,
   adm.admitnum,
   adm.admitdate,
   dis.dischnum,
   dis.dischdate
from
   agencyclient a
   inner join admission adm on a.agclientnum = adm.agclientnum
   left join discharge dis on adm.admitnum = dis.admitnum
go
select m.*
from
   manuallist m
where exists (
   select *
   from
      visit v1
      inner join visit v2 on v1.clientid = v2.clientid and v1.admitnum <> v2.admitnum and v1.admitdate <= v2.admitdate and v1.dischdate >= v2.admitdate
   where exists (
      select *
      from
         visit v
         inner join agency ag on v.agnum = ag.agnum
      where
         ag.provider = 'HCAN'
         and ag.test = 0
         and v.dischdate > '5/31/2008'
         and v.clientid = v1.clientid
   )
   and m.clientid = v1.clientid
)
order by m.clientid, m.admitdate

I really do hope it helps you.

 

[ESquared]

An error fix. I got one column name swapped. And this doesn't find undischarged visits that overlap. So alter the join to add some Coalesces, and see the bold column name change.

inner join visit v2 on v1.clientid = v2.clientid and v1.admitnum <> v2.admitnum and v1.admitdate <= Coalesce(v2.dischdate, '99991231') and Coalesce(v1.dischdate, '99991231') >= v2.admitdate

 

[gmmastros]

yuck. Year 10,000 bug. Oh man.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ESquared]

When you hit that bug, just call my lawyers.

 

[LT2]

Thank you for responding with a solution ESquared, nice job!

OUCH! Your lashing stung.

My original query replaced (agencyclient w/tClient & agency w/tProvider). I didn't realize you were looking for source data when you referred to 'sample data'. I thought you wanted a sample of result data. I don't know how to generate a DDL script. I sincerely thought I was giving you what you requested and was not purposely trying to give you a hard time or make a bigger burden for you to assist me. Forgive my ignorance, I have better things to do then waste all of our time.

With that said, I had to slightly modify the Visit View to include the AgNum because it is referred to in your last 'Select' portion of your code and modified the coalesce portion to address data type conversion and changed the last date to match the date used to create the ManaualList View.

So my final code looks like this:

GO
create view ManualList
as
select a.clientid, ag.provider, adm.admitdate, dis.dischdate
from agencyclient a
   inner join agency ag on a.agnum = ag.agnum
   inner join admission adm on a.agclientnum = adm.agclientnum
   left join discharge dis on adm.admitnum = dis.admitnum
where
   dis.dischdate > '4/30/2008'  --'xx/xx/xxxx'
   and ag.test = 0 --Eliminates Test Agency Data
group by a.clientid, ag.provider, adm.admitdate, dis.dischdate
GO
create view Visit
as
select
   a.clientid,
   [COLOR=blue]ag.agnum[/color]
   adm.admitnum,
   adm.admitdate,
   dis.dischnum,
   dis.dischdate
from
   agencyclient a
   [COLOR=blue]inner join agency ag on a.agnum = ag.agnum[/color]
   inner join admission adm on a.agclientnum = adm.agclientnum
   left join discharge dis on adm.admitnum = dis.admitnum
go
select m.*
from
   manuallist m
where exists (
   select *
   from
      visit v1
      inner join visit v2 on v1.clientid = v2.clientid 
        and v1.admitnum <> v2.admitnum 
        [COLOR=blue]and v1.AdmitDate <= Coalesce(cast(v2.DischDate as smalldatetime), 
          cast('01/01/2020' as smalldatetime)) --To find undischarged visits that overlap
        and Coalesce(cast(v1.DischDate as smalldatetime), 
          cast('01/01/2020' as smalldatetime)) >= v2.AdmitDate[/color]
   where exists (
      select *
      from
         visit v
         inner join agency ag on v.agnum = ag.agnum
      where
         ag.provider = 'HCAN'
         and ag.test = 0
         and v.dischdate > [COLOR=blue]'4/30/2008'[/color]
         and v.clientid = v1.clientid
   )
   and m.clientid = v1.clientid
)
order by m.clientid, m.admitdate

What I failed to account for was same day discharge/admits which may be within the same Provider or between Providers which is acceptable. I realized this as I was proving the code.

Is there a way to tweak the above code to account for this?

-LT

 

[ESquared]

LT,

I'm sorry that I was so harsh. I will try to be more careful in my requests in the future so that it's clear to everyone what I'm asking for. Sad as it may sound, I really was trying to help, and not just with SQL.

I guess I left out a couple of my final tweaks when I posted the code here... sorry about those problems. I'm glad you figured them out.

For what it's worth, clientid is not and was never unique in the agencyclient / tClient table: there are multiple rows in this table that have the same clientid.

To handle same-day discharges/admits, you'll have to "quantize" the datetime fields to the day value (with no time) on them, and then switch from <= and >= to < and >.

I am sure you probably can't change this, but I do have to say that storing dates as text data type is a big problem. If your dates are stored in the format yyyymmdd hh:mm:ss then you may be able to avoid converting, though this is still far less than ideal. You would use the leftmost 8 characters and then perform the inequality comparison.

Instead of using coalesces, you should experiment with putting in OR clauses to test for the NULL explicitly. It may perform better. Something like

AND (v1.AdmitDate < Convert(smalldatetime, v2.DischDate) OR v1.DischDate IS NULL)
AND (v2.AdmitDate < Convert(smalldatetime, v1.DischDate) OR v1.DischDate IS NULL)

Note: you may want to examine this logic and remember it as it works to find any overlaps between two ranges. If the Range2End >= Range1Begin and Range2Begin <= Range1End then they overlap. You can flip them around so the inequality signs are pointing the same direction, but I find this representation more intuitive. This returns two rows per overlap, one for each party to the overlap, but that doesn't matter in this query since we're using EXISTS.

create view ManualList
as
select a.clientid, ag.provider, adm.admitdate, dis.dischdate
from agencyclient a
   inner join agency ag on a.agnum = ag.agnum
   inner join admission adm on a.agclientnum = adm.agclientnum
   left join discharge dis on adm.admitnum = dis.admitnum
where
   dis.dischdate > '4/30/2008'  --'xx/xx/xxxx'
   and ag.test = 0 --Eliminates Test Agency Data
group by a.clientid, ag.provider, adm.admitdate, dis.dischdate
GO
create view Visit
as
select
   a.clientid,
   ag.agnum
   adm.admitnum,
   adm.admitdate,
   dis.dischnum,
   dis.dischdate
from
   agencyclient a
   inner join agency ag on a.agnum = ag.agnum
   inner join admission adm on a.agclientnum = adm.agclientnum
   left join discharge dis on adm.admitnum = dis.admitnum
go
select m.*
from
   manuallist m
where exists (
   select *
   from
      visit v1
      inner join visit v2 on v1.clientid = v2.clientid
        and v1.admitnum <> v2.admitnum
        and [blue]Left([/blue]v1.AdmitDate[blue], 8) <[/blue] Coalesce(cast([blue]Left([/blue]v2.DischDate[blue], 8)[/blue] as smalldatetime),
          cast('01/01/2020' as smalldatetime)) --To find undischarged visits that overlap
        and Coalesce(cast(Left(v1.DischDate, 8) as smalldatetime),
          cast('01/01/2020' as smalldatetime)) [blue]> Left([/blue]v2.AdmitDate[blue], 8)[/blue]
   where exists (
      select *
      from
         visit v
         inner join agency ag on v.agnum = ag.agnum
      where
         ag.provider = 'HCAN'
         and ag.test = 0
         and v.dischdate > '4/30/2008'
         and v.clientid = v1.clientid
   )
   and m.clientid = v1.clientid
)
order by m.clientid, m.admitdate

Here I am assuming that you can extract just the date portion from the date string easily. That is likely more efficient than converting to date first, then chopping the time off afterward.

If the dates were stored as a true date data type, you could cut off the time part with datediff(dd, 0, DateColumn).

Again, don't forget to experiment with the OR version I gave above. Just incorporate the Left(datecolumn, 8) in there.

If your date column text storage format doesn't lend itself to an easy extraction of the date portion, post a new question asking for how to do that, with a exact example of the actual data in your table (one is enough, or post two if they are varying length such as 1/1/2009 and 12/31/2009 being 2 characters different in length).

 

[ESquared]

I missed some blue but you get the idea.