<%@ Language=VBScript %> <% if Session("user_name") = "" then Response.Redirect "Index.asp" end if action ="Sched2.asp" If Session("STATUS") = "3" then Session("SQL") = "SELECT PMR.*, PMR2.* FROM (select USER_ID, VID, MAX(Date) as MD from PMR WHERE USERS.STATUS <> '0' GROUP BY PMR.USER_ID, PMR.VID) AS PMR, PMR AS PMR2 WHERE PMR.VID = PMR2.VID AND PMR.MD = PMR2.DATE ORDER BY PMR.MD" else Session("SQL") = "SELECT PMR.*, PMR2.* FROM (select USER_ID, VID, MAX(Date) as MD from PMR WHERE USERS.STATUS <> '0' AND PMR.USER_ID = " & Session("USER_ID") & " GROUP BY PMR.USER_ID, PMR.VID) AS PMR, PMR AS PMR2 WHERE PMR.VID = PMR2.VID AND PMR.MD = PMR2.DATE ORDER BY PMR.MD" ' Session("SQL") = "SELECT PM.*, PM2.* FROM (select VID, MAX(Date) as MD from PM WHERE STATUS <> '0' AND USER_ID = " & Session("USER_ID") & " GROUP BY PM.VID) AS PM, PM AS PM2 WHERE PM.VID = PM2.VID AND PM.MD = PM2.DATE ORDER BY PM.MD" End if title = "Preventative Maintenance" Session("Search_Page") = Right(Request.ServerVariables("URL"), len(Request.ServerVariables("URL")) - InStrRev(Request.ServerVariables("URL"), "/") ) %>

<%=title%>

<% Set DatabaseConnection = Server.CreateObject("ADODB.Connection") DatabaseConnection.Open Application("Connection1") Set Recordset1 = Server.CreateObject("ADODB.Recordset") Recordset1.CursorType = 1 Recordset1.CursorLocation = 2 Recordset1.LockType = 3 Recordset1.ActiveConnection = DatabaseConnection Recordset1.Open Session("SQL"), DatabaseConnection %>

method=post name=Form1>

<% if Recordset1.EOF then %> <% else While Not Recordset1.EOF or Recordset1.BOF ' RESPONSE.Write recordset1("PM.VID") & " " & RECORDSET1("DATE1")& "
" 'response.Write CDate(Recordset1("DATE")) 'response.End ' Response.Write "" Response.Write "" Response.Write "" 'if Recordset1("DATE_DUE") > date() then Response.Write "OK" else Response.Write "OVERDUE" &"" Response.Write "" ROWS = recordset1("PM_ID") Recordset1.MoveNext Wend End If %>

COMPANY	VID	ORDER	DATE	INSPECTION	MILEAGE	DAYS DUE IN

" Response.write Recordset1("COMPANY") Response.write "	"& Recordset1("PMR.VID") & "	" & Recordset1("INVOICE") & "	" & Recordset1("DATE") & "	" & Recordset1("INSPECTION") & "	" & Recordset1("MILEAGE") & "	" & (CDate(Recordset1("DATE")) - Date() +90) & "

<% Recordset1.close DatabaseConnection.Close Set DatabaseConnection = Nothing %> <% If 1 = 2 then if Session("user_name") = "" then Response.Redirect "Index.asp" end if action ="Sched2.asp" If Session("STATUS") = "3" then Session("SQL") = "SELECT PM.*, PM2.* FROM (select USER_ID, VID, MAX(Date) as MD from PM WHERE STATUS <> '0' GROUP BY PM.USER_ID, PM.VID) AS PM, PM AS PM2 WHERE PM.VID = PM2.VID AND PM.MD = PM2.DATE ORDER BY PM.MD" ' Session("SQL") = "SELECT PMR.*, PM2.* FROM (select USER_ID, VID, MAX(Date) as MD from PMR WHERE STATUS <> '0' GROUP BY PMR.USER_ID, PMR.VID) AS PMR, PMR AS PM2 WHERE PMR.VID = PM2.VID AND PMR.MD = PM2.DATE ORDER BY PMR.MD" ' Session("SQL") = "SELECT PM.*, PM2.* FROM (select USER_ID, VID, MAX(Date) as MD from PM WHERE STATUS <> '0' GROUP BY PM.USER_ID, PM.VID) AS PM, PM AS PM2 WHERE PM.VID = PM2.VID AND PM.MD = PM2.DATE ORDER BY PM.MD" else 'Session("SQL") = "SELECT * FROM PM WHERE PM.STATUS <> '0' AND PM.USER_ID = " & Session("USER_ID") & " ORDER BY PM.PM_ID" 'Session("SQL") = "SELECT VID FROM PM GROUP BY VID" 'Session("SQL") = "SELECT MAX(DATE) AS VID FROM PM" ' WORKS Session("SQL") = "SELECT VID, MAX(DATE) AS MD FROM (SELECT * FROM PM WHERE PM.STATUS <> '0' AND PM.USER_ID = " & Session("USER_ID") & " ORDER BY PM.PM_ID) GROUP BY VID" '' Session("SQL") = "(SELECT VID, MAX(DATE) AS MD FROM (SELECT * FROM PM WHERE PM.STATUS <> '0' AND PM.USER_ID = " & Session("USER_ID") & " ORDER BY PM.PM_ID) GROUP BY VID), PM2.MILEAGE FROM PM AS PM2" 'NO WORK 1 Session("SQL") = "SELECT PM.VID, Max(PM.DATE) AS MD, PM2.MILEAGE FROM (SELECT PM.PM_ID, PM.USER_ID, PM.STATUS FROM PM, PM AS PM2 WHERE PM.STATUS <> '0' AND PM.USER_ID = " & Session("USER_ID") & " WHERE PM.VID = PM2.VID) " ' NO WORK 2 Session("SQL") = "SELECT PM.VID, Max(PM.DATE) AS MD, PM2.MILEAGE FROM (SELECT PM.VID FROM PM), PM AS PM2 WHERE PM.VID = PM2.VID " ' Session("SQL") = "SELECT PM.VID, Max(PM.DATE) AS MD, PM2.MILEAGE FROM PM, PM AS PM2 WHERE PM.VID = PM2.VID" ' WORKS Session("SQL") = "SELECT PM.VID, PM2.MILEAGE FROM PM, PM AS PM2 WHERE PM.D1 =(Select max(PM.D1) as DM from PM)" ' WORKS Session("SQL") = "SELECT PM.VID, PM2.MILEAGE FROM PM, PM AS PM2 WHERE PM.VID = PM2.VID" ''' Session("SQL") = "SELECT PM.VID, PM2.MILEAGE FROM (Select max(PM.D1) as DM from PM GROUP BY PM.VID), PM AS PM2" 'Session("SQL") = "SELECT PM2.MILEAGE FROM (SELECT VID, MAX(DATE1) AS MD FROM (SELECT * FROM PM WHERE PM.STATUS <> '0' AND PM.USER_ID = " & Session("USER_ID") & " ORDER BY PM.PM_ID) GROUP BY VID), PM AS PM2 WHERE PM.VID = PM2.VID" 'WORKS: ' Session("SQL") = "SELECT PM.VID, PM2.VID FROM PM, PM AS PM2 WHERE PM.VID = PM2.VID" ' Session("SQL") = "SELECT PM.*, PM2.* FROM (select VID, MAX(Date1) as MD from PM GROUP BY VID) AS PM, PM AS PM2 WHERE PM.VID = PM2.VID AND PM.MD = PM2.DATE1" Session("SQL") = "SELECT PM.*, PM2.* FROM (select VID, MAX(Date) as MD from PM WHERE STATUS <> '0' AND USER_ID = " & Session("USER_ID") & " GROUP BY PM.VID) AS PM, PM AS PM2 WHERE PM.VID = PM2.VID AND PM.MD = PM2.DATE ORDER BY PM.MD" End if title = "Preventative Maintenance" Session("Search_Page") = Right(Request.ServerVariables("URL"), len(Request.ServerVariables("URL")) - InStrRev(Request.ServerVariables("URL"), "/") ) 'GOOD Response.Write ""& Recordset1("PM.VID") & "" & Recordset1("INVOICE") & "" & Recordset1("DATE") & "" & Recordset1("INSPECTION") & "" & Recordset1("MILEAGE") & "" & (CDate(Recordset1("DATE")) - Date() +90) & "" 'if Recordset1("DATE_DUE") > date() then Response.Write "OK" else Response.Write "OVERDUE" &"" end if %>