Help me build this query. - 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)

Help me build this query.

Author

Topic

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-12-09 : 05:09:52

[code]
[tblCharges]
Date_Charged Smalldatetime
Amount_Charged Smallmoney

[tblPayments]
Date_paid Smalldatetime
Amount smallmoney

sample data
[tblcharges]
date_charged amount_charged
10/11/06 720
11/11/06 360
12/11/06 360
01/11/07 360
02/11/07 360
03/11/07 500

[tblpayments]
date_paid amount
11/09/06 720
11/25/06 300
01/09/06 420
11/20/07 360
02/11/07 100
03/11/07 260
03/05/07 360

declare @tblCharges table (Date_Charged Smalldatetime,Amount_Charged Smallmoney)
declare @tblPayments table (Date_paid Smalldatetime,Amount smallmoney)

insert into @tblCharges
select '10/11/06', 720
union all select '11/11/06', 360
union all select '12/11/06', 360
union all select '01/11/07', 360
union all select '02/11/07', 360
union all select '03/11/07', 500

insert into @tblPayments
select '11/09/06', 720
union all select '11/25/06', 300
union all select '01/09/06', 420
union all select '11/20/07', 360
union all select '02/11/07', 100
union all select '03/11/07', 260
union all select '03/05/07', 360

Can you help me build the query that will result like the sample below. Thanks

Date Charged Amount Charged Date Paid Amount
---------------------------------------------------------
---------------------------------------------------------
10/11/06 720 11/09/06 720
---------------------------------------------------------
11/11/06 360 11/25/06 300
01/09/06 60
---------------------------------------------------------
12/11/06 360 01/09/06 360
---------------------------------------------------------
01/11/07 360 01/20/07 360
---------------------------------------------------------
02/11/07 360 02/11/07 100
03/11/07 260
---------------------------------------------------------
03/11/07 500 03/05/07 360
---------------------------------------------------------
[/code]

TCC

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-09 : 10:15:11

It seems you need another column, something like AccountID or BillID perhaps.

otherwise how will you know which payments apply to which charges? without this association, you could apply the payment from one customer to the bill of another, which would not be fair to the first customer (I wouldn't mind being the second one however

)

http://www.elsasoft.org

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-12-09 : 19:19:32

Oh this is a tricky one ... Ill give it a try (unless Peso beats me to it)

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-12-10 : 06:36:37

Are you sure you payment dates are right?

select '11/09/06', 720
union all select '11/25/06', 300
union all select '01/09/06', 420
union all select '11/20/07', 360
union all select '02/11/07', 100
union all select '03/11/07', 260
union all select '03/05/07', 360

I am going to assume:

insert into @tblPayments
select '20061109', 720 union all
select '20061125', 300 union all
select '20070109', 420 union all
select '20070120', 360 union all
select '20070211', 100 union all
select '20070311', 260 union all
select '20070405', 360

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-12-11 : 00:38:49

Hello guys I had made corrections with my sample data.

declare @tblCharges table (charges_key int IDENTITY (1, 1) ,Date_Charged Smalldatetime,Amount_Charged Smallmoney)
--charges_key is an identity_key and also a primary key

declare @tblPayments table (Or_no int, Date_paid Smalldatetime,Amount smallmoney)
-- or_no = Official Reciept No. as Primary Key

insert into @tblCharges
select '10/11/06', 720
union all select '11/11/06', 360
union all select '12/11/06', 360
union all select '01/11/07', 360
union all select '02/11/07', 360
union all select '03/11/07', 500

insert into @tblPayments
select 1000,'11/09/06', 720
union all select 1001,'11/25/06', 300
union all select 1002,'12/09/06', 420
union all select 1003,'01/20/07', 360
union all select 1004,'02/11/07', 100
union all select 1005,'03/11/07', 260
union all select 1006,'04/05/07', 360

select * from @tblPayments
select * from @tblCharges

How can i join them so that it would produce a result like the one below.

I want to join for each [Charges] joins [Payments] that is only equal to the amount charged and Split the Payment that exceeds the charged amount. Thank you.

SUBSCRIBER'S LEDGER


Date Charged	Amount Charged	Date Paid	Amount
---------------------------------------------------------
---------------------------------------------------------
10/11/06	720		11/09/06	720
---------------------------------------------------------
11/11/06	360		11/25/06	300
				12/09/06	60
---------------------------------------------------------
12/11/06	360		12/09/06	360
---------------------------------------------------------
01/11/07	360		01/20/07	360
---------------------------------------------------------
02/11/07	360		02/11/07	100
				03/11/07	260
---------------------------------------------------------
03/11/07	500		04/05/07	360
---------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources