SQL to retrieve field based on sum calcualtion

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 2005 Forums

Transact-SQL (2005)

SQL to retrieve field based on sum calcualtion

Author

Topic

cgyq
Starting Member

7 Posts

Posted - 2009-11-23 : 17:27:08

I need to have a join from table A and Table B with output fields: budgetnbr, grantdocnumber, drawdate, drawamount, drawreceived.

Table A:

GrantDocNumber DrawDate DrawAmount DrawReceived
08REY018585Z 8/12/2009 1,583.00 8/13/2009
08REY018585Z 9/9/2009 9,122.00 9/10/2009
08REY018585Z 10/23/2009 7,364.00 10/29/2009
08REY018585Z 11/9/2009 2,776.00 11/10/2009

Table B:
TranpostingDate budgetnbr Expenses accountcode Sum
8/5/2009 203066 $0.80 70400
8/5/2009 203066 $33.64 70500
8/5/2009 203066 $9.61 70800
8/5/2009 203066 $18.42 71000
8/5/2009 203066 $800.98 18007
8/5/2009 203066 $37.65 70700
8/5/2009 203066 $0.80 70300
8/5/2009 203066 $3.21 70200
8/5/2009 203066 $678.83 259900 1,583.94
8/20/2009 203066 $2.18 70300
8/20/2009 203066 $8.73 70200
8/20/2009 203066 $1,849.22 259900
8/20/2009 203066 $2.18 70400
8/20/2009 203066 $91.64 70500
8/20/2009 203066 $26.19 70800
8/20/2009 203066 $50.19 71000
8/20/2009 203066 $102.54 70700
8/20/2009 203066 $2,181.98 18007
9/4/2009 203066 $2,059.91 259900
9/4/2009 203066 $2,430.56 18007
9/4/2009 203066 $2.44 70400
9/4/2009 203066 $2.44 70300
9/4/2009 203066 $55.90 71000
9/4/2009 203066 $9.72 70200
9/4/2009 203066 $102.08 70500
9/4/2009 203066 $29.16 70800
9/4/2009 203066 $114.24 70700 9,121.30
9/22/2009 203066 $26.52 70800
9/22/2009 203066 $2,209.60 18007
9/22/2009 203066 $103.86 70700
9/22/2009 203066 $2.20 70400
9/22/2009 203066 $8.84 70200
9/22/2009 203066 $1,872.63 259900
9/22/2009 203066 $92.80 70500
9/22/2009 203066 $2.20 70300
9/22/2009 203066 $50.82 71000
10/21/2009 203334 $18.25 70800
10/21/2009 203334 $1.22 70300
10/21/2009 203334 $80.27 71000
10/21/2009 203334 $1,215.28 18007
10/21/2009 203334 $6.08 70200
10/21/2009 203334 $72.25 70700
10/21/2009 203334 $1,283.45 259900
10/21/2009 203334 $244.40 16001
10/21/2009 203334 $0.49 70100
10/21/2009 203334 $1.46 70400
10/21/2009 203334 $71.57 70500 7,364.19
11/5/2009 203334 $1,104.80 18007
11/5/2009 203334 $77.72 71000
11/5/2009 203334 $5.64 70200
11/5/2009 203334 $66.93 70500
11/5/2009 203334 $1,189.82 259900
11/5/2009 203334 $16.93 70800
11/5/2009 203334 $0.49 70100
11/5/2009 203334 $244.40 16001
11/5/2009 203334 $67.06 70700
11/5/2009 203334 $1.11 70300
11/5/2009 203334 $1.35 70400 2,776.25

To figure out the budgetnbr for each drawamount, we need to first see the transpostingthat is < drawdate and the sum(expense) <=sum(drawamount); if the returned budgetnbr becomes two or more with this condition, then the simply split the first part to one budgetnbr and the rest to another budgetnbr.

Here is the result should like:

Budgetnbr GrantDocNumber DrawDate DrawAmount
203066 08REY018585Z 8/12/2009 1,583.00
203066 08REY018585Z 9/9/2009 9,122.00
203066 08REY018585Z 10/23/2009 4,369.47
203334 08REY018585Z 10/23/2009 2,994.72 203066 08REY018585Z 11/9/2009 2,776.00

Can someone help me to come up the query for this?

Thanks a lot in advance!!

cgyq
Starting Member

7 Posts

Posted - 2009-11-23 : 17:51:26

woops, typo:

Here is the result should like:

Budgetnbr GrantDocNumber DrawDate DrawAmount
203066 08REY018585Z 8/12/2009 1,583.00
203066 08REY018585Z 9/9/2009 9,122.00
203066 08REY018585Z 10/23/2009 4,369.47
203334 08REY018585Z 10/23/2009 2994.72
203334 08REY018585Z 11/9/2009 2,776.00

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources