Max() and Sum() - 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)

Max() and Sum()

Author

Topic

scrap
Yak Posting Veteran

57 Posts

Posted - 2006-04-24 : 11:35:06

I want to do the following request:

SELECT MAX(dt), Nbr, Client_Id FROM TABLE_A
and
SELECT SUM(Nbr) FROM TABLE_B WHERE TABLE_B.Dt = TABLE_A.Dt

Here are the tables:

TABLE_A
NRI Dt Nbr Client_Id
1 1/1/05 10 1
2 1/1/05 11 2
3 1/21/05 12 1
4 1/1/05 13 4

TABLE_B
NRI Dt Nbr Client_Id Activity_Id
1 2/13/06 11 1
2 2/14/06 22 2
3 2/15/06 33 1
4 2/15/06 44 4

I've been trying to get something for two days now.
Thank you.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-04-24 : 11:37:33

What results are you trying to return based on your sample data?

scrap
Yak Posting Veteran

57 Posts

Posted - 2006-04-24 : 11:37:47

quote:
Originally posted by scrap

I want to do the following request:

SELECT MAX(dt), Nbr, Client_Id FROM TABLE_A
and
SELECT SUM(Nbr) FROM TABLE_B WHERE TABLE_B.Dt = TABLE_A.Dt

I forgot, I want that for every Client_Id

scrap
Yak Posting Veteran

57 Posts

Posted - 2006-04-24 : 11:59:10

Ok I will start over.

I want to do the following request:

I want to select (Nbr + Tmp), Client_Id from
SELECT MAX(dt), Nbr, Client_Id FROM TABLE_A
and
SELECT ((SUM(Nbr) FROM TABLE_B WHERE TABLE_B.Dt = TABLE_A.Dt AND Activity_Id = 1) - (SUM(Nbr) FROM TABLE_B WHERE TABLE_B.Dt = TABLE_A.Dt AND Activity_Id = 2)) as Tmp

Here are the tables:

TABLE_A
NRI--Dt------Nbr--Client_Id
1....1/1/05..100..1
2....1/1/05..101..2
3....1/21/05.102..1
4....1/1/05..103..4

TABLE_B
NRI--Dt-------Nbr--Client_Id--Activity_Id
1....2/13/06..11...1..........2
2....2/14/06..22...2..........1
3....2/15/06..33...1..........1
4....2/15/06..44...4..........2

I forgot the thing about the activity.

Here is the result I am looking for:

client_id---Table_A.Nbr---Table_B.Expr1
1___________12____________124(102+33-11)
2___________11____________123(101+22)
4___________13____________69(103-44)

Thank you

scrap
Yak Posting Veteran

57 Posts

Posted - 2006-04-24 : 13:05:51

Here is the template I am going on with right now:

SELECT SUM(Nbr), Client_Id
FROM TABLE_B
where TABLE_B.Dt > (Select max(Dte) FROM TABLE_A WHERE
TABLE_A.Client_Id = TABLE_B.Client_Id)

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-04-24 : 13:43:02

SELECT Client_Id, Nbr + (SELECT SUM(Nbr * CASE WHEN Activity_Id = 2 THEN -1 ELSE 1 END) FROM Table_B B WHERE B.Client_Id = A.Client_Id)
FROM Table_A A
WHERE Dt = (SELECT MAX(Dt) FROM Table_A A1 WHERE A1.Client_Id = A.Client_Id)
ORDER BY Client_Id

scrap
Yak Posting Veteran

57 Posts

Posted - 2006-04-24 : 14:08:08

RIGHT ON

Thank you sooooooooo much!

Solution found by PSamsig

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources