Comparing date values if they are NULL Problem? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

Comparing date values if they are NULL Problem?

Author

Topic

V-Nest
Starting Member

10 Posts

Posted - 2006-12-29 : 16:57:09

Hi
I'm using ms sql 2005.
I created a view where I retrive PriceDate's from 2 tables. However If the priceDate is NULL, I'd like to put the Last PriceDate that comes from the Query. Here is the my view so far;

SELECT TOP (100) PERCENT G.OwningPortfolioID, CI.Description, CI.SecurityID, CI.SecurityTypeID, CI.TQuantity, 
                      CI.Amount, CI.PriceDate
FROM         dbo.CurrentInvestment AS CI WITH (Nolock) INNER JOIN
                      dbo.Groups AS G WITH (Nolock) ON CI.PortfolioID = G.PortfolioID
WHERE     (CI.TQuantity > 0.000001) AND (G.OwningPortfolioID = 270)
ORDER BY CI.PortfolioID, CI.PriceDate DESC

I tried using Case function, but no luck. Any help would greatly appreciated.
Thanks.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-29 : 18:24:40

First things first... don't sort in a view... sort in the select on the view. If other's need the data sorted in a different manner, all you've done is double the time it takes to sort. You might also want to get rid of the hardcoded portfolio ID.

On the "Last" PriceDate... do you mean the MAX(PriceDate) on the whole ci table? ???

--Jeff Moden

V-Nest
Starting Member

10 Posts

Posted - 2006-12-29 : 18:48:10

Jeff; Thanks for your reply.
Yes I mean the Max(PriceDate) on the whole ci table.
Thank you.

blackX
Posting Yak Master

102 Posts

Posted - 2008-03-13 : 22:09:09

quote:
Originally posted by V-Nest

Jeff; Thanks for your reply.
Yes I mean the Max(PriceDate) on the whole ci table.
Thank you.

use the isnull function

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources