Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

indralang
Starting Member

Indonesia
7 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
7 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
17689 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
7 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  
 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