Detail about query

1

Hi ,

I am new to SQL programming , we used one condition to retrieve the date as shown below.

isnull((select Top 1 CONVERT(Varchar(20),b.EffectiveDateTime,101)
from a_DSS.dbo.TempBedData b where b.VisitID = a.VisitID and b.EffectiveDateTime > a.EffectiveDateTime order by b.EffectiveDateTime),
CONVERT(Varchar(20),c.DischargeDateTime,101)) as LocationDismissalDate,

this is written by one of my colleague , I didn't understand this condition clearly but its working good for some data but for some data its not working..

can you explain this query what is trying to pull ?

Thanks,
Archana

2

Without the rest of the query I'm making the assumption that this is one data item returned in a larger SELECT statement. If so, this looks like something called a "correlated subquery". It means that you are using a second query to define some data and it somehow relates to the primary query. You can see that the VisitID and EffectiveDateTime fields are how these two relate. The subquery seems to be looking for the earliest Effective date which is larger than the primary query's Effective date. If there is no data that meets this criteria, use the Discharge date. Correlated subqueries run the risk of having poor performance.
You mentioned that the date is not always accurate but didn't say how it's wrong. Can you elaborate on that?

1 Like
3

Hi Stephen,

Yes it should return earliest Effective date which is larger than the primary query's Effective date . If there is no data that meets this criteria, this should use Discharge date .

For Example : If person XXX admitted in ED at - 5/17/16 at 09:30 am (This is nothing but Effective date for location ED)
Then we move the person 5E at 5/18/16 at 3:30pm (Effective date for location 5E)
Then he discharged from 5E location at 5/19/16 7:30am (This is discharge date)

So This is how my results should be

For ED - Arrival Date 5/17/16 and Arrival time 9:30 am and DismissalDate 5/18/16 and DismissalTime 3:30pm (This is nothing but 5E location arrival time , But the problem is here query returning discharge date which is wrong )

For 5E- Arrival Date 5/18/16 and Arrival time 3:30 pm and DismissalDate 5/19/16 and DismissalTime 7:30am (This is nothing but Discharge date time )

For some of data it is working fine. but most of data it is returning Discharge date blindly..

Below is the full query , Can you please suggest as soon as possible ? :frowning:

**select **
VisitID, EventDateTime, EventSeqID, LocationID, RoomID, EffectiveDateTime
**into a_DSS.dbo.TempBedData **
from AdmVisits
where Code IN ('TRADM','EADMOB')
and (EffectiveDateTime > @FromDate and EffectiveDateTime < @ToDate)

**select **
--a.VisitID as RemoveLater,
c.UnitNumber as MRN,
**SUBSTRING(c.Name, 1, CHARINDEX(',', c.Name) - 1) AS LastName, **
**SUBSTRING(c.Name, CHARINDEX(',', c.Name)+1,30) AS FirstName, **
Convert(Varchar(20),c.BirthDateTime,101) as BirthDate, **
CONVERT(Varchar(20),isnull(c.AdmitDateTime,c.ServiceDateTime),101) as AdmitDate,
c.Account as EncounterID, **
a.LocationID as Unit,
a.RoomID as Room,
CONVERT(Varchar(20),a.EffectiveDateTime,101) as LocationArrivalDate,
CONVERT(Varchar(15),Cast(a.EffectiveDateTime as time), 100) as LocationArrivalTime,
isnull((select Top 1 CONVERT(Varchar(20),b.EffectiveDateTime,101) **
** from a_DSS.dbo.TempBedData b where b.VisitID = a.VisitID and b.EffectiveDateTime > a.EffectiveDateTime order by b.EffectiveDateTime),
** CONVERT(Varchar(20),c.DischargeDateTime,101)) as LocationDismissalDate,
isnull((select Top 1 CONVERT(Varchar(15),Cast(b.EffectiveDateTime as time), 100) **
** from a_DSS.dbo.TempBedFlow b where b.VisitID = a.VisitID and b.EffectiveDateTime > a.EffectiveDateTime order by b.EffectiveDateTime),
** CONVERT(Varchar(15),Cast(c.DischargeDateTime as time), 100)) as LocationDismissalTime
FROM AdmVisits a
**LEFT JOIN BarVT c ON a.VisitID = c.VisitID **
where a.Code IN ('TRADM','EADMOB')
and (c.DischargeDateTime > @FromDate and c .DischargeDateTime < @ToDate )
order by a.EffectiveDateTime

waiting for your reply//

Thank you so much on helping me on this..

4

can any one please help me on this?

5

Can you supply some simple data (INSERT...) the reproduces the problem? Hopefully with simplified table definitions for the to tables involved (CREATE TABLE...)

6

Hi Stephen,

Sorry, but I dint get you.. You want me to change the table definition ?

I am new to SQL programming.. Please advice me what to do, I provided total query..

Thank you so much for your time.

7

To clarify, it would be useful if you would supply us with the CREATE TABLE statements that are a simplified version of your tables you are using; you can leave out any columns that are not needed. Also, can you supply us with the INSERT statements that populate those tables with enough data to show the problem you are experiencing. Help us to help you by giving us a way to reproduce your symptoms.