SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need Help on my Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

indralang
Starting Member

Indonesia
4 Posts

Posted - 02/06/2014 :  03:35:37  Show Profile  Reply with Quote
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

India
2 Posts

Posted - 02/06/2014 :  03:59:44  Show Profile  Reply with Quote
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

Edited by - sree203509 on 02/06/2014 04:00:26
Go to Top of Page

indralang
Starting Member

Indonesia
4 Posts

Posted - 02/06/2014 :  04:19:53  Show Profile  Reply with Quote
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.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 02/06/2014 :  05:41:14  Show Profile  Reply with Quote

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
Time is always against us

Go to Top of Page

indralang
Starting Member

Indonesia
4 Posts

Posted - 02/06/2014 :  21:08:50  Show Profile  Reply with Quote
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
Time is always against us





This is what I really need!! Thank you very much!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000