Solved

adding a max date to a sql statement

Posted on 2010-08-12
42
246 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
42 Comments
 
LVL 7

Expert Comment

by:bouscal
Comment Utility
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
Comment Utility
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
Comment Utility
oh sorry i did not add what i wanyted to max, i would like to max L.TDIR_StartDate is never null
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Change HAVING to AND
HAVING is used in place of a WHERE clause
0
 
LVL 1

Author Comment

by:Chris Jones
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
its still not working for me any other ideals?
0
 
LVL 16

Expert Comment

by:vdr1620
Comment Utility
Can your post your table Structures with Sample data ?
0
 
LVL 1

Author Comment

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

Author Comment

by:Chris Jones
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

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

Author Comment

by:Chris Jones
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Well, you need to post the error..
0
 
LVL 1

Author Comment

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

Expert Comment

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

Author Comment

by:Chris Jones
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
ok the query executed but there is no data
0
 
LVL 16

Expert Comment

by:vdr1620
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now