Date Math based on data in the previous row

[scriptscribe]

Hi,

I have a table in which I want to perform the following function:

If StartDate <= AdmitDate then
(If DischargeDate > End Date then ((EndDate - (AdmitDate + 1)))
Else (DischargeDate - AdmitDate)
Else If AcctNum(from Current Row) = AcctNum (from Previous Row) then (DischargeDate - StartDate)

Basically, the above is a big iif(,,) statement from Excel. That part I can figure out. What I'm having difficulty with is referencing the previous row to compare the account numbers to see if there is a match on that previous row. If so, I want to perform different date calculations than if it is a unique row.

Here is an example of data from my table:

HospitalFK AcctNum AdmitDate DischDate FirstName LastName SSN PatientAcctIPK
1310 1001725231 12/13/06 12/14/06 MARY HANKEY 550527648 25819966
1310 1001725231 01/20/07 01/22/07 MARY HANKEY 550527648 25819966
1310 1001392230 9/2/05 9/6/05 CATHERINE DIAZ 762488212 25868116
1310 1002054730 4/26/05 5/13/05 LILLIAN JONES 985200344 26046401

For anyone who's curious, yes (of course), I changed names and SSNs in the above data before posting them here.

Below is my code so far. I've isolated the dupes and just need to know how to reference the previous row. BTW, in the below code, #PartA is the temp table that is composed of data from the PatientAcctsCodes table and another called PatientAccts, which just has financial data on it, such as TotalCharges and CurrentBalance.

select Count(*) as Amount, acctnum, c.AdmitDate, c.DischargeDate, c.FirstName, c.LastName, c.DOB, c.SSN, c.PatientAcctIPK 
from #PartA c, patientacctscodes pac, foundcoverage fc, medicaidcoverage mc 
where c.patientacctipk = pac.patientacctifk and c.hospitalfk = pac.hospitalfk and c.patientacctipk = fc.patientacctifk
and fc.hospitalfk = mc.hospitalfk and fc.foundcoverageipk = mc.foundcoverageifk
and hitstatus in (1,2)
group by acctnum, c.firstname, c.lastname, c.dob, c.ssn, c.admitdate, c.dischargedate, fc.coverageid, mc.startdate, mc.enddate, mc.programtype, pac.finclasscurrent, pac.insurancecode1, pac.insurancecode2, pac.insurancecode3,
pac.servicetype
having count(AcctNum) > 1
order by acctnum

I wrote a cursor that I thought would work, but after it ran for over an hour, I cancelled it, as it doesn't do me any good if it's going to take that long to run. So I'm back to trying to do it this way.

Any help will be greatly appreciated.

Thanks,
Mike

 

[scriptscribe]

If there's any additional information that I can provide or questions that I can answer, please let me know.

 

[scriptscribe]

I tried the below code and it returned 0 rows. I know that there are dupes in my database, so it should have returned several dupes.

I needed to get rid of the cumbersome where clause in my join, so I performed it in another query and inserted the records into a temp table called #DateMath so that I could do a clean join.

select * from #DateMath c
join (Select Max(Acctnum) as AcctNumID, a.acctnum, a.firstname, a.lastname, a.dob
	, a.ssn, a.admitdate, a.dischargedate, a.coverageid, a.startdate, a.enddate
	, a.programtype, a.finclasscurrent, a.insurancecode1, a.insurancecode2
	, a.insurancecode3, a.servicetype
	From #SumDays a
	group by a.acctnum, a.firstname, a.lastname, a.dob
	, a.ssn, a.admitdate, a.dischargedate, a.coverageid, a.startdate, a.enddate
	, a.programtype, a.finclasscurrent, a.insurancecode1, a.insurancecode2
	, a.insurancecode3, a.servicetype
	Having (Count(*) > 1)) B

on c.AcctNum = B.AcctNum

order by c.AcctNum

 

[scriptscribe]

I figured it out myself using this:

select * from #SumDays c
join (Select Max(Acctnum) as AcctNumID, a.AcctNum, a.firstname, a.lastname, a.dob
	From #Sumdays a
	group by a.AcctNum, a.firstname, a.lastname, a.dob
	Having (Count(AcctNum) > 1)) B
on c.AcctNum = B.AcctNum
order by B.AcctNum