Complicated 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)

Complicated Query..

Author

Topic

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-12-11 : 08:35:04

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 1001,'11/30/06', $40
union all select 1002,'12/09/06', $380
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', $500

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



[           Charges           ] [       Payments        ]
Date Charged	Amount Charged	Date Paid	Amount
---------------------------------------------------------
10/11/06	720		11/09/06	720
---------------------------------------------------------
11/11/06	360		11/25/06	300
				11/30/06	40
				12/09/06	20
---------------------------------------------------------
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	500
---------------------------------------------------------

TCC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 08:40:36

Duplicate post
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76143

Peter Larsson
Helsingborg, Sweden

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-12-12 : 00:06:09

nobody answers thats is why i posted it again. heheheh

TCC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 01:32:02

Maybe you should explain what the number 20 is doing in the payments? Due to the number is 20 dollars larger than the charges.

You must supply more information about this case if you want people to be interested in your problem.
This should give you enough hints how to solve your problem

-- prepare test data
declare @tblCharges table (charges_key int IDENTITY (1, 1) ,Date_Charged Smalldatetime,Amount_Charged 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

declare @tblPayments table (Or_no int, Date_paid Smalldatetime,Amount smallmoney)

insert into @tblPayments
select 1000,'11/09/06', 720
union all select 1001,'11/25/06', 300
union all select 1001,'11/30/06', 40
union all select 1002,'12/09/06', 380
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', 500

-- do the work
select		charge.date_charged,
		charge.amount_charged,
		pay.date_paid,
		pay.amount
from		(
			select		t1.date_charged,
					t1.amount_charged,
					(select isnull(min(t2.date_charged), '20790606') from @tblcharges t2 where t2.date_charged > t1.date_charged) qwe
			from		@tblcharges t1
		) charge
inner join	@tblpayments pay on pay.date_paid >= charge.date_charged and pay.date_paid < charge.qwe
order by	charge.date_charged,
		pay.date_paid

Peter Larsson
Helsingborg, Sweden

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-12-12 : 01:42:58

thanks a lot it worked!! :)

TCC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 01:47:27

I know.
But not as your expected output, because that is not consistent.

Peter Larsson
Helsingborg, Sweden

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-12-12 : 01:49:59

yeah.. but i think i can modify this. maybe just a little changes and this will work. :)

TCC

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-12-12 : 02:01:08

Peso, what can you advise so that i will get the desired output? do i need to add columns? what do you think? :)

TCC

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-12-12 : 04:13:22

Peso, Number 20 is a payment.

20 is taken from ===> 12/09/06, $380

lets say for example, a charge of 360 pesos is added, it is valid if you will only pay 20 or 30 or any amount you want , or if you want to pay in advance you may pay an amount that is greater than 360.

TCC

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-12-12 : 21:42:46

:)

TCC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources