MIN, MAX and "non-zero" functions?

I was somewhat flabbergasted to find that SQL Server doesn't appear to
include a mathematical MAX or MIN. No, I can't use them as aggrs, because its
in a sub-select. Is there some way to simulate these?
What I'm actually trying to accomplish is to make a "it is zero if it's null":
TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
I thought by using MIN(theNumber, 0) I could get close enough. Is there some
easy way to accomplish this?
Maury
Maury Markowitz wrote:
> I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's null":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there some
> easy way to accomplish this?
> Maury
Look up COALESCE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||COALESE will only return the first non-null value. Think of it as a shorthand
way of doing a CASE statement that checks each value in order for a NULL, and
returns the first value that isn't NULL.
This is not really the same as MIN, but if you want to substitue a value for
a NULL, try this:
TotalSales = ISNULL(salesq1,0) + ISNULL(salesq2,0) + ISNULL(salesq3,0) +
ISNULL(salesq4,0)
"Maury Markowitz" wrote:

> I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's null":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there some
> easy way to accomplish this?
> Maury
|||"Tracy McKibben" wrote:

> Look up COALESCE in Books Online...
Thanks! That solves that one. Now what about MIN and MAX? Do I have to roll
my own or is there some oddly named function for this too?
Maury
|||Maury Markowitz wrote:
> "Tracy McKibben" wrote:
>
> Thanks! That solves that one. Now what about MIN and MAX? Do I have to roll
> my own or is there some oddly named function for this too?
> Maury
I've always used CASE myself...
SELECT
CASE WHEN x < y THEN x ELSE y END AS MinVal,
CASE WHEN x > y THEN x ELSE y END AS MaxVal
FROM MyTable
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||INTP56" wrote:
> TotalSales = ISNULL(salesq1,0) + ISNULL(salesq2,0) + ISNULL(salesq3,0) +
> ISNULL(salesq4,0)
Ahhhhh! I never read that entry because I always though it was shorthand for
"IS NULL"! Thanks!
Maury
|||Maury Markowitz wrote:
> INTP56" wrote:
> Ahhhhh! I never read that entry because I always though it was shorthand for
> "IS NULL"! Thanks!
> Maury
ISNULL has some well-known issues. COALESCE does essentially the same
thing as ISNULL, without the issues...
http://toponewithties.blogspot.com/2...lesce-and.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:63E71F36-D5DD-4B72-8708-EB9BE24EFDC8@.microsoft.com...
>I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because
> its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's
> null":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there
> some
> easy way to accomplish this?
> Maury
MIN(COALESCE(theNumber, 0))
Most aggregate functions eliminate NULL, since NULL represents an unknown
value (the exception is COUNT(*) which does not eliminate NULLs). If NULL
is being used as a placeholder for 0 in your data you might consider
replacing it with 0.

Migration Assistant For Access Can''t Find SQL Database?

I've installed SQL 2005 Express Addition which created a default database using Windows Authentication -- MachineName/SQLEXPRES. I want to convert some access databases to sql server in the migration assistant, however, when I try to connect the migration assistant to the default database I can't find the database in the drop down box wizard? I'm perplexed? Please shed some light or point me to some articles that will help. Thanks.

It is not practical to use standard Express to migrate Access so see if you can use the one with Management Studio instead, this will give you control of all the steps. Hope this helps.

http://msdn.microsoft.com/vstudio/express/sql/download/

|||

Yes, Management Studio is the one I used. I am able to connect via Management Studio, however, the Migration Assistant can't locate it? Looking for other suggestions? Thanks.

|||

Try the eval good for 6 months, you could buy the developer for under $40 online if you have the ram this can help you move Access if all you have is tables and queries. If you have VBA you may need professional help. I have seen Access migration taking six months or more because it lets users add stuff SQL Server will not accept and it uses limited data types.

http://www.microsoft.com/sql/downloads/trial-software.mspx

|||

I'm not sure I understand what you mean by "default database" in this context. SQL Server does not create any default databases when you install it.

In my use of SSMS I find that the drop down box that show the database name does not actually show the list of databases in the server because it has not actually connected to the server yet, so it can not list the databases. You can either specify a new database to be created or specify an existing database to open once you connect, but you need to type the name of the database, it won't populate the list for you.

Regards,

Mike