How to calculate day in different row

aspfun

Registered User.
Local time
Today, 10:38
Joined
Apr 22, 2006
Messages
29
I need help to code days. As the sample data below, days between [ADMDATE] and [DSCHDATE] need to be count as this way (at different rows):

MEMBID---ADMDATE--DSCHDATE--RESULT
06000248 08/02/2010 08/04/2010
06000248 08/10/2010 08/12/2010 (08/10/2010 - 08/04/2010=6)
06000248 08/20/2010 08/22/2010 (08/20/2010 - 08/12/2012=8)
 
Have a look at the DateDiff() function:
Code:
http://www.techonthenet.com/access/functions/date/datediff.php
 
DateDiff is only part of the solution. You will also need DMAX: http://www.techonthenet.com/access/functions/domain/dmax.php

What you need to do is to look back into the table you are querying to find the record just prior to the one you are working on (This is where DMAX comes in). Next you will take that date and subtract it from the current record (this is where DateDiff comes in).

The key is getting the criteria in the DMAX function correct. You want to use the current record's MEMBID and then the record that is less than the current records date. Hopefully that should be enough to get you what you need. If you need more specifics post back.
 
Good eye there plog. I didn't even realise it was looking back at the previous record.
 
This problem was solved in SQL as below.
How to convert it to Access vba?

CREATE TABLE dbo.PatientInfo (
MEMBID CHAR(8),
ADMDATE DATE,
DSCHDATE DATE
);
go

INSERT INTO dbo.PatientInfo
VALUES ('06000248','08/02/2010','08/04/2010'),
('06000248','08/10/2010','08/12/2010'),
('06000248','08/20/2010','08/22/2010'),
('06000348','08/02/2011','08/04/2011'),
('06000348','08/10/2011','08/12/2011'),
('06000348','08/20/2011','08/22/2011');
GO



WITH BaseData AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY MEMBID ORDER BY DSCHDATE) rn,
MEMBID,
ADMDATE,
DSCHDATE
FROM
dbo.PatientInfo
)
SELECT
bd1.MEMBID,
bd1.ADMDATE,
bd1.DSCHDATE,
ISNULL(DATEDIFF(dd, bd2.DSCHDATE, bd1.ADMDATE),0) DaysOut
FROM
BaseData bd1
LEFT OUTER JOIN BaseData bd2
ON (bd1.rn = bd2.rn + 1
AND bd1.MEMBID = bd2.MEMBID)
;
GO

DROP TABLE dbo.PatientInfo;
GO

-----
MEMBID--ADMDATE----DSCHDATE-DaysOut
06000248 2010-08-02 2010-08-04 0
06000248 2010-08-10 2010-08-12 6
06000248 2010-08-20 2010-08-22 8
06000348 2011-08-02 2011-08-04 0
06000348 2011-08-10 2011-08-12 6
06000348 2011-08-20 2011-08-22 8
 
You didn't tell us you were working with SQL Server. Glad it's solved though.
 
In SQL server query window but my question is:
Is it possible to convert it to Access query or VBA running in Access?
 
You've used SQL Server specific functions. plog covered how you can do it in Access.
 

Users who are viewing this thread

Back
Top Bottom