Query Help, thanks!

[rong.guo]

Greetings!

Please see my data below, for each account, I would need the lastest
balance_date with the corresponding balance. Can anyone help me with
the query? Thanks a lot!

create table a
(account int
,balance_date datetime
,balance money)

insert into a values(101,'1/31/2005', 5000)
insert into a values(101,'2/28/2005', 6000)
insert into a values(102,'5/31/2005', 15000)
insert into a values(102,'6/30/2005', 10000)

Ideal Output
101 '2/28/2005' 6000
102 '6/30/2005' 10000

[SQL]

Here is one way
select a.account,a.balance_date,a.balance
from
(select account,max(balance_date) as balancedate
from a
group by account)aTemp join a on a.account =aTemp.account
and a.balance_date =aTemp.balancedate

http://sqlservercode.blogspot.com

[David Portas]

> create table a

(account int
,balance_date datetime
,balance money)

Why are all your columns nullable? What is/are the key/s of this table?
Are you aware that MONEY will give you imprecisely rounded results when
you multiply or divide?

Try:

SELECT account, balance_date, balance
FROM a AS T
WHERE balance_date =
(SELECT MAX(balance_date)
FROM a
WHERE account = T.account) ;

--
David Portas
SQL Server MVP
--

[rong.guo]

Thanks for the reply!

As I only occationally create tables (I normally use tables that are
already available), I am not sure how to set up columns with NULL
options and key. What is the best way to set up MONEY to avoid
calculation problems? I would appreciate any suggestions, thanks!

[David Portas]

Probably something like this. I'm guessing you have an Accounts table
somewhere as well.

CREATE TABLE account_balances
(account_no INTEGER NOT NULL
CONSTRAINT fk_account_balances_accounts
FOREIGN KEY REFERENCES accounts (account_no)
,balance_date DATETIME NOT NULL
,balance NUMERIC(19,4) NOT NULL,
CONSTRAINT pk_account_balances
PRIMARY KEY (account_no,balance_date)) ;

--
David Portas
SQL Server MVP
--

[Alexander Kuznetsov]

another way:

select a.account,a.balance_date,a.balance
from a
where not exists
(select 1
from a a1
where a.account =a1.account
and a.balance_date <a1.balancedate )

[rong.guo]

THANKS!

Alexander Kuznetsov wrote:

another way:

select a.account,a.balance_date,a.balance
from a
where not exists
(select 1
from a a1
where a.account =a1.account
and a.balance_date <a1.balancedate )

[Simon Hayes]

David Portas wrote:

create table a
(account int
,balance_date datetime
,balance money)

Why are all your columns nullable? What is/are the key/s of this table?
Are you aware that MONEY will give you imprecisely rounded results when
you multiply or divide?

Try:

SELECT account, balance_date, balance
FROM a AS T
WHERE balance_date =
(SELECT MAX(balance_date)
FROM a
WHERE account = T.account) ;

David,

Are you channelling Celko? :-) Whether or not the columns are NULL or
NOT NULL depends on ANSI_NULL_DFLT_ON/OFF - admittedly, they will be
NULL by default most of the time.

As for money, what data type should an amount of money be; money in
MSSQL is is essentially decimal(19,4), which is a precise data type (and
specifically recommended by BOL for cases where precision is required).
Ignoring the nonsense with currency symbols which MSSQL allows, and
forgetting about currencies like the old Turkish Lira (where dec(24,4)
or more might be needed in certain industries), why would there be any
reason to believe that results will be imprecise?

Simon

[David Portas]

> Are you channelling Celko? :-)

Nah, I'm a total pussycat!

Whether or not the columns are NULL or NOT NULL depends on
ANSI_NULL_DFLT_ON/OFF - admittedly, they will be NULL by default most of
the time.
True, but the purpose of posting DDL is to define the problem more clearly.
Rong's DDL was about as clear as mud - and not much more useful.
As for money, what data type should an amount of money be; money in MSSQL
is is essentially decimal(19,4), which is a precise data type (and
specifically recommended by BOL for cases where precision is required).
Incorrect, false and wrong! MONEY is definitively not even near equivalent
to DECIMAL(19,4). My professional advice is always that MONEY is unsuitable
for monetary amounts. See the example below for why. Now it's a fact that
DECIMAL isn't without its own issues because the coercian rules for DECIMAL
are not documented and not always well understood, but there is no excuse at
all for MONEY or SMALLMONEY.

DECLA RE
@mon1 MONEY,
@mon2 MONEY,
@mon3 MONEY,
@mon4 MONEY,
@num1 DECIMAL(19,4),
@num2 DECIMAL(19,4),
@num3 DECIMAL(19,4),
@num4 DECIMAL(19,4)

SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000

SET @mon4 = @mon1/@mon2*@mon3
SET @num4 = @num1/@num2*@num3

SELECT @mon4 AS money_result,
@num4 AS numeric_result
Result:

money_result numeric_result
--------------------- ---------------------
2949.0000 2949.8525
(1 row(s) affected)

--
David Portas
SQL Server MVP
--

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:43**********@news.bluewin.ch... David Portas wrote:

create table a
(account int
,balance_date datetime
,balance money)

Why are all your columns nullable? What is/are the key/s of this table?
Are you aware that MONEY will give you imprecisely rounded results when
you multiply or divide?

Try:

SELECT account, balance_date, balance
FROM a AS T
WHERE balance_date =
(SELECT MAX(balance_date)
FROM a
WHERE account = T.account) ;

David,

Are you channelling Celko? :-) Whether or not the columns are NULL or NOT
NULL depends on ANSI_NULL_DFLT_ON/OFF - admittedly, they will be NULL by
default most of the time.

As for money, what data type should an amount of money be; money in MSSQL
is is essentially decimal(19,4), which is a precise data type (and
specifically recommended by BOL for cases where precision is required).
Ignoring the nonsense with currency symbols which MSSQL allows, and
forgetting about currencies like the old Turkish Lira (where dec(24,4) or
more might be needed in certain industries), why would there be any reason
to believe that results will be imprecise?

Simon

[Simon Hayes]

David Portas wrote:

Are you channelling Celko? :-)

Nah, I'm a total pussycat!

Hmm, no comment... :-)

Whether or not the columns are NULL or NOT NULL depends on
ANSI_NULL_DFLT_ON/OFF - admittedly, they will be NULL by default most of
the time.

True, but the purpose of posting DDL is to define the problem more clearly.
Rong's DDL was about as clear as mud - and not much more useful.

Agreed - I was certainly splitting hairs there. If you want other people
to reproduce your results, then of course you need a script which works
the same way everywhere. Or at least as far as possible; if you really
want reproducible DDL then you need the COLLATE clause with every
character data type, and even then if your client doesn't behave the
same way as the OP's, you may find it tough to compare (in addition to
the numerous issues which I didn't even think of). Sounds like I'm
splitting hairs again...

As for money, what data type should an amount of money be; money in MSSQL
is is essentially decimal(19,4), which is a precise data type (and
specifically recommended by BOL for cases where precision is required).

Incorrect, false and wrong! MONEY is definitively not even near equivalent
to DECIMAL(19,4). My professional advice is always that MONEY is unsuitable
for monetary amounts. See the example below for why. Now it's a fact that
DECIMAL isn't without its own issues because the coercian rules for DECIMAL
are not documented and not always well understood, but there is no excuse at
all for MONEY or SMALLMONEY.

DECLA RE
@mon1 MONEY,
@mon2 MONEY,
@mon3 MONEY,
@mon4 MONEY,
@num1 DECIMAL(19,4),
@num2 DECIMAL(19,4),
@num3 DECIMAL(19,4),
@num4 DECIMAL(19,4)

SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000

SET @mon4 = @mon1/@mon2*@mon3
SET @num4 = @num1/@num2*@num3

SELECT @mon4 AS money_result,
@num4 AS numeric_result
Result:

money_result numeric_result
--------------------- ---------------------
2949.0000 2949.8525
(1 row(s) affected)

OK, this is where it gets interesting. My experience is that you do the
"right thing", so you go to the accounting department and ask for their
data storage and rounding rules, based on GAAP and EU regulations. The
answer is normally a resounding "huh?". So you essentially cover your
backside by choosing 'money', on the grounds that MS recommended it (and
you document the fact that Accounting had no objections). If and when
you have rounding errors, then you blame the accountants, quote BOL and
give a theoretical example, as you and I have just done between us.

This may seem excessively cynical, but I have to admit that I have no
experience at all of a company where anyone cares about a difference as
small as the one you've shown. Which is not to say that it's not
important to some people, simply that it's never been important to me or
my clients, in the context of the applications that I've worked on.

I suspect that this is a long-winded way of saying that you're right and
I'm wrong, but this is one issue in data modelling where I've seen very
little solid information. You've said that 'money' isn't appropriate for
monetary amounts, and it's not equivalent to dec(19,4); fair enough, but
what is the appropriate data type? Another good example is Celko's
mystical natural primary key for human beings, which he seems to think
is an SSN, despite the fact that most of the world's never heard of one...

Simon

[David Portas]

Use DECIMAL / NUMERIC for monetary amounts. Why would you need anything
else?

I haven't seen a recommendation from Microsoft to use MONEY for
monetary amounts. Maybe you could post a reference if you know of one
because I'd like to send some feedback telling them to change it. What
they DO say is "Monetary data types for representing monetary or
currency values", which is a statement not a recommendation.

Knowing the rounding issues I think I'd have to be either careless,
malicious or under written instructions from my boss to use MONEY in
permanent tables in a production database. It does have some potential
uses for formatting and converting values if you need to do such things
in the database.

--
David Portas
SQL Server MVP
--