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
but i think im still missing something since i'm getting errordim 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"
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 needPublic 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
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
if it is possible so how can i call this function thenCREATE TRIGGER `delete`
AFTER INSERT
ON visits FOR EACH ROW
DELETE FROM visits
WHERE sdat AND edat < DATE_SUB(NOW(), INTERVAL 1080 DAY)
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?