help with ordering records

[georgia51390]

Hi there,
I have a table(table A) that contains patients and appointment dates. A patient may be in the table numerous times. I need to create a new table that has each record from table A for each patient ordered from the earliest to latest date. Is there a way to create a new column that orders them, maybe assigns them a number 1 - whatever. I eventually need to take the earliest date for each patient and the MIN function did not work.
Thanks!

 

[tlbroadbent]

Question: Why do you need to order the data in a table? In relational tables, order is unimportant. You can always order rows in result sets when selecting data.

Will the new table differ in any way from the original table other than order of data? If there is no other difference then you will be wasting space and time by creating a new table.

You can cause SQL server to physically order the data by creating a clustered index on appointment date column or on the patientID and appointment date columns. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[georgia51390]

It is kind of complicated. I have patient IDs, admission dates, and discharge dates. I need to look at every patient and their discharge date. I need to select, for each patient, their earliest discharge date. Then I need to look at their next admission date and only select those records that have a discharge date with no admission date within 7 days of the discharge. In the end, I want a table of patients that are present only once, with their earliest discharge date and no following admission within 7 days of discharge. Does that make sense? Is there an easier way to do this other than ordering each discharge?

 

[tlbroadbent]

You can what you need in a query utilizing a derived table and a correlated sub-query. You could even create a view from this script.

Here is the basic script. You'll have to modify for your table and column names, adding additional columns as needed.

Select PatientID, Min(DischargeDate) As DischargeDate
(Select PatientID, DischargeDate
From PatientTable As p
Where Not Exists
(Select * From PatientTable
Where PatientID=p.PatientID
And AdmitDate<=Dateadd(d,7,p.DischargeDate))) As q

Hopefully, I've understood your requirement correctly. Let me know if you have questions or problems with this script. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[georgia51390]

Hey, THANKS Terry! I will try on a sample table and see if it works. Thanks so much!