complicated sql statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

complicated sql statement

Author

Topic

kk_raju1
Starting Member

3 Posts

Posted - 2006-09-13 : 01:41:56

Guys,

I had some problem retrieving data as some fields are removed fro a table.

Existing Querey:

SELECT Patient.Pat_ID1 as Pat_ID1,
Schedule.App_Date as App_Date,Queue.Status as Status,Schedule.Notes as Notes,
Admin.Attending_MD_ID as Attending_MD_ID,Cpt.Billable as Billable,
Schedule.Sch_Id as Sch_Id, Admin.adm_Diag as Adm_Diag,Admin.adm_Diag2 as Adm_Diag2,Admin.adm_Diag3 as Adm_Diag3, Admin.adm_Diag4 as Adm_Diag4,
Admin.In_Out as In_Out FROM (Schedule Schedule left outer join Queue Queue on Schedule.pat_id1 = Queue.pat_id1 AND Queue.app_date = Schedule.app_date AND Queue.app_time = Schedule.app_time AND Queue.Activity = Schedule.Activity),Patient Patient, Cpt Cpt, Admin Admin, IDENT IDENT WHERE Patient.Pat_ID1 > 9999 AND Patient.Last_Name <> '' AND Patient.First_Name <> '' AND Patient.pat_id1 = Schedule.pat_id1 AND Cpt.hsp_code = Schedule.Activity AND
Admin.pat_id1 = Patient.pat_id1 AND Schedule.app_date = '2005-08-29' AND
Schedule.Location = 115 and Patient.Pat_IDA = Pat_IDA

Now adm_Diag ,adm_Diag2 ,adm_Diag3,adm_Diag4
Are removed from Admin table and I need to get data related to these fields from Medical table
In this fashion.

SELECT Medical.Topography FROM Medical where Diagnosis_Class = 1 and PAT_ID1 = 10214 order by MED_ID

Out put: 1st row: adm_Diag,
Out put: 2nd row: adm_Diag2, and so on.

How can I join/change the existing query to make thigs work.

Kristen
Test

22859 Posts

Posted - 2006-09-13 : 02:02:37

In your SELECT statement change

Admin.adm_Diag as Adm_Diag

to

(SELECT Topography AS adm_Diag FROM Medical WHERE Diagnosis_Class = 1 and PAT_ID1 = 10214 AND MED_ID = 1) as Adm_Diag

Kristen

kk_raju1
Starting Member

3 Posts

Posted - 2006-09-13 : 02:11:21

Hi,

Thanks for the solution.

there is one more proble,
i.e med_id will be some numbers.

we can only order them as ascending but we do not know the numbers.

thanks once again.

Kristen
Test

22859 Posts

Posted - 2006-09-13 : 04:03:12

First one is:

(SELECT TOP 1 Topography AS adm_Diag FROM Medical WHERE Diagnosis_Class = 1 and PAT_ID1 = 10214 ORDER BY MED_ID)

second one is:

(SELECT TOP 1 Topography AS adm_Diag FROM (SELECT TOP 2 Topography FROM Medical WHERE Diagnosis_Class = 1 and PAT_ID1 = 10214 ORDER BY MED_ID) ORDER BY MED_ID DESC)

and so on. But this will be horrible inefficient and you would do better to rethink your data design, or reporting method.

Kristen

kk_raju1
Starting Member

3 Posts

Posted - 2006-09-13 : 04:29:55

Hi,

Yaaa kristi...design nee to be changed,
but for now i should do this.

thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources