adding a max date to a sql statement

You'll get errors trying to MAX(NULL)

Your final where clause is S.dateend IS NULL AND A.dateend IS NULL

Include "Group by" in the End followed by all the Columns and the "Having" Clause with Max()

GROUP BY all Column Names (specified in select list without an aggregate function)
HAVING MAX(Date)  Condition
ORDER BY -- If necessary

oh sorry i did not add what i wanyted to max, i would like to max L.TDIR_StartDate is never null

1) Using "SELECT *" in code is a bad, bad habit to get into.  You should always specify your recordset columns in production code2) If you want to use MAX, you are going to have to have a GROUP BY clause (just as with any aggregate function)3) If you want to apply criteria to the MAX result, that goes in the HAVING clause, not the WHERE clausePlease try rewriting the query specifying your columns instead of using *, and give a description of what you wanted to do vis-a-vis MAX.

can you explain more on the line
HAVING MAX(Date)  Condition

what is condition
EXMPALE
HAVING MAX(Date)  L.TDIR_StartDate   ?

well having is very similar to WHERE but its used to check for Conditions when using aggregate Functions
So, you will need to mention some value with Conditional operators

like HAVING MAX(L.TDIR_StartDate) > Somedatevalue

> or >= or = or < (as you would do in where clause to check for a condition)

since its a date value it should be enclosed in Single Quotes

Like HAVING MAX(L.TDIR_StartDate) > '2010-01-02 00:00:00'

when i add that line to my code its telling me thats an invalid column but its trhere and used

HERE IS MY NEW CODE

        sqlstmt = "SELECT * FROM TPerson AS P INNER JOIN TDIR AS L ON L.TDIR_PIDM = P.TPERSON_PIDM" & _
                 " INNER JOIN TAccounts AS S ON S.Acct = L.TDIR_Acct" & _
                 " INNER JOIN TCables AS A ON A.DN = L.TDIR_DN " & _
                 " INNER JOIN TBuildings AS B ON B.Bldg3d = A.Bldg3d WHERE COALESCE( L.TDIR_DN, '') NOT IN ('n/a', '')  AND  S.dateend IS NULL  AND A.dateend IS NULL HAVING MAX(L.TDIR_StartDate) > '" & datetime.Now & "' ORDER BY P.TPERSON_Name"

Select allOpen in new window

As  i have suggested before you will need to include all the columns in the Group by which are being used in SELECT statement and then HAVING Clause Like

SELECT A,B,C,Date FROM Table
GROUP BY A,B,C,Date
HAVING MAX(Date) > GETDATE()


If you can post some Sample Data with table structure, i may be able to help you with the EXACT Sql

Check this link for syntax
http://www.databasejournal.com/features/mysql/article.php/3469351/The-HAVING-and-GROUP-BY-SQL-clauses.htm

Change HAVING to AND
HAVING is used in place of a WHERE clause

if i remove HAVING and use and i get this error

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

vdr1620: i added my fields but i still get that sam error L.TDIR_StartDate not found

        sqlstmt = "SELECT * FROM TPerson AS P INNER JOIN TDIR AS L ON L.TDIR_PIDM = P.TPERSON_PIDM" & _
                 " INNER JOIN TAccounts AS S ON S.Acct = L.TDIR_Acct" & _
                 " INNER JOIN TCables AS A ON A.DN = L.TDIR_DN " & _
                 " INNER JOIN TBuildings AS B ON B.Bldg3d = A.Bldg3d" & _
                 " WHERE COALESCE( L.TDIR_DN, '') NOT IN ('n/a', '')  AND  S.dateend IS NULL AND A.dateend IS NULL GROUP BY L.TDIR_PIDM,L.TDIR_Acct,L.TDIR_DN,L.TDIR_StartDate HAVING MAX(L.TDIR_StartDate) > '" & datetime.Now & "' ORDER BY P.TPERSON_Name"

Select allOpen in new window

i don't see any problem with the syntax

If none of the other tables have TDIR_startDate replace L.TDIR_startDate with TDIR_startDate and give it a try, but before that i want you to make sure that TDIR_startDate Exists in that table aliased as L

also you have specified " > " with datetime.now in the Having condition .. i doubt if its going to return anything with > operator

its still not working for me any other ideals?

Can your post your table Structures with Sample data ?

the table structure has 4 joins its kinda hard

here is a sql statement that has all my joins in it from my sql developer

SELECT     P.TPERSON_PIDM, P.TPERSON_CWID, P.TPERSON_SSN, P.TPERSON_UIN, P.TPERSON_Name, P.TPERSON_DirectoryName, P.TPERSON_DateUpdated, 
                      P.TPERSON_DateCreated, P.TPERSON_UpdatedBy, L.TDIR_ID, L.TDIR_PIDM, L.TDIR_DateStart, L.TDIR_DateEnd, L.TDIR_Deleted, L.TDIR_ShowInDir, L.TDIR_DN, 
                      L.TDIR_Acct, L.TDIR_PositionTitle, L.TDIR_Campus, L.TDIR_PhoneNumber, L.TDIR_DateCreated, L.TDIR_DateUpdated, L.TDIR_UpdatedBy, S.ID, S.Acct, S.Name, 
                      S.DeptHeadName, S.DeptHeadSSN, S.DeptHeadUIN, S.DeptHeadCWID, S.DeptDN, S.DeptDNOff, S.[Percent], S.DirSwitch, S.ForwardDept, S.DirSw, S.DateStart, 
                      S.DateEnd, S.Deleted, S.DateUpdated, S.UpdatedBy, A.ID AS Expr1, A.CableComposite, A.DN, A.CableRun, A.CableOut, A.AcctNumber, A.Bldg3d, A.Bldg, A.Room, 
                      A.IDFNumber, A.TRUNK, A.PhoneType, A.DS1, A.DS2, A.EthernetPort, A.AppleTalk, A.VoiceMailSw, A.Remarks, A.AcctOld, A.AcctDate, A.DateUpdated AS Expr2, 
                      A.UpdatedBy AS Expr3, A.DateStart AS Expr4, A.DateEnd AS Expr5, A.Deleted AS Expr6, B.ID AS Expr7, B.Bldg3d AS Expr8, B.MAP_NO, B.BuildingName, 
                      B.BuildingNameShort, B.BuildingNameAlias
FROM         TPerson AS P INNER JOIN
                      TDIR AS L ON L.TDIR_PIDM = P.TPERSON_PIDM INNER JOIN
                      TAccounts AS S ON S.Acct = L.TDIR_Acct INNER JOIN
                      TCables AS A ON A.DN = L.TDIR_DN INNER JOIN
                      TBuildings AS B ON B.Bldg3d = A.Bldg3d
WHERE     (COALESCE (L.TDIR_DN, '') NOT IN ('n/a', '')) AND (S.DateEnd IS NULL) AND (A.DateEnd IS NULL)
ORDER BY P.TPERSON_Name

Select allOpen in new window

So its actually L.TDIR_DateStart NOT L.TDIR_StartDate.. that's the reason i asked you to check the column name

Change your SQL

sqlstmt = "SELECT L.TDIR_PIDM,L.TDIR_Acct,L.TDIR_DN,L.TDIR_DateStart FROM TPerson AS P INNER JOIN TDIR AS L ON L.TDIR_PIDM = P.TPERSON_PIDM" & _
                 " INNER JOIN TAccounts AS S ON S.Acct = L.TDIR_Acct" & _
                 " INNER JOIN TCables AS A ON A.DN = L.TDIR_DN " & _
                 " INNER JOIN TBuildings AS B ON B.Bldg3d = A.Bldg3d" & _
                 " WHERE COALESCE( L.TDIR_DN, '') NOT IN ('n/a', '')  AND  S.dateend IS NULL AND A.dateend IS NULL GROUP BY L.TDIR_PIDM,L.TDIR_Acct,L.TDIR_DN,L.TDIR_DateStart HAVING MAX(L.TDIR_DateStart) > Getdate() ORDER BY P.TPERSON_Name"

