SQL Query Question
-
Hi All
I ‘m using SQL Server 2005.I got 2 tables as below
Table A:-
Invoice No Invoice Amount Invoice Date
A1234 1000 30/07/2008
B4444 200 11/08/2008
Table B:-
Invoice No Payment No Payment Collected Payment Date
A1234 P111 300 03/08/2008
A1234 P256 400 09/09/2008
B4444 P555 200 31/08/2008
I have 2 parameters called “Datefrom” and “DateTo”
Datefrom=’01/09/2008’
DateTo=’30/09/2008’
If one of transaction is between 01/09/2008 to 30/09/2008 then it will show all related transaction.
Eg: Payment P111 and P256 is apply to same invoice(A1234) and P256 is between 01/09/2008 to 30/09/2008, so both payment will display.
P555 is not between 01/09/2008 to 30/09/2008, so it won’t display.
Final result as below:-
Invoice No Payment No Payment Collected Payment Date
A1234 P111 300 03/08/2008
A1234 P256 400 09/09/2008
how to write SQL Query in order to get the result?
Thank for help.
-
select [Invoice No],[Payment No],
[Payment Collected],
[Payment Date]
from TableB
where [Invoice No] in (select [Invoice No]
from TableB
where [Payment Date] between @Datefrom and @DateTo)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark (10/7/2008)
select [Invoice No],[Payment No],
[Payment Collected],
[Payment Date]
from TableB
where [Invoice No] in (select [Invoice No]
from TableB
where [Payment Date] between @Datefrom and @DateTo)
You don't even need the sub query as all of the data is in table b.
It could be:
SELECT [Invoice No], [Payment No], [Payment Collected], [Payment Date]
FROM TableB
WHERE [Payment Date] BETWEEN @DateFrom AND @DateTo
-
The sub-query is required by the bizlogic
If one of transaction is between 01/09/2008 to 30/09/2008 then it will show all related transaction.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply