Trying to Pass Date from one Query to Another

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

General SQL Server Forums

New to SQL Server Programming

Trying to Pass Date from one Query to Another

Author

Topic

bconner
Starting Member

48 Posts

Posted - 2010-11-11 : 10:25:50

I have 2 queries one is a Sub Query that look something like this


SELECT
  A.INVOICE
 ,A.DATE
 ,B.PAY
FROM TABLE A
LEFT JOIN
  (SELECT
    ,INVOICE
    ,POST_DATE
    ,SUM(PAY) AS PAY
   FROM TABLE B
   GROUP BY
     INVOICE) B ON A.INVOICE = B.INVOICE

I want to show all records in table A and only the Pay from table B where the B.Post_Date >= A.DATE

How can I pass the Date from the first query to the Sub query so that the Sub query will only pull payments where the Post_Date >= Date from the first query?

Any help is greatly appreciated....
Brian

Brian

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-11 : 10:33:28

if you have sql server 2005 or later:


SELECT A.INVOICE
       ,A.DATE
       ,ca.PAY
FROM TABLEA a
outer apply  
       (SELECT SUM(PAY) AS PAY
       FROM TABLEB b
       where  b.invoice = a.invoice
       and    b.Post_date >= a.date
       ) ca

But assuming tableA is invoices and a.Date is the date of the invoice then how could you have payments against that invoice earlier than the invoice date?

Be One with the Optimizer
TG

bconner
Starting Member

48 Posts

Posted - 2010-11-11 : 11:18:33

Thanks TG for the quick response.... That worked! The Date in table a is a Date stamp that was used to indicate when the invoice was worked. So, we were wanting to quantify what payments came thru after the invoice was worked. Sometimes we get payments on invoices before they are worked....

Brian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources