• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

adding a max date to a sql statement

Hello

i am trying to add a max date  onto the current sql statement and i am not sure where to add it i keep geting errors everytime i add it oin the where clause and before the where clause.


here is my sql statement
NOTE: i am using vb.net to run the code in a program
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  ORDER BY P.TPERSON_Name"

Open in new window

0
Chris Jones
Asked:
Chris Jones
  • 19
  • 17
  • 3
  • +2
1 Solution
 
TimBusiness Systems AnalystCommented:
You'll get errors trying to MAX(NULL)

Your final where clause is S.dateend IS NULL AND A.dateend IS NULL
0
 
vdr1620Commented:
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
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
oh sorry i did not add what i wanyted to max, i would like to max L.TDIR_StartDate is never null
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Patrick MatthewsCommented:
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.
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
can you explain more on the line
HAVING MAX(Date)  Condition

what is condition
EXMPALE
HAVING MAX(Date)  L.TDIR_StartDate   ?
0
 
vdr1620Commented:
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)
0
 
vdr1620Commented:
since its a date value it should be enclosed in Single Quotes

Like HAVING MAX(L.TDIR_StartDate) > '2010-01-02 00:00:00'
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
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"

Open in new window

0
 
vdr1620Commented:
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
0
 
TimBusiness Systems AnalystCommented:
Change HAVING to AND
HAVING is used in place of a WHERE clause
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
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.
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
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"

Open in new window

0
 
vdr1620Commented:
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
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
its still not working for me any other ideals?
0
 
vdr1620Commented:
Can your post your table Structures with Sample data ?
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
the table structure has 4 joins its kinda hard
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
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

Open in new window

0
 
vdr1620Commented:
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"

Open in new window

0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
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
0
 
vdr1620Commented:
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 ?
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
yeah i am still here i graduated this weekend so i was very very busy.
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
you had the correct field name but now i dont have the rest of nyt data from TPERSON
0
 
vdr1620Commented:
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


0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
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
0
 
vdr1620Commented:
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"

Open in new window

0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
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

Open in new window

0
 
vdr1620Commented:
Well, you need to post the error..
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
sorry the erorr is attached below
sql-phone-error.png
0
 
vdr1620Commented:
In the Group by Clause change S.PERCENT to S.[Percent]
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
new error incorrect syntax near A. i think it may be a jeyword TRUNK.
sql-phone-error.png
0
 
vdr1620Commented:
TRUNK is not a keyword in SQL.. Did you change the S.percent to S.[Percent] in the GROUP BY clause ?
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
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

Open in new window

0
 
TimBusiness Systems AnalystCommented:
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...

0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
ok the query executed but there is no data
0
 
vdr1620Commented:
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()
0
 
vdr1620Commented:
are looking for today's data.. The change it to
WHERE Max(L.TDIR_DateStart) >= Convert(Varchar,Getdate(),112)
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
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-
0
 
vdr1620Commented:
i dint get you.. b/w I am sorry it should be HAVING MAX(..... instead of WHERE MAX(....
0
 
vdr1620Commented:
If that is what you need.,you will also need to convert the left side of the operator in the same way as

 HAVING Convert(Varchar,Max(L.TDIR_DateStart),112) >= Convert(Varchar,Getdate(),112)
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
ok i see the data that i am dealing with is in bad shap i will be back with you guys shortly.
0
 
James MurrellProduct SpecialistCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 19
  • 17
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now