473,246 Members | 1,934 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,246 software developers and data experts.

Query Help, thanks!

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

Sep 29 '05 #1
10 2640
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

Sep 29 '05 #2
> 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
--

Sep 29 '05 #3
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!

Sep 29 '05 #4
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
--

Sep 29 '05 #5
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 )

Sep 29 '05 #6
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 )


Sep 29 '05 #7
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
Sep 29 '05 #8
> 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

Sep 29 '05 #9
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
Oct 4 '05 #10
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
--

Oct 5 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
4
by: sah | last post by:
I need some help with the following query: DECLARE @SRV VARCHAR(20), @date smalldatetime SET @SRV = (select @@servername) SET @date = '20040901' select Srv_Name = @SRV, DB_Name = 'DB_NAME',...
7
by: Simon Bailey | last post by:
How do you created a query in VB? I have a button on a form that signifies a certain computer in a computer suite. On clicking on this button i would like to create a query searching for all...
4
by: d.p. | last post by:
Hi all, I'm using MS Access 2003. Bare with me on this description....here's the situation: Imagine insurance, and working out premiums for different insured properties. The rates for calculating...
36
by: Liam.M | last post by:
hey guys, I have one last problem to fix, and then my database is essentially done...I would therefore very much appreciate any assistance anyone would be able to provide me with. Currently I...
5
by: darnnews | last post by:
Hi, I have been creating a database to keep track of press clippings, but I have hit a couple stumbling blocks. Any help is much appreciate. 1) Seeing if my query is done I have the...
5
by: elitecodex | last post by:
Hey everyone. I have this query select * from `TableName` where `SomeIDField` 0 I can open a mysql command prompt and execute this command with no issues. However, Im trying to issue the...
2
by: Haas C | last post by:
Hey all, I created a Query (in Design View) which asks the user for an "As Of Date" which would then display relevant data. I put in the Criteria Row of the Query Design for the Date field. Is...
10
by: aaronrm | last post by:
I have a real simple cross-tab query that I am trying to sum on as the action but I am getting the "data type mismatch criteria expression" error. About three queries up the food chain from this...
6
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.