Link to home
Start Free TrialLog in
Avatar of Sheritlw
SheritlwFlag for United States of America

asked on

View all records from 1 table and last date from related 2nd table

Hi
I have a table of clients that is related to a schedule table.  I want to list all clients a long with there next schedule date.
However, there may not be a schedule, or there could be a lot of schedules, but I only want the next schedule, greater than or equal to (>=) the current date.  It is a datetime field and I only need to compare that date part.

I will be adding this to a view to bind to a gridview.

How would I do this?

Thanks
SOLUTION
Avatar of David Kroll
David Kroll
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sheritlw

ASKER

That is great thank you.
When it is null, how can I exchange the date for the text 'No Schedule'?

Thank you so much
select c.*,
(select
  case when min(s.date) is not null then min(s.date) else 'No Schedule' end
 from schedules s
 where s.clientid = c.clientid
 and convert(varchar, s.date, 101) >= convert(varchar, getdate(), 101)) as nextschedule
from clients c
Hi,

First thank you so much for your help.

I received the following error when I tried to run the view.
Error Conversion failed when converting datetime from character string

SELECT     CustomerID, UserID, ClientUserID, FName, LName, FullName, ClientAdd, ClientCity, CityID, StateID, ZipCode, CntryID, emailadd, BirthDate, Aniversary, PrimaryPhone, 
                      MobPhone, OtherPhone, Describe, DateAdded, LastServiceDate, Active, BlockScheduling, MessageForBlock,
                          (SELECT     CASE WHEN MIN(s.SchedStartDateTime) IS NOT NULL THEN MIN(s.SchedStartDateTime) ELSE 'No Schedule' END AS Expr1
                            FROM          dbo.Schedules AS s
                            WHERE      (CustomerID = c.CustomerID) AND (CONVERT(varchar, SchedStartDateTime, 101) >= CONVERT(varchar, GETDATE(), 101))) AS nextschedule
FROM         dbo.Clients AS c

Open in new window

Thanks
SELECT     CustomerID, UserID, ClientUserID, FName, LName, FullName, ClientAdd, ClientCity, CityID, StateID, ZipCode, CntryID, emailadd, BirthDate, Aniversary, PrimaryPhone, 
                      MobPhone, OtherPhone, Describe, DateAdded, LastServiceDate, Active, BlockScheduling, MessageForBlock,
                          (SELECT     CASE WHEN MIN(s.SchedStartDateTime) IS NOT NULL THEN MIN(s.SchedStartDateTime) ELSE 'No Schedule' END AS Expr1
                            FROM          dbo.Schedules AS s
                            WHERE      (CustomerID = c.CustomerID) AND ((CONVERT(varchar, SchedStartDateTime, 101) >= CONVERT(varchar, GETDATE(), 101))) OR s.SchedStartDateTime IS NULL) AS nextschedule
FROM         dbo.Clients AS c

Open in new window

I still get the same error.
Is there something I'm doing wrong?

Thanks
maybe there's a date that is incorrect.  try doing this:

select * from schedules
order by schedstartdatetime

Look at the top and bottom of the list and see if any of the schedstartdatetime are not date/time.
Everything is fine in the schedule table.  The schedstartdatetime is a datetime field.

For some reason it does not want to accept the varchar 'No Schedule' in place of the datetime field.

SELECT   CustomerID, etc,
(SELECT  CASE WHEN MIN(s.SchedStartDateTime) IS NOT NULL THEN MIN(s.SchedStartDateTime) ELSE 'No Schedule' END AS Expr1
 FROM   dbo.Schedules AS s
WHERE  (CustomerID = c.CustomerID) AND ((CONVERT(varchar, SchedStartDateTime, 101) >= CONVERT(varchar, GETDATE(), 101))) OR s.SchedStartDateTime IS NULL) AS nextschedule
FROM   dbo.Clients AS c

Open in new window


I tried to make the sql a little more readable... Any ideas?

Thanks
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I've requested that this question be closed as follows:

Accepted answer: 0 points for Sheritlw's comment #a38569085
Assisted answer: 500 points for dkrollCTN's comment #a38568791

for the following reason:

Thank you again for your help!
Please accept my last answer to award me the points.
Please accept my last answer to award me the points.
Avatar of Scott Pletcher
FYI, the code as written will give you logic errors.  mmddyyyy is not a reliable string format for comparing dates, since 11012010 is greater than 01012011, for example.

You should compare the datetime as a datetime, not as a string, not only for accuracy but also for performance (Google "sargable" for the performance side of it).


WHERE
    ...
    SchedStartDateTime > GETDATE()
    ...