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

 

[gmmastros]

Can you show some sample data and expected results?

Please, no real names!

This will make it easier for use to help you.

-George

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

 

[LT2]

Sure George,

Basic results:

client provider admitdate dischdate
123 ABC 4/1/2008 6/1/2008
123 B 5/31/2008 8/31/2008
248 ABC 10/1/2008 9/30/2008
346 C 6/11/2008 7/12/2008
346 D 7/11/2008 NULL
782 ABC 11/15/2008 7/1/2008
782 B 6/28/2008 7/3/2008
782 C 7/4/2008 9/30/2008
782 D 10/5/2008 12/27/208
987 B 4/1/2008 5/15/2008
987 D 5/16/2008 7/31/2008


Goal results:

client provider admitdate dischdate
123 ABC 4/1/2008 6/1/2008
123 B 5/31/2008 8/31/2008
782 ABC 11/15/2008 7/1/2008
782 B 6/28/2008 7/3/2008
782 C 7/4/2008 9/30/2008
782 D 10/5/2008 12/27/2008

Does this make sense with what I said I wanted?
LT

 

[gmmastros]

This is an interesting problem.

I think your sample data is flawed.

[tt][blue]782 ABC 11/15/2008 7/1/2008[/blue][/tt]

You have an admit date that is greater than discharge date. I'll assume this was just a 'creating sample data typo error' and that your data in the table is actually ok.

My approach here is to create some smaller queries that I later use as derived tables in a larger query. By doing things this way, it becomes much easier to understand. Also, by INNER JOINing the derived table and the outer query, it allows us to treat the derived table as a filter condition.

For the queries I show, I am basically hard coding your data in to a table variable. This allows me to test the query without having access to your actual tables. Once you are satisfied that the query I show returns the correct data, you will need to modify this to use your actual tables instead.

To get a list of clients that have provider B and discharge date on 6/1/2008 or later...

Declare @Temp Table(Client int, Provider VarChar(10), AdmitDate DateTime, DischDate DateTime)

Insert Into @Temp Values(123,'ABC','4/1/2008' ,'6/1/2008')
Insert Into @Temp Values(123,'B'  ,'5/31/2008','8/31/2008')
Insert Into @Temp Values(248,'ABC','10/1/2008','9/30/2008')
Insert Into @Temp Values(346,'C'  ,'6/11/2008','7/12/2008')
Insert Into @Temp Values(346,'D'  ,'7/11/2008',NULL)
Insert Into @Temp Values(782,'ABC','7/1/2008' ,'11/15/2008')
Insert Into @Temp Values(782,'B'  ,'6/28/2008','7/3/2008')
Insert Into @Temp Values(782,'C'  ,'7/4/2008' ,'9/30/2008')
Insert Into @Temp Values(782,'D'  ,'10/5/2008','12/27/2008')
Insert Into @Temp Values(987,'B'  ,'4/1/2008' ,'5/15/2008')
Insert Into @Temp Values(987,'D'  ,'5/16/2008','7/31/2008')

Select  Client
From    @Temp
Where   Provider = 'B'
        And DischDate >= '20080601'

When you run the above code, you'll see that only those clients that have provider B and a discharge date greater than 6/1/2009 are returned.

Next, let's write a query that returns clients that have overlaps, like this...

Declare @Temp Table(Client int, Provider VarChar(10), AdmitDate DateTime, DischDate DateTime)

Insert Into @Temp Values(123,'ABC','4/1/2008' ,'6/1/2008')
Insert Into @Temp Values(123,'B'  ,'5/31/2008','8/31/2008')
Insert Into @Temp Values(248,'ABC','10/1/2008','9/30/2008')
Insert Into @Temp Values(346,'C'  ,'6/11/2008','7/12/2008')
Insert Into @Temp Values(346,'D'  ,'7/11/2008',NULL)
Insert Into @Temp Values(782,'ABC','7/1/2008' ,'11/15/2008')
Insert Into @Temp Values(782,'B'  ,'6/28/2008','7/3/2008')
Insert Into @Temp Values(782,'C'  ,'7/4/2008' ,'9/30/2008')
Insert Into @Temp Values(782,'D'  ,'10/5/2008','12/27/2008')
Insert Into @Temp Values(987,'B'  ,'4/1/2008' ,'5/15/2008')
Insert Into @Temp Values(987,'D'  ,'5/16/2008','7/31/2008')

Select  Client
From    @Temp As A
        Inner Join @Temp As B
        On A.Client = B.Client
          And A.AdmitDate <> B.AdmitDate
          And A.DischDate <> B.DischDate
Where   A.AdmitDate < B.AdmitDate
        And (A.DischDate > B.AdmitDate
        Or A.AdmitDate > B.DischDate)

Now, let's put the whole thing together in such a way that we get all the data from the main table for those clients that match both of the queries shown above.

Declare @Temp Table(Client int, Provider VarChar(10), AdmitDate DateTime, DischDate DateTime)

Insert Into @Temp Values(123,'ABC','4/1/2008' ,'6/1/2008')
Insert Into @Temp Values(123,'B'  ,'5/31/2008','8/31/2008')
Insert Into @Temp Values(248,'ABC','10/1/2008','9/30/2008')
Insert Into @Temp Values(346,'C'  ,'6/11/2008','7/12/2008')
Insert Into @Temp Values(346,'D'  ,'7/11/2008',NULL)
Insert Into @Temp Values(782,'ABC','7/1/2008' ,'11/15/2008')
Insert Into @Temp Values(782,'B'  ,'6/28/2008','7/3/2008')
Insert Into @Temp Values(782,'C'  ,'7/4/2008' ,'9/30/2008')
Insert Into @Temp Values(782,'D'  ,'10/5/2008','12/27/2008')
Insert Into @Temp Values(987,'B'  ,'4/1/2008' ,'5/15/2008')
Insert Into @Temp Values(987,'D'  ,'5/16/2008','7/31/2008')

Select Distinct Temp.*
From   @Temp As Temp
       Inner Join [!](
         Select Distinct A.Client
         From	@Temp As A
		         Inner Join @Temp As B
			         On A.Client = B.Client
			         And A.AdmitDate <> B.AdmitDate
			         And A.DischDate <> B.DischDate
         Where	A.AdmitDate < B.AdmitDate
		         And (A.DischDate > B.AdmitDate
		         Or A.AdmitDate > B.DischDate)
         ) As A[/!]
         On Temp.Client = A.Client
       Inner Join [blue][b](
         Select  Distinct Client
         From    @Temp
         Where   Provider = 'B'
                 And DischDate >= '20080601'
         ) As B[/b][/blue]
         On Temp.Client = A.Client

Notice the parts highlighted above, with the exception of adding DISTINCT, there are the same queries I showed in the first 2 code blocks. Since these queries ONLY return client's that satisfy the individual queries, the derived tables essentially act as a filter.

Does this make sense?

-George

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

 

[LT2]

Hi George,

Thanks for the wonderful explanation! You assumed correctly on the faulty data set. Yes your code makes sense. I need more experience using temporary tables.

Using your code, the results I show however do not display in AdmitDate order and when I add an Order by A.AdmitDate clause at the end, I get the following error msg:

Msg 207, Level 16, State 1, Line 36
Invalid column name 'AdmitDate'.
Msg 145, Level 15, State 1, Line 36
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

In addition, Provider B has more than one site so I have to extract the data for all four, preferably in one query. Providers have their own agency number, let's assume 1-4 for this case scenario. How do I accommodate for this?

LT

 

[gmmastros]

I need more experience using temporary tables.

In the code I show above, I am using a table variable, not a temp table. They are similar, but different. I encourage you to learn about both because they both have pro's and con's.

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

You tried ordering by A.AdmitDate, but that column isn't being returned. Instead (as per the example above)...

Order By Temp.AdmitDate

In addition, Provider B has more than one site so I have to extract the data for all four, preferably in one query. Providers have their own agency number, let's assume 1-4 for this case scenario. How do I accommodate for this?

I have no idea what this means. In your sample data, I don't see a column for site, nor any mention of site anywhere until now.

-George

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

 

[LT2]

I stand corrected on the variable v.s. temp table and agree more knowledge is required for both and will work on this.

Your explanation of Order by works and thanks.

You are correct, I neglected to include this tidbit and my apologies. I just missed it.

Revised Goal Result Sample Data:

Client Provider AdmitDate DischDate ProvNum
123 ABC 4/1/2008 6/1/2008 250
987 B1 4/1/2008 5/15/2008 211
987 D 5/16/2008 7/31/2008 306
123 B2 5/31/2008 8/31/2008 235
346 C 6/11/2008 7/12/2008 177
782 B3 6/28/2008 7/3/2008 183
782 ABC 7/1/2008 11/15/2008 250
782 C 7/4/2008 9/30/2008 177
346 D 7/11/2008 NULL 306
248 ABC 10/1/2008 9/30/2008 250
782 D 10/5/2008 12/27/2008 306

I would need to capture all the B's if they fit the other criteria.

LT

 

[gmmastros]

Without knowing more, I'd say you need to modify this part:

[tt][blue]
Select Distinct Client
From @Temp
Where Provider = 'B'
And DischDate >= '20080601'
[/blue][/tt]

If your B providers all start with B (followed by any other characters/numbers), then you can use a Like compare instead of =. Like this.

[tt][blue]
Select Distinct Client
From @Temp
Where Provider [!]Like 'B%'[/!]
And DischDate >= '20080601'
[/blue][/tt]


Of course, this will return ANY provider that starts with the letter B (possibly returning more than you want).

You could also 'hardcode' the provider list, like this...

[tt][blue]
Select Distinct Client
From @Temp
Where Provider [!]In ('B1','B2','B3','B4')[/!]
And DischDate >= '20080601'
[/blue][/tt]

There's likely to be a variety of ways to accommodate this. What I show are just a couple ways. The important part to realize is that this part of the query controls the filter for provider and discharge date. Modify this part to suit your data.

Make sense?

-George

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

 

[LT2]

