Help me in challenging this complicated query.

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)

Help me in challenging this complicated query.

Author

Topic

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-03-28 : 21:03:50

declare @charges table (charges_key int, date_charged smalldatetime, amount smallmoney, [description] varchar(50))
insert into @charges
select 1, '10/19/2004', 350,'basic' union all
select 2,'11/19/2004', 350, 'excess' union all
select 3, '12/19/2004', 350, 'monthly' union all
select 4, '1/19/2005', 350,'monthly'

declare @payments table (or_no int, date_paid smalldatetime, amount smallmoney, [description] varchar(50))
insert into @payments
select 1001, '10/24/2004', 200, 'basic' union all
select 1002, '11/26/2004', 500, 'excess' union all
select 1003, '1/5/2005',350, 'monthly'


/*
Date_Charged	Amount		Charges_Key	Description	Or_No	Date_Paid	Amount		Description
2004-10-19	350.00		1		basic		1001	2004-10-24	200.00		basic
null		null		null		null		1002	2004-11-26	150.00		basic
2004-11-19	350.00		2		excess		1002	2004-11-26	350.00		excess	
2004-12-19	350.00		3		monthly		1003	2005-01-05	350.00		monthly
2005-1-19	350.00		4		monthly		null	null		null		null
 
*/

Hey guys,
I really need help with this query. How can I build the query above so i can produce the result quoted below.
I split the payment of 2004-11-26 having an amount of 500.00 to 150.00 and 350.00 because there was a previous balance
of 2004-10-19.
The concept of this query is to join the payment and charges using amount. Payment should be split so that
it will fill-up previous unpaid charge. Payment and charges should be ordered by date.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 01:57:30

This will show you how to get started.

Newer version further down...

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-29 : 02:12:44

Unbelievable!!

Complete domination...!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 02:13:48

Huh?

Peter Larsson
Helsingborg, Sweden

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-29 : 02:14:11

Peso, you are a MACHINE!

www.elsasoft.org

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-29 : 02:14:19

quote:

Originally posted by Peso

This will show you how to get started.

-- prepare sample data
declare	@charges table (charges_key int, date_charged smalldatetime, amount smallmoney, [description] varchar(50))

insert	@charges
select	1, '10/19/2004', 350,'basic' union all
select	2,'11/19/2004', 350, 'excess' union all
select	3, '12/19/2004', 350, 'monthly' union all
select	4, '1/19/2005', 350,'monthly' 

declare	@payments table (or_no int, date_paid smalldatetime, amount smallmoney, [description] varchar(50))
insert	@payments 
select	1001, '10/24/2004', 200, 'basic' union all
select	1002, '11/26/2004', 500, 'excess' union all
select	1003, '1/5/2005',350, 'monthly' 

-- Stage the data
DECLARE	@Stage TABLE
	(
		RecID INT IDENTITY PRIMARY KEY CLUSTERED,
		Charges_Key INT,
		Date_Charged SMALLDATETIME,
		ChargeAmount SMALLMONEY,
		ChargeDescription VARCHAR(50),
		Or_No INT,
		Date_Paid SMALLDATETIME,
		PaymentAmount SMALLMONEY,
		PaymentDescription VARCHAR(50)
	)

INSERT		@Stage
                (
		Charges_Key ,
		Date_Charged ,
		ChargeAmount ,
		ChargeDescription,
		Or_No ,
		Date_Paid ,
		PaymentAmount ,
		PaymentDescription
		)
SELECT		c.Charges_Key,
		c.Date_Charged,
		c.Amount,
		c.Description,
		p.Or_No,
		p.Date_Paid,
		p.Amount,
		p.Description
FROM		(
			SELECT		Charges_Key,
					Date_Charged,
					Amount,
					Description
			FROM		@Charges
		) AS c
FULL JOIN	(
			SELECT		Or_No,
					Date_Paid,
					Amount,
					Description
			FROM		@Payments
		) AS p ON p.Date_Paid = c.Date_Charged
ORDER BY	COALESCE(c.Date_Charged, p.Date_Paid)

-- Update missing data for charges
UPDATE		s1
SET		s1.Charges_Key = s2.Charges_Key,
		s1.Date_Charged = s2.Date_Charged,
		s1.ChargeAmount = s2.ChargeAmount,
		s1.ChargeDescription = s2.ChargeDescription
FROM		@Stage AS s1
INNER JOIN	@Stage AS s2 ON s2.RecID = s1.RecID - 1
WHERE		s1.Charges_Key IS NULL

-- Update missing data for payments
UPDATE		s1
SET		s1.Or_No = s2.Or_No,
		s1.Date_Paid = s2.Date_Paid,
		s1.PaymentAmount = s2.PaymentAmount,
		s1.PaymentDescription = s2.PaymentDescription
FROM		@Stage AS s1
INNER JOIN	@Stage AS s2 ON s2.RecID = s1.RecID + 1
WHERE		s1.Or_No IS NULL

-- Delete redundant data
DELETE		s
FROM		@Stage AS s
INNER JOIN	(
			SELECT		Charges_Key,
					MIN(RecID) AS RecID
			FROM		@Stage
			WHERE		ChargeAmount >= PaymentAmount
			GROUP BY	Charges_Key
		) AS x ON x.Charges_Key = s.Charges_Key AND x.RecID <> s.RecID

-- Update overflow payments
UPDATE		s
SET		s.Charges_Key = CASE WHEN x.MinRecID = s.RecID THEN NULL ELSE s.Charges_Key END,
		s.Date_Charged = CASE WHEN x.MinRecID = s.RecID THEN NULL ELSE s.Date_Charged END,
		s.ChargeAmount = CASE WHEN x.MinRecID = s.RecID THEN NULL ELSE s.ChargeAmount END,
		s.ChargeDescription = CASE WHEN x.MinRecID = s.RecID THEN NULL ELSE s.ChargeDescription END,
		s.PaymentAmount =	CASE
						WHEN x.MinRecID = s.RecID THEN s.PaymentAmount - s.ChargeAmount
						ELSE s.ChargeAmount
					END
FROM		@Stage AS s
INNER JOIN	(
			SELECT		Charges_Key,
					MIN(RecID) AS MinRecID,
					MAX(RecID) AS MaxRecID
			FROM		@Stage
			WHERE		ChargeAmount < PaymentAmount
			GROUP BY	Charges_Key
		) AS x ON x.Charges_Key = s.Charges_Key

-- Show the expected output
SELECT		Date_Charged,
		ChargeAmount,
		Charges_Key,
		ChargeDescription,
		Or_No,
		Date_Paid,
		PaymentAmount,
		PaymentDescription
FROM		@Stage
ORDER BY	RecID

Peter Larsson
Helsingborg, Sweden

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-29 : 02:15:45

quote:
Originally posted by jezemine

Peso, you are a MACHINE!

www.elsasoft.org

No...he has designed an intelligent and fast SQLBot !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 02:20:28

No, I am not a machine

Sometimes a bot

But mostly, I get lucky now and then...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 02:34:04

This is the complete code to get the accurate result as wanted.

-- prepare sample data
declare	@charges table (charges_key int, date_charged smalldatetime, amount smallmoney, [description] varchar(50))

insert	@charges
select	1, '10/19/2004', 350,'basic' union all
select	2,'11/19/2004', 350, 'excess' union all
select	3, '12/19/2004', 350, 'monthly' union all
select	4, '1/19/2005', 350,'monthly' 

declare	@payments table (or_no int, date_paid smalldatetime, amount smallmoney, [description] varchar(50))
insert	@payments 
select	1001, '10/24/2004', 200, 'basic' union all
select	1002, '11/26/2004', 500, 'excess' union all
select	1003, '1/5/2005',350, 'monthly' 

-- Stage the data
DECLARE	@Stage TABLE
	(
		RecID INT IDENTITY PRIMARY KEY CLUSTERED,
		Charges_Key INT,
		Date_Charged SMALLDATETIME,
		ChargeAmount SMALLMONEY,
		ChargeDescription VARCHAR(50),
		Or_No INT,
		Date_Paid SMALLDATETIME,
		PaymentAmount SMALLMONEY,
		PaymentDescription VARCHAR(50)
	)

INSERT		@Stage
		(
			Charges_Key,
			Date_Charged,
			ChargeAmount,
			ChargeDescription,
			Or_No,
			Date_Paid,
			PaymentAmount,
			PaymentDescription
		)
SELECT		c.Charges_Key,
		c.Date_Charged,
		c.Amount,
		c.Description,
		p.Or_No,
		p.Date_Paid,
		p.Amount,
		p.Description
FROM		(
			SELECT		Charges_Key,
					Date_Charged,
					Amount,
					Description
			FROM		@Charges
		) AS c
FULL JOIN	(
			SELECT		Or_No,
					Date_Paid,
					Amount,
					Description
			FROM		@Payments
		) AS p ON p.Date_Paid = c.Date_Charged
ORDER BY	COALESCE(c.Date_Charged, p.Date_Paid)

-- Update missing data for charges
UPDATE		s1
SET		s1.Charges_Key = s2.Charges_Key,
		s1.Date_Charged = s2.Date_Charged,
		s1.ChargeAmount = s2.ChargeAmount,
		s1.ChargeDescription = s2.ChargeDescription
FROM		@Stage AS s1
INNER JOIN	@Stage AS s2 ON s2.RecID = s1.RecID - 1
WHERE		s1.Charges_Key IS NULL

-- Update missing data for payments
UPDATE		s1
SET		s1.Or_No = s2.Or_No,
		s1.Date_Paid = s2.Date_Paid,
		s1.PaymentAmount = s2.PaymentAmount,
		s1.PaymentDescription = s2.PaymentDescription
FROM		@Stage AS s1
INNER JOIN	@Stage AS s2 ON s2.RecID = s1.RecID + 1
WHERE		s1.Or_No IS NULL

-- Delete redundant data
DELETE		s
FROM		@Stage AS s
INNER JOIN	(
			SELECT		Charges_Key,
					MIN(RecID) AS RecID
			FROM		@Stage
			WHERE		ChargeAmount >= PaymentAmount
			GROUP BY	Charges_Key
		) AS x ON x.Charges_Key = s.Charges_Key AND x.RecID <> s.RecID

-- Update overflow payments
UPDATE		s
SET		s.Charges_Key = CASE WHEN x.MinRecID = s.RecID THEN NULL ELSE s.Charges_Key END,
		s.Date_Charged = CASE WHEN x.MinRecID = s.RecID THEN NULL ELSE s.Date_Charged END,
		s.ChargeAmount = CASE WHEN x.MinRecID = s.RecID THEN NULL ELSE s.ChargeAmount END,
		s.ChargeDescription = CASE WHEN x.MinRecID = s.RecID THEN NULL ELSE s.ChargeDescription END,
		s.PaymentAmount =	CASE
						WHEN x.MinRecID = s.RecID THEN s.PaymentAmount - s.ChargeAmount
						ELSE s.ChargeAmount
					END
FROM		@Stage AS s
INNER JOIN	(
			SELECT		Charges_Key,
					MIN(RecID) AS MinRecID,
					MAX(RecID) AS MaxRecID
			FROM		@Stage
			WHERE		ChargeAmount < PaymentAmount
			GROUP BY	Charges_Key
		) AS x ON x.Charges_Key = s.Charges_Key

-- Update invalid data for payments
UPDATE		s1
SET		s1.PaymentDescription = s2.PaymentDescription
FROM		@Stage AS s1
INNER JOIN	(
			SELECT		s1.RecID,
					MAX(s2.RecID) AS NewRecID
			FROM		@Stage AS s1
			INNER JOIN	@Stage AS s2 ON s2.RecID < s1.RecID
			WHERE		s1.Charges_Key IS NULL
			GROUP BY	s1.RecID
		) AS x ON x.RecID = s1.RecID
INNER JOIN	@Stage AS s2 ON s2.RecID = x.NewRecID

-- Show the expected output
SELECT		Date_Charged,
		ChargeAmount,
		Charges_Key,
		ChargeDescription,
		Or_No,
		Date_Paid,
		PaymentAmount,
		PaymentDescription
FROM		@Stage
ORDER BY	RecID

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 08:35:52

Is this nothing but a continuation from these?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76143
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76197

Peter Larsson
Helsingborg, Sweden

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-03-29 : 20:01:14

thanks a lot..!!! it really works!!!

TCC

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-03-29 : 20:04:57

yeah.. its a continuation of my previous problem.. and now it's all done.. your really great guyzzz

TCC

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-03-29 : 20:09:24

I LOVE SQLTEAM... :)

TCC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 20:11:08

Let's hope so.

Peter Larsson
Helsingborg, Sweden

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-03-29 : 22:11:45

-- prepare sample data
declare @charges table (charges_key int, date_charged smalldatetime, amount smallmoney, [description] varchar(50))

insert @charges
select 1, '10/19/2004', 400,'basic' union all
select 2,'11/19/2004', 350, 'excess' union all
select 3, '12/19/2004', 350, 'monthly' union all
select 4, '1/19/2005', 400,'monthly' union all
select 5, '2/25/2005', 350,'monthly'

declare @payments table (or_no int, date_paid smalldatetime, amount smallmoney, [description] varchar(50))
insert @payments
select 1001, '10/24/2004', 200, 'basic' union all
select 1002, '11/26/2004', 600, 'excess' union all
select 1003, '1/5/2005',350, 'monthly' union all
select 1004, '3/5/2005',350, 'monthly' union all
select 1005, '4/5/2005',100, 'monthly' union all
select 1006, '5/5/2005',50, 'monthly'


Date_Charged	ChargeAmount Charges_Key ChargeDescription	Or_No       Date_Paid		PaymentAmount PaymentDescription                                 
------------------------------------------------------ ------------ ----------- -------------------------------------------------- 
2004-10-19	400.0000     1		basic			1001        2004-10-24		200.0000      basic
NULL		NULL         NULL        NULL			1002        2004-11-26		250.0000      basic
2004-11-19	350.0000     2		excess			1002        2004-11-26		350.0000      excess
2004-12-19	350.0000     3		monthly			1003        2005-01-05		350.0000      monthly
2005-01-19	400.0000     4		monthly			NULL        NULL		NULL          NULL
2005-02-25	350.0000     5		monthly			1004        2005-03-05		350.0000      monthly
NULL		NULL         NULL        NULL			1005        2005-04-05		100.0000      monthly
NULL		NULL         NULL        NULL			1006        2005-05-05		50.0000       monthly

The Value Colored in RED is incorrect because its charge is only 400.00. The payment of 2004-10-24 200.00 + 2004-11-26 250.00 already exceeds to 400.
The Value Colored in YELLOW should be moved up because there is an unpaid charges. Charges should not be leaved unpaid. Payment should fill-up the 
previous unpaid charge.


This should be the query result... :)
Date_Charged	ChargeAmount Charges_Key Description	Or_No       Date_Paid		PaymentAmount PaymentDescription                                 
-------------  	------------------------------------- ------------ ----------- ----------------------------------------------
2004-10-19 	400.0000     1           basic		1001        2004-10-24		200.0000      basic
NULL		NULL	     NULL	 NULL		1002	    2004-11-26   	200.0000      basic
2004-11-19	350.0000     2           excess		1002        2004-11-26		350.0000      excess
NULL		NULL	     NULL	 NULL		1002	    2004-11-26   	50.0000       monthly
2004-12-19	350.0000     3           monthly	1003        2005-01-05		300.0000      monthly
NULL		NULL	     NULL	 NULL		1003	    2005-01-05		50.0000	      monthly
2005-01-19	400.0000     4           monthly	1004        2005-03-05		350.0000      monthly
2005-02-25	350.0000     5           monthly	1005        2005-04-05		100.0000      monthly
NULL		NULL         NULL        NULL		1006        2005-05-05		50.0000       monthly

I put separator between between charges..
Date_Charged	ChargeAmount Charges_Key Description	Or_No       Date_Paid		PaymentAmount PaymentDescription                                 
-------------  	------------------------------------- ------------ ----------- ----------------------------------------------
2004-10-19 	400.0000     1           basic		1001        2004-10-24		200.0000      basic
NULL		NULL	     NULL	 NULL		1002	    2004-11-26   	200.0000      basic

2004-11-19	350.0000     2           excess		1002        2004-11-26		350.0000      excess

NULL		NULL	     NULL	 NULL		1002	    2004-11-26   	50.0000       monthly
2004-12-19	350.0000     3           monthly	1003        2005-01-05		300.0000      monthly

NULL		NULL	     NULL	 NULL		1003	    2005-01-05		50.0000	      monthly
2005-01-19	400.0000     4           monthly	1004        2005-03-05		350.0000      monthly

2005-02-25	350.0000     5           monthly	1005        2005-04-05		100.0000      monthly
NULL		NULL         NULL        NULL		1006        2005-05-05		50.0000       monthly

The query works but i think there is just a little adjustment. I tried the query above and it didnt meet the
result i expected. :)
Do you have any idea where to modify so that the payment of 2004-11-26 of 600.00 will be divided into "payment for previous unpaid charge = 200.00" and
"payment for current charge=350" and "payment for advance charge=50".
And also the payment in color yellow.. should be moved up to fill-up those unpaid charges..

TCC

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-03-29 : 23:12:10

Another EXAMPLE... :)

declare @charges table (charges_key int, date_charged smalldatetime, amount smallmoney, [description] varchar(50))

insert @charges
select 1, '10/19/2004', 400,'basic' union all
select 2,'11/19/2004', 350, 'excess' union all
select 3, '12/19/2004', 350, 'monthly' union all
select 4, '1/19/2005', 400,'monthly' union all
select 5, '2/25/2005', 350,'monthly'

declare @payments table (or_no int, date_paid smalldatetime, amount smallmoney, [description] varchar(50))
insert @payments
select 1001, '10/24/2004', 200, 'basic' union all
select 1002, '11/26/2004', 600, 'excess' union all
select 1003, '1/5/2005',350, 'monthly' union all
select 1004, '3/5/2005',700, 'monthly' union all
select 1005, '4/5/2005',100, 'monthly' union all
select 1006, '5/5/2005',50, 'monthly'



Date_Charged	ChargeAmount Charges_Key Description	Or_No       Date_Paid		PaymentAmount PaymentDescription                                 
-------------  	------------------------------------- ------------ ----------- ----------------------------------------------
2004-10-19 	400.0000     1           basic		1001        2004-10-24		200.0000      basic
NULL		NULL	     NULL	 NULL		1002	    2004-11-26   	200.0000      basic

2004-11-19	350.0000     2           excess		1002        2004-11-26		350.0000      excess

NULL		NULL	     NULL	 NULL		1002	    2004-11-26   	50.0000       monthly
2004-12-19	350.0000     3           monthly	1003        2005-01-05		300.0000      monthly

NULL		NULL	     NULL	 NULL		1003	    2005-01-05		50.0000	      monthly
2005-01-19	400.0000     4           monthly	1004        2005-03-05		350.0000      monthly

2005-02-25	350.0000     5           monthly	1004	    2005-03-05		350.00	      monthly

NULL		NULL	     NULL	 NULL		1005        2005-04-05		100.0000      monthly
NULL		NULL         NULL        NULL		1006        2005-05-05		50.0000       monthly

The VALUE colored in RED is an advance payment of 2005-03-05 having an amount of 700.00 which was divided into
350.00 for previous and 350.00 for advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-30 : 03:37:36

Well, I have given you the blueprint how to solve this.
Good luck.

Peter Larsson
Helsingborg, Sweden

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-03-30 : 04:09:02

ok thanks

TCC

tishri
Yak Posting Veteran

95 Posts

Posted - 2007-03-30 : 04:11:47

last favor, do you think its possible to do it using transact sql? :) hehehe

TCC