Simple Question - SQL Server Forums

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

Transact-SQL (2000)

Simple Question

Author

Topic

Da_Retina
Posting Yak Master

109 Posts

Posted - 2002-02-03 : 08:25:03

Slaute..
here is a simple query!
I have a shareholders table that has the following colmuns:
ShareholderId,BeginingBalance,BlockedBlanace,AcutalBalance
and another table transactions that has the following columns:
TID,BuyerId,SellerId,TransDate
I query the tables for the balance for each shareholder at a CERTAIN date!..for example : 12/ 12/ 2000
That is I have to Add the buys and subtract the sells for each shareholder's BEGINNING BLANACE!!..
Salute

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-03 : 09:18:01

select sh.ShareholderId, BeginingBalance - sum(b.amount) + sum(s.amount)
from shareholders sh
join transactions b on b.BuyerId = sh.ShareholderId
join transactions s on s.SellerId = sh.ShareholderId
where b.TransDate <= @date
and s.TransDate <= @date
group by sh.ShareholderId

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Da_Retina
Posting Yak Master

109 Posts

Posted - 2002-02-03 : 09:28:32

Thanks Alot..

Nazim
A custom title

1408 Posts

Posted - 2002-02-03 : 10:14:05

thought this should give a error indicating
"Column 'BeginingBalance' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause".

quote:

select sh.ShareholderId, BeginingBalance - sum(b.amount) + sum(s.amount)
from shareholders sh
join transactions b on b.BuyerId = sh.ShareholderId
join transactions s on s.SellerId = sh.ShareholderId
where b.TransDate <= @date
and s.TransDate <= @date
group by sh.ShareholderId

How About this one

select sh.ShareholderId, BeginingBalance - b.BAmt + s.SAmt from shareholders sh
join ( select buyerid,sum(b.amount) as BAmt from transactions where transdate<=@date) b on
b.BuyerId = sh.ShareholderId
join ( select sellerid,sum(b.amount) as SAmt from transactions where transdate<=@date) s on s.SellerId = sh.ShareholderId

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

Edited by - Nazim on 02/03/2002 10:23:01

Da_Retina
Posting Yak Master

109 Posts

Posted - 2002-02-03 : 10:26:57

Ooops..
Sorry it didnt work!..
and yes Nazim..that gave an error..I used MAX it worked fine..but it is not summing correctly..and it only retrieves 70 records while I have 615 shareholders in my sh table!!!...
I thought it worked!!!

Nazim
A custom title

1408 Posts

Posted - 2002-02-03 : 10:48:06

Did you tried the query i gave u . i think the join should be changed to left

select sh.ShareholderId, BeginingBalance - isnull(b.BAmt,0) + isnull(s.SAmt,0) from shareholders sh
left join ( select buyerid,sum(b.amount) as BAmt from transactions where transdate<=@date) b on
b.BuyerId = sh.ShareholderId
left join ( select sellerid,sum(b.amount) as SAmt from transactions where transdate<=@date) s on s.SellerId = sh.ShareholderId

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-03 : 16:22:49

select sh.ShareholderId, BeginingBalance - coalesce(sum(b.amount),0) + coalesce(sum(s.amount),0)
from shareholders sh
left outer join transactions b on b.BuyerId = sh.ShareholderId and b.TransDate <= @date
left outer join transactions s on s.SellerId = sh.ShareholderId and s.TransDate <= @date
group by sh.ShareholderId, sh.BeginingBalance

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Da_Retina
Posting Yak Master

109 Posts

Posted - 2002-02-04 : 02:02:42

Salute..
Thanks for the effort guys..but my problem is still there!!!..
Nazim..ur query didnt work..it gave the following error:
Server: Msg 107, Level 16, State 2, Line 6
The column prefix 'b' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 6
The column prefix 'b' does not match with a table name or alias name used in the query.
NR: Ur query ran fine...it returned the whole number of shareholders but it summed correctly sometimes and didnt not other times!..I cant see the logic how the erronous summing is taking place!..I will give u an example :
a shareholder has an actual balance of 200 shares..
the shareholder BOUGHT 200 shares in 1996 and SOLD 200 shares in 1997 and BOUGHT 200 shares in 2001..ur query is returning 0 for this shareholder when I make the date '01/01/2002' or make the year 2001,2000,1999,1998 BUT WHEN I MAKE THE YEAR 1997 it returns 200!!!
what could be wrong in ur query NR?..by the way!..the transactions table contains a DELETED(bit) column..we FLAG deleted transactions and dont delete them!..BUT the deleted trasactions have nothing to do with that shareholder I am telling U about!!!..
NR and Nazim...Thanks ALOTTTT!>.I wish u continue to help!
Salute...

Nazim
A custom title

1408 Posts

Posted - 2002-02-04 : 02:30:08

Are you sure Retina you tried this same query. i think you missed aliasing the first query with b.

select sh.ShareholderId, BeginingBalance - b.BAmt + s.SAmt from shareholders sh
join ( select buyerid,sum(b.amount) as BAmt from transactions where transdate<=@date ) b on
b.BuyerId = sh.ShareholderId
join ( select sellerid,sum(b.amount) as SAmt from transactions where transdate<=@date ) s on s.SellerId = sh.ShareholderId

nr's query looks pretty fine to me. can you clarify about your beginingbalance column like does it changes every year or in the case you have mentioned what is ur Beginningbalance

quote:

I will give u an example :
a shareholder has an actual balance of 200 shares..
the shareholder BOUGHT 200 shares in 1996 and SOLD 200 shares in 1997 and BOUGHT 200 shares in 2001..ur query is returning 0 for this shareholder when I make the date '01/01/2002' or make the year 2001,2000,1999,1998 BUT WHEN I MAKE THE YEAR 1997 it returns 200!!!
what could be wrong in ur query NR?..by the way!..the transactions table contains a DELETED(bit) column..we FLAG deleted transactions and dont delete them!..BUT the deleted trasactions have nothing to do with that shareholder I am telling U about!!!..

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

Edited by - Nazim on 02/04/2002 02:31:14

Da_Retina
Posting Yak Master

109 Posts

Posted - 2002-02-05 : 01:14:15

Salute..
THANKS TO ALL OF U!!!!!...
I am sorry to say that this also didnt work!!..i think that the error was in the join as it retrieved non desired rows!!!..
The solution that I came up with and worked was the following:
SELECT SH.ShareholderID,Max(BeginningBalance) as Beginningbalance,
Max(ActualBalance) as ActualBalance,
Max(BeginningBalance)+
SUM(CASE WHEN TR.BuyerId=SH.ShareholderId THEN ISNULL(TR.SharesNo,0) ELSE 0 END)-
SUM(CASE WHEN TR.SellerId=SH.ShareholderId THEN ISNULL(TR.SharesNo,0) ELSE 0 END) as SharesUp2Date

FROM SRH_Shareholders SH LEFT OUTER JOIN SRH_Transactions TR ON
(SH.ShareholderId = TR.BuyerId OR SH.ShareholderId = TR.SellerId) AND TR.TransDate <= @TransDate
AND TR.Deleted = 0
GROUP BY ShareholderId HAVING SH.ShareholderId BETWEEN @FromShareholder AND @ToShareholder
salute..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources