how find next greater than Date in sql

Question

0.00/5 (No votes)

See more:

<big>patientno	VisitDate	NextVisitDate</big>
210	1/1/2001	5/1/2001
210	5/1/2001	9/1/2001
210	9/1/2001	NotFound
211	1/1/2001	5/1/2001
211	5/1/2001	9/1/2001
211	9/1/2001	NotFound

i want to Show next visit date for each patient with current visitdate

i have only two columns 1,patientno , 2nd visitdate how show nextvisitdate

Select Patientno,visitdate (nextvisitdate) from hospital

Posted 23-Dec-13 20:08pm

saimm

Updated 23-Dec-13 20:13pm

Maciej Los

v2

Add a Solution

Comments

Ganesh Raja 24-Dec-13 2:31am

If u have 2 columns then what you need to show in nextvisitdate?
its a current(getdate()) date or some other?

Christian Graus 24-Dec-13 3:47am

What you need to be clear on is, where does next visit date come from ? Does the system assume that someone only has ONE future visit date ? If so, this is clearly homework and not a real world system. Either way, you should not design it like this, the design is broken. All visits should be visits, with a flag to say if the person showed up. Appointments don't work on the basis of the format you're showing, not anywhere.

4 solutions

Solution 3

I would imagine you want to do a select where the NextVisitDate > getdate(), to ensure you don't get back 'next visit dates' that are in the past, because people don't have a future visit date.

It makes no sense to order by patientno if you're partitioning by patientno, the order should just be the date. I also don't see why you need a windowing function here:

select patientno, max(visitdate), case when max(visitdate) > getdate() then max(visitdate) else null end
from myTable group by patientNo

is what i think would do it ( but I am booted to Mac right now, so I can't test it ). If that did not work, I'd use CTEs to find the two values I need and go from there.

Posted 23-Dec-13 20:50pm

Christian Graus

Comments

Maciej Los 24-Dec-13 3:44am

Christian, i think OP wants to get existing 'visit dates' and to arrange them in a tabular form, eg.:

visit date 1 | visit date 2
visit date 2 | visit date 3

etc.
Please, see my comment to Solution 1.

Christian Graus 24-Dec-13 3:46am

The OP marked solution 2 as correct, so I think they just want the max values. One other issue, is that people may have one more future visit booked. Really, the business rules need to be laid out clearer for a solution to be offered. I suspect what's needed is a CTE that pulls out visits in the future, and THEN gets the min, not the max, for the NEXT visit.

Maciej Los 24-Dec-13 3:56am

It's quite different requirement, i think ;)
Is it possible to book more than one visit in a future?
I know you're working for medicine bussines ;)

Christian Graus 24-Dec-13 3:58am

I don't work for vets anymore, actually, but I know my wife sometimes books her specialist visits a year at a time. It's not uncommon for people who have to visit regularly, to do that, and people who don't need to, just make ad hoc appointments. Why would the DB need to store my next appt next to my last one, when they are 2 years apart, and after this, it might turn out I don't come back for 3 1/2 ?

Maciej Los 24-Dec-13 4:16am

The difference is that the OP wants to work on "dates in a past", but you want to operate on "dates in a future"...
Sorry, my English isn't perfect.

saimm 6-Jan-14 0:02am

actually Christian, there is Record maintain for admin of hospital ,, not for patient

admin of hospital want to know that which patient come on time as his/her schedule

i apply maciej los solution and its working properly,,
its new for me ,,,

Christian Graus 6-Jan-14 0:04am

OK. You edited your question, it was not at all clear at first that you were not storing the nextvisitdate. If you're using SS2012, there's a much more elegant solution, using the LEAD keyword.

saimm 5-Jan-14 23:59pm

yes u are right Maciej Los,,

Solution 4

If you are using SQL Server 2012, this is much nicer and works fine:

SQL

SELECT patientno, visitdate, lead(visitdate, 1, null) over (PARTITION BY patientno ORDER BY visitdate)
FROM visits v

Posted 5-Jan-14 18:08pm

Christian Graus

Solution 1

Try this:
//I have taken 4th day from visit date as next date, you can change as per your need
Select Patientno,visitdate, DATEADD(day,4,visitdate) as nextvisitdate from hospital

Posted 23-Dec-13 20:26pm

Member 10434230

Comments

saimm 24-Dec-13 2:35am

Thanks, But there is not fix

Maciej Los 24-Dec-13 2:36am

What???
Please, read question carefully. The question is: How to get visit date and next visit date from visitdate field?

Member 10434230 24-Dec-13 2:40am

Ooops.... sorry i missed it. Thanks for bringing this up.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-12-23T20:29:00

Test it:

SQL

DECLARE @tbl TABLE (PatientNo INT, VisitDate DATETIME)

INSERT INTO @tbl (PatientNo, VisitDate)
VALUES(210, '1/1/2001'),
(210, '5/1/2001'),
(210, '9/1/2001'),
(211, '1/1/2001'),
(211, '5/1/2001'),
(211, '9/1/2001')


SELECT t1.PatientNo, t1.Rowno, MAX(t1.VisitDate) AS VisitDate, MAX(t2.VisitDate) AS NextVisitDate
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY PatientNo ORDER BY PatientNo, VisitDate) AS RowNo, PatientNo, VisitDate
    FROM @tbl
    ) AS t1 LEFT JOIN (
        SELECT ROW_NUMBER() OVER(PARTITION BY PatientNo ORDER BY PatientNo, VisitDate) -1 AS RowNo, PatientNo, VisitDate
        FROM @tbl
        ) AS t2 ON t1.RowNo = t2.RowNo
GROUP BY t1.PatientNo, t1.RowNo
ORDER BY t1.PatientNo, t1.RowNo

how find next greater than Date in sql

4 solutions

Solution 2

Solution 3

Solution 4

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us