Hi Everyone,
I have a query that returns list of accounts with their tranasaction
amount, date, and a running total for the balance. It looks like this:
SELECT acount,transDate,Amount,(SELECT SUM(AMOUNT) FROM transactions B
where B.transDate<=A.transDate and B.Account=A.Account) as Balance from
transactions A
It produces something like the following:
Account trnasDate Amount Balance
-- -- -- --
aaaaaa 01/01/2005 10 10
aaaaaa 01/01/2005 20 30 <--
bbbbbb 01/01/2005 50 50
bbbbbb 01/01/2005 -30 20 <--
I need to get the ending balance of each account for each day. So for
account 'aaaaaa' I need to get 30 and 20 for account 'bbbbbb'. In
Microsoft Access I used to use LAST(Column) method. But I do not know
now how to do it in SQL Server. For example in Access I would run the
following queery against the above query and it will do it:
SELECT transDate, LAST(Balance) from aboveQuery group by
account,transDate order by account,transDate;
Thanks
*** Sent via Developersdex http://www.examnotes.net ***Select Account, TransDate
, Sum(Amount) As DailyTotal
, Sum(Balance) As DailyBalance
From Transactions As T
Group By T.Account, T.TransDate
If you wanted the balances for a specific range of dates, simply add a Where
clause like so:
Select Account, TransDate
, Sum(Amount) As DailyTotal
, Sum(Balance) As DailyBalance
From Transactions As T
Where T.TransDate >= '20050101' And T.TransDate <= '20050131'
Group By T.Account, T.TransDate
HTH
Thomas|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Something like this:
SELECT C.Account, C.transDate, C.Balance
FROM (
SELECT A.Account, A.transDate, A.Amount,
(SELECT SUM(B.Amount)
FROM transactions B
WHERE B.transDate <= A.transDate
AND B.Account=A.Account) As Balance
FROM transactions A ) As C
WHERE C.transDate = (SELECT MAX(D.transDate)
FROM transactions D
WHERE D.Account=C.Account)
ORDER BY C.Account, C.transDate
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQmmkc4echKqOuFEgEQLuOgCgubKOpyICO2eW
tqXffbr7NZxG9cIAoPCu
pmxZbB9udscUSv55WJgi+qbN
=qdg0
--END PGP SIGNATURE--
Hussain Al-Dhaheri wrote:
> Hi Everyone,
> I have a query that returns list of accounts with their tranasaction
> amount, date, and a running total for the balance. It looks like this:
> SELECT acount,transDate,Amount,(SELECT SUM(AMOUNT) FROM transactions B
> where B.transDate<=A.transDate and B.Account=A.Account) as Balance from
> transactions A
> It produces something like the following:
> Account trnasDate Amount Balance
> -- -- -- --
> aaaaaa 01/01/2005 10 10
> aaaaaa 01/01/2005 20 30 <--
> bbbbbb 01/01/2005 50 50
> bbbbbb 01/01/2005 -30 20 <--
> I need to get the ending balance of each account for each day. So for
> account 'aaaaaa' I need to get 30 and 20 for account 'bbbbbb'. In
> Microsoft Access I used to use LAST(Column) method. But I do not know
> now how to do it in SQL Server. For example in Access I would run the
> following queery against the above query and it will do it:
> SELECT transDate, LAST(Balance) from aboveQuery group by
> account,transDate order by account,transDate;|||Thank you for the reply. The first query is a view which is used by so
many other queries in the system. And really there is no problem with it
and doing what it is supposed to do. My problem is with the second query
in which I can not choose the last record of records with the same date.
I tried MAX(transDate), but since the date are the same ('2005/01/01')
is returns the first occurance.
*** Sent via Developersdex http://www.examnotes.net ***|||Thank you for the reply. The first query is a view which is used by so
many other queries in the system. And really there is no problem with it
and doing what it is supposed to do. My problem is with the second query
in which I can not choose the last record of records with the same date.
I tried MAX(transDate), but since the date are the same ('2005/01/01')
is returns the first occurance.
*** Sent via Developersdex http://www.examnotes.net ***|||On Fri, 22 Apr 2005 21:18:36 -0700, Hussain Al-Dhaheri wrote:
>Thank you for the reply. The first query is a view which is used by so
>many other queries in the system. And really there is no problem with it
>and doing what it is supposed to do. My problem is with the second query
>in which I can not choose the last record of records with the same date.
>I tried MAX(transDate), but since the date are the same ('2005/01/01')
>is returns the first occurance.
Hi Hussain,
IN a relational database, there is no such thing as a "first" or "last"
row. A table is, by definition, an UNordered collection or rows. The
terms "first" and "last" can only have meaning in the context of an
ordering imposed on the rows - and that ordering can onlyt be based on
data that is stored in the table.
If the transDate column includes time as well as date, then you can use
the query posted by MGFoster.
If you have other data in your tables that can determine which of the
rows with the same transDate is the "last", then you can probably use a
variation on MGFoster's code - but I can't tell for sure without first
knowing WHAT data in your tables can be used to determine which row is
"last", and HOW to determine it.
If there is no way to determine "last" row using only the data in your
table, than there is no way to return the information you need. You'll
have to change the table design, to include the information needed to
identify the "last" row. And then, you'll have to manually repair the
rows already in the database that lack this information.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I agree with you that RD does not provide any mechanizem for moving
sequentially through records without any specific information(key). The
first query I mentioned is a VIEW that I can not change. I want to write
another query which gets the last 'balance' of each account for each
day. None of the supplied queries do the job. The transaction date
includes time to the millisecond. My problem is when I want to group by
transaction date I have to include 'balance' as an aggregate field which
I can not. FIRST in Access allows me to get the value of 'balance'
without beging aggregated. So, is there a way to get 'balance' with
SUM/AVG/MAX/so on?
*** Sent via Developersdex http://www.examnotes.net ***|||On Sun, 24 Apr 2005 00:41:30 -0700, Hussain Al-Dhaheri wrote:
>I agree with you that RD does not provide any mechanizem for moving
>sequentially through records without any specific information(key). The
>first query I mentioned is a VIEW that I can not change. I want to write
>another query which gets the last 'balance' of each account for each
>day. None of the supplied queries do the job. The transaction date
>includes time to the millisecond. My problem is when I want to group by
>transaction date I have to include 'balance' as an aggregate field which
>I can not. FIRST in Access allows me to get the value of 'balance'
>without beging aggregated. So, is there a way to get 'balance' with
>SUM/AVG/MAX/so on?
Hi Hussain,
If the time portion is included in the view as well, then your problem
is equivalent to the problem of extracting the total amount and closing
balance from the following table:
CREATE TABLE Trans
(Account char(6) NOT NULL,
TransDate datetime NOT NULL,
Amount decimal (10,2) NOT NULL,
Balance decimal (10,2) NOT NULL,
PRIMARY KEY (Account, TransDate)
)
go
INSERT INTO Trans (Account, TransDate, Amount, Balance)
SELECT 'aaaaaa', '2005-01-01T10:00:00.000', 10, 10 UNION ALL
SELECT 'aaaaaa', '2005-01-01T20:00:00.000', 20, 30 UNION ALL
SELECT 'bbbbbb', '2005-01-01T13:00:00.000', 50, 50 UNION ALL
SELECT 'bbbbbb', '2005-01-01T14:00:00.000',-30, 20 UNION ALL
SELECT 'bbbbbb', '2005-01-02T12:00:00.000',-40,-20
-- Added the last row to test correct result for each date
go
I think the following query will return the results you're after:
SELECT a.Account, a.TransDay,
(SELECT SUM(Amount)
FROM Trans AS b
WHERE b.Account = a.Account
AND b.TransDate >= a.TransDay
AND b.TransDate < DATEADD(day, 1, a.TransDay)) AS Amount,
a.Balance
FROM (SELECT c.Account, c.Balance,
DATEADD(day,
DATEDIFF(day, '20000101', c.TransDate),
'20000101') AS TransDay
FROM Trans AS c
WHERE c.TransDate = (SELECT MAX(d.TransDate)
FROM Trans AS d
WHERE d.Account = c.Account
AND DATEDIFF(day,
c.TransDate,
d.TransDate) = 0)) AS a
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank all for the help you provided. I used your inputs and could at
last get it to work.
*** Sent via Developersdex http://www.examnotes.net ***
