SELECT statement. Comparing dates and NULLs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SELECT statement. Comparing dates and NULLs

Author

Topic

mark1504
Posting Yak Master

103 Posts

Posted - 2004-03-23 : 02:49:22

My requirement is simple.

My app has a users' notice board (not as pretty as this one!) and I want to alert the user if there have been posts since he last logged on.

Slightly modified for clarity here, I have:

SELECT CAST((SELECT TOP 1 PostDate FROM Notices WHERE CompanyID=1234 ORDER BY Postdate DESC)-ISNULL(S.LastLogin,0) AS Money) FROM Staff AS S WHERE S.UserID=23456

This will return a positive value if the LastNotice > LastLogin but will Return NULL if there are no notices, whereas I need it to return 0.

I've handled the NULL in the LastLogin (i.e. the user is new) but how do I cover the NULL produced if the company doesn't have any notices?
Is there a simpler way of comparing these dates? I guess the situation is a common one.

Thanks in anticipation
Mark

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2004-03-23 : 03:18:02

You already used the isnull() function once, wrap another is null around your first cast.

Just out of curiousity, you are subtracting one date from another (or zero from a date depending) why do you then cast the result as money?

Justin

"I want to thank you. You could've given us help, but you've given us so much more."

mark1504
Posting Yak Master

103 Posts

Posted - 2004-03-24 : 08:45:38

OK I've now modified it to...

SELECT CAST(ISNULL((SELECT TOP 1 PostDate FROM Notices WHERE CompanyID=1234 ORDER BY Postdate DESC),0)-ISNULL(S.LastLogin,0) AS Money) FROM Staff AS S WHERE S.UserID=23456

It works but just seems a hell of the busy command to return something so basic. But SQL is like that sometimes.

Maybe I could update a last post field in another table? But I'm averse to duplicated data.

BTW, The money cast was because I need to return a non-integer value to the app.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-24 : 09:12:56

i am very confused. do you want to see if there exists a "Notice" with a date greater than the last time the user's logged in ?


select N.PostDate
From notices N
Where 
  CompanyID= 1234
  PostDate > = (select LastLoginIn from Staff where UserID = 23456)

if that returns any rows, you have something out there.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources