www.un.org/spanish/ga/president
付款来自我们的供应商,这些供应商向账户付款,而根据这些账户,再付款多少。
Customers Table (Usage is kwH)
+----+----------+------------+----------+----------+----------+-------+-------+
| ID | Customer | Account_no | Meter_no | Supplier | Active | Usage | Repid |
+----+----------+------------+----------+----------+----------+-------+-------+
| 1 | Joe | 123 | 111 | NSTAR | active | 20 | 100 |
| 2 | Joe | 123 | 222 | NSTAR | active | 30 | 100 |
| 3 | Joe | 123 | 150 | NSTAR | inactive | 60 | 100 |
| 4 | Sam | 456 | 352 | SEP | active | 50 | 100 |
| 5 | Jill | 789 | 222 | FES | active | 40 | 200 |
| 6 | Mike | 883 | 150 | ABB | inactive | 40 | 200 |
+----+----------+------------+----------+----------+----------+-------+-------+
Payment_Receive (table)
+------------+----------+-------------+-------------+
| Account_no | Supplier | Amount_paid | PaymentDate |
+------------+----------+-------------+-------------+
| 123 | NSTAR | 20 | 2011-11-01 |
| 456 | SEP | 40 | 2011-11-01 |
| 456 | SEP | -40 | 2011-11-01 |
| 456 | SEP | 40 | 2011-11-01 |
| 789 | FES | 50 | 2011-11-01 |
| 883 | ABB | 30 | 2011-11-01 |
+------------+----------+-------------+-------------+
这两张表格用于复制费。 每个账户都得到补偿,根据账户——与客户匹配。 无供应商。 我们无法控制付款——因为付款来自外部。 这造成了某些问题,因为我们不能在两个表格之间做一对一的匹配。 除此以外,我要按某些标准计算为PRID=100。 这是我希望看到的Repid=100的产出。
+------------+----------+-------------+-------------+-------------+
| Account_no | Supplier | Amount_paid | Usage | PaymentDate |
+------------+----------+-------------+-------------+-------------+
| 123 | NSTAR | 20 | 60* | 2011-11-01 |
| 456 | SEP | 40 | 50 | 2011-11-01 |
| 456 | SEP | -40 | 40 | 2011-11-01 |
| 456 | SEP | 40 | 40 | 2011-11-01 |
+------------+----------+-------------+-------------+-------------+
请注意:
- Account_no 123 exists thrice in customers table, it must show one time in rep payout
- 3 amounts were paid to account_no 456, all the three must show in the report
- *60 = Notice that there are 2 active records (and one inactive). This could be the sum of the two active. But any other value is acceptable if that makes the query easy (for greater of the two or one, not the other)
- Note that Usage column must appear in the output table, This is the column that creates problem for me. If I dont include this everything works fine.
- The point with Usage column, if I have two records for same customer having same Account_No and Supplier but different usage, that makes the two records distinct when I include usage column. Therefore distinct does not work to remove this duplicate.
报告按月计算
问题案文
create database testcase
go
use testcase
go
create table customers (
id int not null primary key identity,
customer_name varchar(25),
account_no int,
meter_no int,
supplier varchar(20),
active varchar(20),
usage int,
repid int
)
create table payments_received (
account_no int,
supplier varchar(20),
amount_paid float,
paymentdate smalldatetime
)
insert into customers values( Joe ,123, 111, NSTAR , active ,20,100)
insert into customers values( Joe ,123, 222, NSTAR , active ,30, 100)
insert into customers values( Joe ,123, 150, NSTAR , inactive ,60,100)
insert into customers values( Sam ,456, 352, SEP , active ,40,100)
insert into customers values( Jill ,789, 222, FES , active ,40,200)
insert into customers values( Mike ,883, 150, ABB , inactive ,40,200)
select * from customers
insert into payments_received values(123, NSTAR ,20, 2011-11-01 )
insert into payments_received values(456, SEP ,40, 2011-11-01 )
insert into payments_received values(456, SEP ,-40, 2011-11-01 )
insert into payments_received values(456, SEP ,40, 2011-11-01 )
insert into payments_received values(789, FES ,50, 2011-11-01 )
insert into payments_received values(883, ABB ,30, 2011-11-01 )
select * from payments_received