[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

adding a max date to a sql statement

Posted on 2010-08-12
42
Medium Priority
?
266 Views
Last Modified: 2012-06-21
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
Comment
Question by:Chris Jones
  • 19
  • 17
  • 3
  • +2
41 Comments
 
LVL 7

Expert Comment

by:bouscal
ID: 33420811
You'll get errors trying to MAX(NULL)

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

Expert Comment

by:vdr1620
ID: 33420822
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
 
LVL 1

Author Comment

by:Chris Jones
ID: 33420824
oh sorry i did not add what i wanyted to max, i would like to max L.TDIR_StartDate is never null
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33420829
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
 
LVL 1

Author Comment

by:Chris Jones
ID: 33420845
can you explain more on the line
HAVING MAX(Date)  Condition

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

Expert Comment

by:vdr1620
ID: 33420988
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 33421011
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
 
LVL 1

Author Comment

by:Chris Jones
ID: 33421059
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 33421192
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
 
LVL 7

Expert Comment

by:bouscal
ID: 33421225
Change HAVING to AND
HAVING is used in place of a WHERE clause
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 33421246
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
 
LVL 1

Author Comment

by:Chris Jones
ID: 33421622
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 33421900
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
 
LVL 1

Author Comment

by:Chris Jones
ID: 33423418
its still not working for me any other ideals?
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33423452
Can your post your table Structures with Sample data ?
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 33423462
the table structure has 4 joins its kinda hard
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 33423470
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 33423662
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
 
LVL 1

Author Comment

by:Chris Jones
ID: 33426792
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 33429447
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
 
LVL 1

Author Comment

by:Chris Jones
ID: 33444974
yeah i am still here i graduated this weekend so i was very very busy.
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 33445326
you had the correct field name but now i dont have the rest of nyt data from TPERSON
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33445364
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
 
LVL 1

Author Comment

by:Chris Jones
ID: 33445387
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 33445624
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
 
LVL 1

Author Comment

by:Chris Jones
ID: 33445680
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 33445711
Well, you need to post the error..
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 33445732
sorry the erorr is attached below
sql-phone-error.png
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33445765
In the Group by Clause change S.PERCENT to S.[Percent]
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 33445809
new error incorrect syntax near A. i think it may be a jeyword TRUNK.
sql-phone-error.png
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33445859
TRUNK is not a keyword in SQL.. Did you change the S.percent to S.[Percent] in the GROUP BY clause ?
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 33446166
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
 
LVL 7

Expert Comment

by:bouscal
ID: 33446357
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
 
LVL 1

Author Comment

by:Chris Jones
ID: 33446384
ok the query executed but there is no data
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33446440
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 33446455
are looking for today's data.. The change it to
WHERE Max(L.TDIR_DateStart) >= Convert(Varchar,Getdate(),112)
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 33446483
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 33446575
i dint get you.. b/w I am sorry it should be HAVING MAX(..... instead of WHERE MAX(....
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 2000 total points
ID: 33446732
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
 
LVL 1

Author Comment

by:Chris Jones
ID: 33446800
ok i see the data that i am dealing with is in bad shap i will be back with you guys shortly.
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 36715359
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question