Need Help on my 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 2008 Forums

Transact-SQL (2008)

Need Help on my Query

Author

Topic

indralang
Starting Member

7 Posts

Posted - 2014-02-06 : 03:35:37

I have 2 tables


CREATE TABLE BILL
(
BILL_ID INT INDENTITY NOTNULL
BILL_DATE DATETIME
BILL_AMOUNT NUMERIC(10,2)
BILL_BALANCE NUMERIC(10,2)
)

CREATE TABLE PAYMENT
(
PAYMENT_ID INT IDENTITY NOTNULL
BILL_ID INT
PAYMENT_DATE DATETIME
PAYMENT_AMOUNT NUMERIC(10,2)
)

I have data like this:
Bill: $100

In January, I paid $50
In Febuary, I paid $30
In March, I paid $20

I need to create report like below:
In January the report will be:

Bill: $100
Payment1: $50
In Febuary:

Bill: $100
Payment1: $50
Payment2: $30

In March:

Bill: $100
Payment1: $50
Payment2: $30
Payment3: $20

In May it won't appear because it already paid in March.

This is my Query:
SELECT A.BILL_AMOUNT, A.BILL_DATE, B.PAYMENT_AMOUNT, B.PAYMENT_DATE
FROM BILL AS A
LEFT JOIN PAYMENT AS B ON A.BILL_ID = B.BILL_ID
WHERE MONTH(A.BILL_DATE) <= @PERIOD_MONTH AND YEAR(A.BILL_DATE) = @PERIOD_YEAR

I need help to fix my query so the result like the report.

sree203509
Starting Member

2 Posts

Posted - 2014-02-06 : 03:59:44

Try this

SELECT A.BILL_AMOUNT, A.BILL_DATE, B.PAYMENT_AMOUNT, B.PAYMENT_DATE
FROM BILL AS A
LEFT JOIN PAYMENT AS B ON A.BILL_ID = B.BILL_ID
WHERE MONTH(B.PAYMENT_DATE) <= @PERIOD_MONTH AND YEAR(B.PAYMENT_DATE) = @PERIOD_YEAR

indralang
Starting Member

7 Posts

Posted - 2014-02-06 : 04:19:53

quote:
Originally posted by sree203509

Try this

SELECT A.BILL_AMOUNT, A.BILL_DATE, B.PAYMENT_AMOUNT, B.PAYMENT_DATE
FROM BILL AS A
LEFT JOIN PAYMENT AS B ON A.BILL_ID = B.BILL_ID
WHERE MONTH(B.PAYMENT_DATE) <= @PERIOD_MONTH AND YEAR(B.PAYMENT_DATE) = @PERIOD_YEAR

Thanks for answering, but its not the answer if there is a bill in January but no payment in the same month. For example the first payment in Febuary.

If that happened, the report should be like this:

January
Bill: $100
Payment: $0

Febuary
Bill: $100
Payment: $50

etc.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-06 : 05:41:14

[code]
DECLARE @PERIOD_YEAR INT = 2014,
@PERIOD_MONTH INT = 3

; with
PYMT AS
(
SELECT p.BILL_ID, p.PAYMENT_AMOUNT, p.PAYMENT_DATE
FROM PAYMENT p
WHERE p.PAYMENT_DATE < DATEADD(MONTH, @PERIOD_MONTH, DATEADD(YEAR, @PERIOD_YEAR - 1900, 0))
)
SELECT b.BILL_ID, b.BILL_AMOUNT, b.BILL_DATE, p.PAYMENT_AMOUNT, p.PAYMENT_DATE
FROM BILL b
LEFT JOIN
(
SELECT p.BILL_ID, PAYMENT_AMOUNT = SUM(p.PAYMENT_AMOUNT), LAST_PYMT_DATE = MAX(p.PAYMENT_DATE)
FROM PYMT p
GROUP BY p.BILL_ID
) tp on b.BILL_ID = tp.BILL_ID
LEFT JOIN PYMT p on b.BILL_ID = p.BILL_ID
WHERE b.BILL_AMOUNT > ISNULL(tp.PAYMENT_AMOUNT, 0) -- if the bill is not fully paid
OR ( -- or the last payment is the report date
YEAR(tp.LAST_PYMT_DATE) = @PERIOD_YEAR
AND MONTH(tp.LAST_PYMT_DATE) = @PERIOD_MONTH
)
ORDER BY b.BILL_ID, p.PAYMENT_DATE
[/code]

KH
[spoiler]Time is always against us[/spoiler]

indralang
Starting Member

7 Posts

Posted - 2014-02-06 : 21:08:50

quote:

Originally posted by khtan


DECLARE	@PERIOD_YEAR	INT	= 2014,
	@PERIOD_MONTH	INT	= 3

; with 
PYMT AS
(
	SELECT	p.BILL_ID, p.PAYMENT_AMOUNT, p.PAYMENT_DATE
	FROM	PAYMENT p
	WHERE	p.PAYMENT_DATE	< DATEADD(MONTH, @PERIOD_MONTH, DATEADD(YEAR, @PERIOD_YEAR - 1900, 0))	
)
SELECT	b.BILL_ID, b.BILL_AMOUNT, b.BILL_DATE, p.PAYMENT_AMOUNT, p.PAYMENT_DATE
FROM	BILL b
	LEFT JOIN 
	(
		SELECT	p.BILL_ID, PAYMENT_AMOUNT = SUM(p.PAYMENT_AMOUNT), LAST_PYMT_DATE = MAX(p.PAYMENT_DATE)
		FROM	PYMT p
		GROUP BY p.BILL_ID
	) tp	on	b.BILL_ID	= tp.BILL_ID
	LEFT JOIN PYMT p	on	b.BILL_ID	= p.BILL_ID
WHERE	b.BILL_AMOUNT	> ISNULL(tp.PAYMENT_AMOUNT, 0)		-- if the bill is not fully paid
OR	(							-- or the last payment is the report date
		YEAR(tp.LAST_PYMT_DATE)		= @PERIOD_YEAR
	AND	MONTH(tp.LAST_PYMT_DATE)	= @PERIOD_MONTH
	)
ORDER BY b.BILL_ID, p.PAYMENT_DATE

KH
[spoiler]Time is always against us[/spoiler]

This is what I really need!! Thank you very much!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources