Prev: Cann't find ' sys.dm_sql_referenced_entities '
Next: Read file info using SMO locks database!
From: RJ Roberts on 5 Apr 2010 17:17
Something like this should work for you...

;WITH Subselect
AS
(
SELECT PatientAcct, AdmissionDate, DischargeDate, Facility, RowNumber =
Row_Number() OVER(PARTITION BY PatientAcct ORDER BY AdmissionDate ASC)
FROM TestReadmission
)
Select PatientAcct, AdmissionDate, DischargeDate, Facility, RowNumber,
Days = Case RowNumber When 1 then 0
Else (Select DateDiff(Day,DischargeDate, A.AdmissionDate) from Subselect
where A.PatientAcct = PatientAcct and RowNumber = A.RowNumber-1)
End
From Subselect A

HTH
--
RJ Roberts
DB Architect/Developer


"Amanda" wrote:

> I'm trying to write a query that allows me to calculated the number of days
> elapsed between admissions.
>
> Table Example:
> PatientAcct|AdmissionDate|DischargeDate|Facility
> 1234567|1/1/2010|1/5/2010|WMC
> 1234567|1/6/2010|1/10/2010|WMC
> 2345678|2/1/2010|2/2/2010|WMC
> 3456789|2/3/2010|2/5/2010|WMC
> 3456789|2/7/2010|2/10/2010|WMC
> 3456789|2/11/2010|3/1/2010|WMC
>
> Current Query: (it calcuates from first admissionDate to last discharge
> date....not what I want)
> SELECT PatientAcct, AdmissionDate, DischargeDate, Facility,
> DATEDIFF(day, DischargeDate,
> (SELECT MAX(AdmissionDate) AS AdmissionDate
> FROM dbo.Test_Readmission AS B
> WHERE (A.PatientAcct = PatientAcct) AND
> (A.AdmissionDate > DischargeDate))) AS ElapsedDay
> FROM dbo.Test_Readmission AS A
>
> Expected results:
> PatientAcct|AdmissionDate|DischargeDate|Facility|ElapsedDays
> 1234567|1/1/2010|1/5/2010|WMC|
> 1234567|1/6/2010|1/10/2010|WMC|1
> 2345678|2/1/2010|2/2/2010|WMC|
> 3456789|2/3/2010|2/5/2010|WMC|
> 3456789|2/7/2010|2/10/2010|WMC|2
> 3456789|2/11/2010|3/1/2010|WMC|1
>
> Anyone know what the code is to get it to calculate the date from last
> discharge date to current admission date? I'm creating a SQL view in SQL
> 2005. Thanks for help!
From: --CELKO-- on 6 Apr 2010 05:15
Please get in the habit of posting DDL and not narrative descriptions.
Instead a self-join, for 3 bytes you can add the prior discharge date
to the row:

CREATE TABLE Admission_History
(patient_acct CHAR(7) NOT NULL,
prior_discharge_date DATE -- null means no prior
admission_date DATE DEFAULT CURRENT_DATE NOT NULL,
discharge_date DATE -- null means current,
CHECK (admission_date <= discharge_date),
-- need to know re-admit rules for next constraints
CHECK (prior_discharge_date < admission_date),
CHECK (admission_date BETWEEN prior_discharge_date AND
discharge_date),
facility_code CHAR(3) NOT NULL,
PRIMARY KEY (patient_acct, admission_date));

Now the problem is trivial. The CHECK()s need good names and soem
adjustments for your business rules about re-admits. I am not writing
the insertion procedures. But since SQL is a database language, look
for data solutions first.

Also, start using ISO-8601 date formats. Everything you posted is
ambiguous and will not port.
From: Amanda on 6 Apr 2010 09:10
Thank you RJ! This is exactly what I was looking to do.

"RJ Roberts" wrote:

> Something like this should work for you...
>
> ;WITH Subselect
> AS
> (
> SELECT PatientAcct, AdmissionDate, DischargeDate, Facility, RowNumber =
> Row_Number() OVER(PARTITION BY PatientAcct ORDER BY AdmissionDate ASC)
> FROM TestReadmission
> )
> Select PatientAcct, AdmissionDate, DischargeDate, Facility, RowNumber,
> Days = Case RowNumber When 1 then 0
> Else (Select DateDiff(Day,DischargeDate, A.AdmissionDate) from Subselect
> where A.PatientAcct = PatientAcct and RowNumber = A.RowNumber-1)
> End
> From Subselect A
>
> HTH
> --
> RJ Roberts
> DB Architect/Developer
>
>
> "Amanda" wrote:
>
> > I'm trying to write a query that allows me to calculated the number of days
> > elapsed between admissions.
> >
> > Table Example:
> > PatientAcct|AdmissionDate|DischargeDate|Facility
> > 1234567|1/1/2010|1/5/2010|WMC
> > 1234567|1/6/2010|1/10/2010|WMC
> > 2345678|2/1/2010|2/2/2010|WMC
> > 3456789|2/3/2010|2/5/2010|WMC
> > 3456789|2/7/2010|2/10/2010|WMC
> > 3456789|2/11/2010|3/1/2010|WMC
> >
> > Current Query: (it calcuates from first admissionDate to last discharge
> > date....not what I want)
> > SELECT PatientAcct, AdmissionDate, DischargeDate, Facility,
> > DATEDIFF(day, DischargeDate,
> > (SELECT MAX(AdmissionDate) AS AdmissionDate
> > FROM dbo.Test_Readmission AS B
> > WHERE (A.PatientAcct = PatientAcct) AND
> > (A.AdmissionDate > DischargeDate))) AS ElapsedDay
> > FROM dbo.Test_Readmission AS A
> >
> > Expected results:
> > PatientAcct|AdmissionDate|DischargeDate|Facility|ElapsedDays
> > 1234567|1/1/2010|1/5/2010|WMC|
> > 1234567|1/6/2010|1/10/2010|WMC|1
> > 2345678|2/1/2010|2/2/2010|WMC|
> > 3456789|2/3/2010|2/5/2010|WMC|
> > 3456789|2/7/2010|2/10/2010|WMC|2
> > 3456789|2/11/2010|3/1/2010|WMC|1
> >
> > Anyone know what the code is to get it to calculate the date from last
> > discharge date to current admission date? I'm creating a SQL view in SQL
> > 2005. Thanks for help!
 | 
Pages: 1
Prev: Cann't find ' sys.dm_sql_referenced_entities '
Next: Read file info using SMO locks database!