Select allOpen in new window

this is wrong the table that you are selecting the iutems frfom the items dont exist in that table they exist in P.TPE$RSON

Is the sql giving you an error.. all i did was replaced * with the columns that you have used in GROUP BY List.. As i have told you before all the columns without any aggregate function should listed in the Group by Clause

2. i changed the L.TDIR_StartDate to L.TDIR_DateStart as mentioned in the SQL post " here is a sql statement that has all my joins in it from my sql developer "

Did i loose you? are we on the same page ?

yeah i am still here i graduated this weekend so i was very very busy.

you had the correct field name but now i dont have the rest of nyt data from TPERSON

Congratulations!!!!

Ok. Are you saying that this query is working fine, but you do not have all the fields.. If that's your problem, i would say add all the required column Names in the select Clause and also in the Group By clause

yes i am only pulling everything in TDIR  i see that we only selected from that table but i also need to select from TPERSON

Here's the code.. With a Group by and Select with all the columns (as in your Developer Code)

Remove the unnecessary columns from both Select and Group by, If needed

sqlstmt =  " SELECT P.TPERSON_PIDM, P.TPERSON_CWID, P.TPERSON_SSN, P.TPERSON_UIN, P.TPERSON_Name, P.TPERSON_DirectoryName," &_
           "  P.TPERSON_DateUpdated,P.TPERSON_DateCreated, P.TPERSON_UpdatedBy, L.TDIR_ID, L.TDIR_PIDM, L.TDIR_DateStart, L. " &_				  "  TDIR_DateEnd, L.TDIR_Deleted, L.TDIR_ShowInDir, L.TDIR_DN, L.TDIR_Acct, L.TDIR_PositionTitle, L.TDIR_Campus, L. " &_				  "  TDIR_PhoneNumber, L.TDIR_DateCreated, L.TDIR_DateUpdated, L.TDIR_UpdatedBy, S.ID, S.Acct, S.Name,S.DeptHeadName, " &_             "  S.DeptHeadSSN, S.DeptHeadUIN, S.DeptHeadCWID, S.DeptDN, S.DeptDNOff, S.[Percent], S.DirSwitch, S.ForwardDept, " &_
           "  S.DirSw,S.DateStart, S.DateEnd, S.Deleted, S.DateUpdated, S.UpdatedBy, A.ID AS Expr1, A.CableComposite, A.DN,  " &_
           "  A.CableRun,A.CableOut, A.AcctNumber, A.Bldg3d, A.Bldg, A.Room, A.IDFNumber, A.TRUNK, A.PhoneType, A.DS1, A.DS2,  " &_             "  A.EthernetPort, A.AppleTalk, A.VoiceMailSw, A.Remarks, A.AcctOld, A.AcctDate, A.DateUpdated AS Expr2, A.      " &_                "  UpdatedBy AS Expr3, A.DateStart AS Expr4, A.DateEnd AS Expr5, A.Deleted AS Expr6, B.ID AS Expr7, B.Bldg3d AS	 " &_			        "  Expr8, B.MAP_NO, B.BuildingName,B.BuildingNameShort, B.BuildingNameAlias " &_
       " FROM TPerson AS P INNER JOIN TDIR AS L ON L.TDIR_PIDM = P.TPERSON_PIDM" & _
       " INNER JOIN TAccounts AS S ON S.Acct = L.TDIR_Acct" & _
       " INNER JOIN TCables AS A ON A.DN = L.TDIR_DN " & _
       " INNER JOIN TBuildings AS B ON B.Bldg3d = A.Bldg3d" & _
       " WHERE COALESCE( L.TDIR_DN, '') NOT IN ('n/a', '')  AND  S.dateend IS NULL AND A.dateend IS NULL " & _
       " GROUP BY P.TPERSON_PIDM, P.TPERSON_CWID, P.TPERSON_SSN, P.TPERSON_UIN, P.TPERSON_Name, P.TPERSON_DirectoryName," & _
           "  P.TPERSON_DateUpdated,P.TPERSON_DateCreated, P.TPERSON_UpdatedBy, L.TDIR_ID, L.TDIR_PIDM, L.TDIR_DateStart, L. " &_				  "  TDIR_DateEnd, L.TDIR_Deleted, L.TDIR_ShowInDir, L.TDIR_DN, L.TDIR_Acct, L.TDIR_PositionTitle, L.TDIR_Campus, L. " &_				  "  TDIR_PhoneNumber, L.TDIR_DateCreated, L.TDIR_DateUpdated, L.TDIR_UpdatedBy, S.ID, S.Acct, S.Name,S.DeptHeadName, " &_             "  S.DeptHeadSSN, S.DeptHeadUIN, S.DeptHeadCWID, S.DeptDN, S.DeptDNOff, S.Percent, S.DirSwitch, S.ForwardDept, " &_
           "  S.DirSw,S.DateStart, S.DateEnd, S.Deleted, S.DateUpdated, S.UpdatedBy, A.ID , A.CableComposite, A.DN,  " &_
           "  A.CableRun,A.CableOut, A.AcctNumber, A.Bldg3d, A.Bldg, A.Room, A.IDFNumber, A.TRUNK, A.PhoneType, A.DS1, A.DS2,  " &_             "  A.EthernetPort, A.AppleTalk, A.VoiceMailSw, A.Remarks, A.AcctOld, A.AcctDate, A.DateUpdated ,       " &_                          "  A.UpdatedBy , A.DateStart , A.DateEnd, A.Deleted , B.ID A, B.Bldg3d, B.MAP_NO, B.BuildingName,B.BuildingNameShort, " &_
           "  B.BuildingNameAlias " &_
       " HAVING MAX(L.TDIR_DateStart) > Getdate() ORDER BY P.TPERSON_Name"

Select allOpen in new window

ok i ran the query and got an error on the select statement part of the query

SELECT     P.TPERSON_PIDM, P.TPERSON_CWID, P.TPERSON_SSN, P.TPERSON_UIN, P.TPERSON_Name, P.TPERSON_DirectoryName, P.TPERSON_DateUpdated, 
                      P.TPERSON_DateCreated, P.TPERSON_UpdatedBy, L.TDIR_ID, L.TDIR_PIDM, L.TDIR_DateStart, L.TDIR_DateEnd, L.TDIR_Deleted, L.TDIR_ShowInDir, L.TDIR_DN, 
                      L.TDIR_Acct, L.TDIR_PositionTitle, L.TDIR_Campus, L.TDIR_PhoneNumber, L.TDIR_DateCreated, L.TDIR_DateUpdated, L.TDIR_UpdatedBy, S.ID, S.Acct, S.Name, 
                      S.DeptHeadName, S.DeptHeadSSN, S.DeptHeadUIN, S.DeptHeadCWID, S.DeptDN, S.DeptDNOff, S.[Percent], S.DirSwitch, S.ForwardDept, S.DirSw, S.DateStart, 
                      S.DateEnd, S.Deleted, S.DateUpdated, S.UpdatedBy, A.ID AS Expr1, A.CableComposite, A.DN, A.CableRun, A.CableOut, A.AcctNumber, A.Bldg3d, A.Bldg, A.Room, 
                      A.IDFNumber, A.TRUNK, A.PhoneType, A.DS1, A.DS2, A.EthernetPort, A.AppleTalk, A.VoiceMailSw, A.Remarks, A.AcctOld, A.AcctDate, A.DateUpdated AS Expr2, 
                      A.UpdatedBy AS Expr3, A.DateStart AS Expr4, A.DateEnd AS Expr5, A.Deleted AS Expr6, B.ID AS Expr7, B.Bldg3d AS Expr8, B.MAP_NO, B.BuildingName, 
                      B.BuildingNameShort, B.BuildingNameAlias
FROM         TPerson AS P INNER JOIN
                      TDIR AS L ON L.TDIR_PIDM = P.TPERSON_PIDM INNER JOIN
                      TAccounts AS S ON S.Acct = L.TDIR_Acct INNER JOIN
                      TCables AS A ON A.DN = L.TDIR_DN INNER JOIN
                      TBuildings AS B ON B.Bldg3d = A.Bldg3d
WHERE     COALESCE (L.TDIR_DN, '') NOT IN ('n/a', '') AND S.dateend IS NULL AND A.dateend IS NULL
GROUP BY P.TPERSON_PIDM, P.TPERSON_CWID, P.TPERSON_SSN, P.TPERSON_UIN, P.TPERSON_Name, P.TPERSON_DirectoryName, P.TPERSON_DateUpdated, 
                      P.TPERSON_DateCreated, P.TPERSON_UpdatedBy, L.TDIR_ID, L.TDIR_PIDM, L.TDIR_DateStart, L.TDIR_DateEnd, L.TDIR_Deleted, L.TDIR_ShowInDir, L.TDIR_DN, 
                      L.TDIR_Acct, L.TDIR_PositionTitle, L.TDIR_Campus, L.TDIR_PhoneNumber, L.TDIR_DateCreated, L.TDIR_DateUpdated, L.TDIR_UpdatedBy, S.ID, S.Acct, S.Name, 
                      S.DeptHeadName, S.DeptHeadSSN, S.DeptHeadUIN, S.DeptHeadCWID, S.DeptDN, S.DeptDNOff, S. PERCENT, S.DirSwitch, S.ForwardDept, S.DirSw, S.DateStart, 
                      S.DateEnd, S.Deleted, S.DateUpdated, S.UpdatedBy, A.ID, A.CableComposite, A.DN, A.CableRun, A.CableOut, A.AcctNumber, A.Bldg3d, A.Bldg, A.Room, A.IDFNumber, 
                      A.TRUNK, A.PhoneType, A.DS1, A.DS2, A.EthernetPort, A.AppleTalk, A.VoiceMailSw, A.Remarks, A.AcctOld, A.AcctDate, A.DateUpdated, A.UpdatedBy, A.DateStart, 
                      A.DateEnd, A.Deleted, B.ID A, B.Bldg3d, B.MAP_NO, B.BuildingName, B.BuildingNameShort, B.BuildingNameAlias
HAVING      MAX(L.TDIR_DateStart) > Getdate()
ORDER BY P.TPERSON_Name

Select allOpen in new window

Well, you need to post the error..

sorry the erorr is attached below
sql-phone-error.png

In the Group by Clause change S.PERCENT to S.[Percent]

new error incorrect syntax near A. i think it may be a jeyword TRUNK.
sql-phone-error.png

TRUNK is not a keyword in SQL.. Did you change the S.percent to S.[Percent] in the GROUP BY clause ?

yes
here is the sql statement

SELECT     P.TPERSON_PIDM, P.TPERSON_CWID, P.TPERSON_SSN, P.TPERSON_UIN, P.TPERSON_Name, P.TPERSON_DirectoryName, P.TPERSON_DateUpdated, 
                      P.TPERSON_DateCreated, P.TPERSON_UpdatedBy, L.TDIR_ID, L.TDIR_PIDM, L.TDIR_DateStart, L.TDIR_DateEnd, L.TDIR_Deleted, L.TDIR_ShowInDir, L.TDIR_DN, 
                      L.TDIR_Acct, L.TDIR_PositionTitle, L.TDIR_Campus, L.TDIR_PhoneNumber, L.TDIR_DateCreated, L.TDIR_DateUpdated, L.TDIR_UpdatedBy, S.ID, S.Acct, S.Name, 
                      S.DeptHeadName, S.DeptHeadSSN, S.DeptHeadUIN, S.DeptHeadCWID, S.DeptDN, S.DeptDNOff, S.[Percent], S.DirSwitch, S.ForwardDept, S.DirSw, S.DateStart, 
                      S.DateEnd, S.Deleted, S.DateUpdated, S.UpdatedBy, A.ID AS Expr1, A.CableComposite, A.DN, A.CableRun, A.CableOut, A.AcctNumber, A.Bldg3d, A.Bldg, A.Room, 
                      A.IDFNumber, A.TRUNK, A.PhoneType, A.DS1, A.DS2, A.EthernetPort, A.AppleTalk, A.VoiceMailSw, A.Remarks, A.AcctOld, A.AcctDate, A.DateUpdated AS Expr2, 
                      A.UpdatedBy AS Expr3, A.DateStart AS Expr4, A.DateEnd AS Expr5, A.Deleted AS Expr6, B.ID AS Expr7, B.Bldg3d AS Expr8, B.MAP_NO, B.BuildingName, 
                      B.BuildingNameShort, B.BuildingNameAlias
FROM         TPerson AS P INNER JOIN
                      TDIR AS L ON L.TDIR_PIDM = P.TPERSON_PIDM INNER JOIN
                      TAccounts AS S ON S.Acct = L.TDIR_Acct INNER JOIN
                      TCables AS A ON A.DN = L.TDIR_DN INNER JOIN
                      TBuildings AS B ON B.Bldg3d = A.Bldg3d
WHERE     COALESCE (L.TDIR_DN, '') NOT IN ('n/a', '') AND S.dateend IS NULL AND A.dateend IS NULL
GROUP BY P.TPERSON_PIDM, P.TPERSON_CWID, P.TPERSON_SSN, P.TPERSON_UIN, P.TPERSON_Name, P.TPERSON_DirectoryName, P.TPERSON_DateUpdated, 
                      P.TPERSON_DateCreated, P.TPERSON_UpdatedBy, L.TDIR_ID, L.TDIR_PIDM, L.TDIR_DateStart, L.TDIR_DateEnd, L.TDIR_Deleted, L.TDIR_ShowInDir, L.TDIR_DN, 
                      L.TDIR_Acct, L.TDIR_PositionTitle, L.TDIR_Campus, L.TDIR_PhoneNumber, L.TDIR_DateCreated, L.TDIR_DateUpdated, L.TDIR_UpdatedBy, S.ID, S.Acct, S.Name, 
                      S.DeptHeadName, S.DeptHeadSSN, S.DeptHeadUIN, S.DeptHeadCWID, S.DeptDN, S.DeptDNOff, S.[Percent], S.DirSwitch, S.ForwardDept, S.DirSw, S.DateStart, 
                      S.DateEnd, S.Deleted, S.DateUpdated, S.UpdatedBy, A.ID, A.CableComposite, A.DN, A.CableRun, A.CableOut, A.AcctNumber, A.Bldg3d, A.Bldg, A.Room, A.IDFNumber, 
                      A.TRUNK, A.PhoneType, A.DS1, A.DS2, A.EthernetPort, A.AppleTalk, A.VoiceMailSw, A.Remarks, A.AcctOld, A.AcctDate, A.DateUpdated, A.UpdatedBy, A.DateStart, 
                      A.DateEnd, A.Deleted, B.ID A, B.Bldg3d, B.MAP_NO, B.BuildingName, B.BuildingNameShort, B.BuildingNameAlias
HAVING      MAX(L.TDIR_DateStart) > Getdate()
ORDER BY P.TPERSON_Name

Select allOpen in new window

In your grouping there is an extra character (A)  B.ID A

  A.DateEnd, A.Deleted, B.ID A, B.Bl...

should be

  A.DateEnd, A.Deleted, B.ID, B.Bl...

ok the query executed but there is no data

I have told at the very beginning that you might not see any data.. because of your where Clause.. You might want to change it to "<" from ">" ..but that will result in all data.. Revise your condition

WHERE MAX(L.TDIR_DateStart) > Getdate()

are looking for today's data.. The change it to
WHERE Max(L.TDIR_DateStart) >= Convert(Varchar,Getdate(),112)

so with MAX date i can not pull the highest date from the database i have a few records trhat are the same but the dates are dif-

i dint get you.. b/w I am sorry it should be HAVING MAX(..... instead of WHERE MAX(....

ok i see the data that i am dealing with is in bad shap i will be back with you guys shortly.

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.