how can i get the last stored DateTime then use it inside my code

How is your data structures? O you effectively hve. Working table
Table that solely contains only active, admitted PTs.
Visits is a transaction table.
One option you can add a view based on data in the visits table where the discharge data is added to the record.

Discharged status means the pt have to have been admitted rather than just visited.....

Do you have flexibility?

sorry if my question was not clear enough i'm new to the coding still learning actually
so about my database i have 2 tables by using PHP Myadmin  first table called patients for pt info and the second one called visits to store pt visits datetime and i created relation between them so i wanted to add this feature to the app which is the pt status if admitted or discharged but i'm not sure of the best way to use to do it
the view idea the one you suggested do you mean by adding one more table name view ??

thank your your replay i really appreciate it
in my (visits) tabla i have only 2 columns one name(sdat) for admission date and one name(edat) for discharge date i don't have column called for arrival actually so i changed the code to be like this and i tried it with my SQL builder unless if you think that i need to add one more column name (arrival)

SELECT a.pid,MAX(ISNULL(b.sdat,"1901-01-01" )),MAX(ISNULL(b.edat,"1901-01-01")) from patients a left join visits b on a.pid=b.pid Where datediff(a,b.sdat,now()) <=365 group by a.pid

Select allOpen in new window

but i think im still missing something since i'm getting error
(SQL Error [1582] [42000]: Incorrect parameter count in the call to native function 'ISNULL)

then if i put if in code should i do it like this but no luck yet:

dim sql as string
sql = SELECT a.pid,MAX(ISNULL(b.sdat,"1901-01-01" )),MAX(ISNULL(b.edat,"1901-01-01")) from patients a left join visits b on a.pid=b.pid Where datediff(a,b.sdat,now()) <=365 group by a.pid"

Select allOpen in new window

i i have a function is it possible that i can get use of this code for auto number if edit it to match my need

Public Function GetAutoNum(TableName As String, ColName As String) As String
        Dim str As String = "select max( " & ColName & " ) + 1 from " & TableName
        Dim tbl As New DataTable
        tbl = GetTable(str)
        Dim AutoNum As String
        If tbl.Rows(0)(0) Is DBNull.Value Then
            AutoNum = "1"
        Else
            AutoNum = tbl.Rows(0)(0)
        End If
        Return AutoNum

Select allOpen in new window

so i edited to look like this:

Public Function GetStatus(TableName As String, ColName As String) As String
        Dim str As String = "SELECT a.pid,MAX(ISNULL(b.sdat,"1901-01-01" )),MAX(ISNULL(b.edat,"1901-01-01")) from patients a left join visits b on a.pid=b.pid Where datediff(a,b.sdat,now()) <=365 group by a.pid"
        Dim tbl As New DataTable
        tbl = GetTable(str)
        Dim GetStatus As String
        If tbl.Rows(0)(0) Is DBNull.Value Then
            GetStatus = "1"
        Else
            GetStatus = tbl.Rows(0)(0)
        End If
        Return GetStatus

Select allOpen in new window

if it is possible so how can i call this function then
by the way i'm interested with your Method about having another 3rd table as visits history but i think i need to add code to let the visits history table deleted records older than 2 years for example to decrease load on the database unless if this can be done by using the trigger event

sorry for the long explanation i hope that i delivered my points well enough thank you for your patient sir

about deleting the old records history i added a trigger like this and i hope it's the correct way

CREATE TRIGGER `delete`
AFTER INSERT
ON visits FOR EACH ROW
    DELETE FROM visits
    WHERE sdat AND edat < DATE_SUB(NOW(), INTERVAL 1080 DAY)

Select allOpen in new window

thank you so much this is so helpful:)
god bless you