Yes, thank you George. I will attempt to modify for my tables and will keep you posted.

Thank you for being so generous with your knowledge, explanations and understanding.

LT

p.s. Is there simple way to get test data in this forum that is formatted for easier read?

 

[gmmastros]

p.s. Is there simple way to get test data in this forum that is formatted for easier read?

There are a lot of formatting options. Take a look here:

http://www.tipmaster.com/includes/tgmlinfo.cfm?w=450&h=450

The simple answer is... use the TT block. Like this...

[ignore][tt]
Client Provider AdmitDate DischDate ProvNum
123 ABC 4/1/2008 6/1/2008 250
987 B1 4/1/2008 5/15/2008 211
987 D 5/16/2008 7/31/2008 306
123 B2 5/31/2008 8/31/2008 235
346 C 6/11/2008 7/12/2008 177
782 B3 6/28/2008 7/3/2008 183
782 ABC 7/1/2008 11/15/2008 250
782 C 7/4/2008 9/30/2008 177
346 D 7/11/2008 NULL 306
248 ABC 10/1/2008 9/30/2008 250
782 D 10/5/2008 12/27/2008 306
[/tt][/ignore]

Which will look like this:

[tt]
Client Provider AdmitDate DischDate ProvNum
123 ABC 4/1/2008 6/1/2008 250
987 B1 4/1/2008 5/15/2008 211
987 D 5/16/2008 7/31/2008 306
123 B2 5/31/2008 8/31/2008 235
346 C 6/11/2008 7/12/2008 177
782 B3 6/28/2008 7/3/2008 183
782 ABC 7/1/2008 11/15/2008 250
782 C 7/4/2008 9/30/2008 177
346 D 7/11/2008 NULL 306
248 ABC 10/1/2008 9/30/2008 250
782 D 10/5/2008 12/27/2008 306
[/tt]

-George

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

 

[bborissov]

Yes, just click on "Process TGML" link that is bellow the message creation area. Also you could download sql2tgml from George's site to have syntax coloring.
Kill me I can't remember the link right now.
But when I check my archive I'll post it to you.
(BTW it is always a good idea to give a star to whom answered your question, in that case George. That way you show him/her that the answer is helpful to you and also if someone else has the similar problem he/she can concentrate to the marked answers)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

Here it is:
thread183-1350140

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[LT2]

Thank you both!

Stars will be coming upon modification of my tables in case I have a hiccup. Thank you for your reminder Boris. I intend to reward all who take their time to get to a solution.

LT

 

[LT2]

Hi George,

Once you are satisfied that the query I show returns the correct data, you will need to modify this to use your actual tables instead.

I'm not sure how to modify this given the original 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
Group by c.client, p.name, a.admitdate, d.dischdate
Order by c.client, a.admitdate

I tried to Declare @Temp by Setting @Temp with results from the query above. I don't know how to do this. Can you give me a start?

LT

 

[gmmastros]

Try this:

SET NOCOUNT ON

Declare @Temp Table(Client int, Provider VarChar(10), AdmitDate DateTime, DischDate DateTime)

Insert Into @Temp(Client, Provider, AdmitDate, DischDate)
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 

Select Distinct Temp.*
From   @Temp As Temp
       Inner Join (
         Select Distinct A.Client
         From    @Temp As A
                 Inner Join @Temp As B
                     On A.Client = B.Client
                     And A.AdmitDate <> B.AdmitDate
                     And A.DischDate <> B.DischDate
         Where    A.AdmitDate < B.AdmitDate
                 And (A.DischDate > B.AdmitDate
                 Or A.AdmitDate > B.DischDate)
         ) As A
         On Temp.Client = A.Client
       Inner Join (
         Select  Distinct Client
         From    @Temp
         Where   Provider In ('B1','B2','B3','B4')
                 And DischDate >= '20080601'
         ) As B
         On Temp.Client = A.Client

Notice how I declare the table variable. Then, I insert in to it based on your query. Then, finally, I use the table variable in the (relatively) complicated query.

Make sense?

-George

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

 

[LT2]

Yes, this is making more sense.

I slightly modified to actual and receive the following error msg:

Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.
The statement has been terminated.

Any ideas?

LT

 

[gmmastros]

Check this:

[tt][blue]Declare @Temp Table(Client int, Provider VarChar([!]10[/!]), AdmitDate DateTime, DischDate DateTime)
[/blue][/tt]

I just picked a length for that column. You should change to 10 to the actual length of the provider column that you have in the real table.

-George

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

 

[LT2]

Thanks George. I've made the change and now get results.

Should I expect the query will take a long time to run? I stopped after 4 1/2 minutes to check client 1 of results that shouldn't be in the results which I've confirmed. This client was never at Provider B.

I will need to investigate a bit further.

LT

 

[LT2]

George,

After the second run I stopped after 6 min. The results though grouped by client in client order, do not display in admitdate sequential order. Nor do they appear to meet the criteria of having a dischdate >= 20080601 at Provider B.

LT

 

[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?

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?

  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:

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

The different values ABC and B will not be returned in the two rows because there is only one providernum in the client table, only one providernum listed for client 123.