• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 892
  • Last Modified:

Compare current date to previous date - MS Access or SQL

Below is the query I am using to list a patients Admit Date.  I want to be able to compare the listed Admit Date to the patient's previous admission date (based on the Unit Number) to see if they were here <= 30 days ago.

The query is currently pulling the Max(AdmitDateTime) as the previous date.  I need it to be the last admission before the current one.

Can someone rewrite the MS-Access SQL as SQL Server 2008 syntax?

SELECT T.UnitNumber, T.AccountNumber, T.AdmitDateTime, Max(T2.AdmitDateTime) AS PrevAdmitDate
FROM qry_Lace_Last_Admit_30Days AS T LEFT JOIN qry_Lace_Last_Admit_30Days AS T2 ON T.UnitNumber = T2.UnitNumber
GROUP BY T.UnitNumber, T.AccountNumber, T.AdmitDateTime;


I have attached a sample of the data I am getting.

Thanks

Glen
Admissions.xlsx
0
GPSPOW
Asked:
GPSPOW
1 Solution
 
Duy PhamFreelance IT ConsultantCommented:
You might try to add one more condition to T2 when joining with T:  T LEFT JOIN T2 ON T.UnitNumber = T2.UnitNumber AND T2.AdmitDateTime < T.AdmitDateTime

Full query could be:
SELECT T.UnitNumber, T.AccountNumber, T.AdmitDateTime, Max(T2.AdmitDateTime) AS PrevAdmitDate
 FROM qry_Lace_Last_Admit_30Days AS T LEFT JOIN qry_Lace_Last_Admit_30Days AS T2 ON T.UnitNumber = T2.UnitNumber AND T2.AdmitDateTime < T.AdmitDateTime
 GROUP BY T.UnitNumber, T.AccountNumber, T.AdmitDateTime;
0
 
PortletPaulfreelancerCommented:
This result:
| UnitNumber | AccountNumber |              AdmitDateTime |              PrevAdmitDate |
|------------|---------------|----------------------------|----------------------------|
| M000000464 |  A00000258668 | December, 15 2014 00:00:00 |     June, 06 2007 00:00:00 |
| M000002322 |  A00000266945 | February, 13 2015 00:00:00 |  January, 15 2015 00:00:00 |
| M000003283 |  A00000265234 |  January, 29 2015 00:00:00 |  January, 02 2015 00:00:00 |
| M000003485 |  A00000262887 |  January, 21 2015 00:00:00 |    April, 21 2011 00:00:00 |
| M000005788 |  A00000268924 |    March, 04 2015 00:00:00 | December, 05 2014 00:00:00 |
| M000011408 |  A00000269384 |    March, 07 2015 00:00:00 | February, 25 2015 00:00:00 |
| M000013920 |  A00000266232 | February, 07 2015 00:00:00 |     July, 11 2008 00:00:00 |
| M000014847 |  A00000269702 |    March, 11 2015 00:00:00 |  January, 03 2015 00:00:00 |
| M000038170 |  A00000266338 | February, 09 2015 00:00:00 | November, 28 2014 00:00:00 |
| M000047791 |  A00000267030 | February, 15 2015 00:00:00 | December, 20 2011 00:00:00 |
| M000080315 |  A00000266492 | February, 10 2015 00:00:00 |  January, 02 2015 00:00:00 |
| M000080809 |  A00000267234 |    March, 02 2015 00:00:00 |                            |
| M000081478 |  A00000260814 | December, 17 2014 00:00:00 |   August, 18 2014 00:00:00 |
| M000086477 |  A00000263091 |  January, 10 2015 00:00:00 |                            |
| M000094830 |  A00000261910 | December, 29 2014 00:00:00 | December, 15 2014 00:00:00 |
| M000100980 |  A00000266264 | February, 07 2015 00:00:00 |  January, 09 2015 00:00:00 |
| M000105528 |  A00000265359 |  January, 30 2015 00:00:00 |                            |
| M000106104 |  A00000268815 |    March, 03 2015 00:00:00 | February, 13 2015 00:00:00 |
| M000110889 |  A00000263019 |  January, 10 2015 00:00:00 |                            |
| M000111960 |  A00000266636 | February, 11 2015 00:00:00 |                            |

Open in new window


From this query:
WITH CTE
AS (SELECT
      UnitNumber
    , AccountNumber
    , AdmitDateTime
    , ROW_NUMBER() OVER (PARTITION BY UnitNumber ORDER BY AdmitDateTime DESC) AS rn
FROM table1)
SELECT
      cte.UnitNumber
    , cte.AccountNumber
    , cte.AdmitDateTime
    , prev.AdmitDateTime AS PrevAdmitDate
FROM CTE
      LEFT JOIN CTE AS prev ON cte.UnitNumber = prev.UnitNumber
      AND cte.rn + 1 = prev.rn
WHERE cte.rn = 1
;

Open in new window


The query uses ROW_NUMBER() to give each visit by a UnitNumber a number starting at 1 = latest and increasing by 1 for each previous visit. Then it is possible to join one visit to it's previous visit by this: AND cte.rn + 1 = prev.rn

Note: from SQL Server 2012 on, you could use the LAG() function which should simplify this a bit.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
GPSPOW, do you still need help on this question?
0
 
GPSPOWAuthor Commented:
Thanks to all who responded.

The CTE solution had problems with when I copied it over to my database.

Duy's solution was the best with some modification.

glen
0
 
PortletPaulfreelancerCommented:
>>"The CTE solution had problems with when I copied it over to my database."

For future reference it isn't necessary to use a CTE in this case, it is just an option.


SELECT
      cte.UnitNumber
    , cte.AccountNumber
    , cte.AdmitDateTime
    , prev.AdmitDateTime AS PrevAdmitDate
FROM (SELECT
      UnitNumber
    , AccountNumber
    , AdmitDateTime
    , ROW_NUMBER() OVER (PARTITION BY UnitNumber ORDER BY AdmitDateTime DESC) AS rn
FROM table1) CTE
      LEFT JOIN CTE AS prev ON cte.UnitNumber = prev.UnitNumber
      AND cte.rn + 1 = prev.rn
WHERE cte.rn = 1
;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now