April 15, 2011 at 9:14 am
Hello all,
I have two queries:
First:
SELECT TransactionID, TransactionType, EventDate, TransactionDate
FROM dbo.Invoice
WHERE dbo.Invoice.EventDate = '2011/02/22'
AND dbo.Invoice.EventDate > dbo.Invoice.TransactionDate
Results:
205017714Product2011/02/222010/12/02
205017714Product2011/02/222010/12/02
205017714Product2011/02/222010/12/02
165030095Product22/02/201110/01/2011
205017826Product22/02/201114/12/2010
205018146Product22/02/201124/01/2011
205018146Product22/02/201124/01/2011
205018146Product22/02/201124/01/2011
9956470 Product22/02/201104/01/2011
9956470 Product22/02/201104/01/2011
9956866 Product22/02/201106/02/2011
9956866 Product22/02/201106/02/2011
9957000 Product22/02/201116/02/2011
9957000 Product22/02/201116/02/2011
9957047 Product22/02/201119/02/2011
9957088 Product22/02/201121/02/2011
9957088 Product22/02/201121/02/2011
And second (two TransactionIDs in which you can see difference between them):
SELECT TransactionID, TransactionType, EventDate, TransactionDateFROM dbo.JournalHistory
WHERE (JournalHistory.TransactionID = 'AG09957047' OR
JournalHistory.TransactionID = '205017714')
ORDER BY TransactionID
Results:
205017714 Product2011/02/222010/12/02
205017714 Product2011/02/222010/12/02
205017714 Product2011/02/222010/12/02
205017714 Product2011/02/222011/02/22
205017714 Product2011/02/222011/02/22
205017714 Product2011/02/222011/02/22
9957047 Product2011/02/222011/02/19
9957047 Product2011/02/222011/02/19
9957047 Payment2011/02/192011/02/19
Idea is to have first query but with only ones which have payment before date in the first query.
So, I will have 9957047, but not 205017714.
Any ideas?
Thanks,
Brano
April 15, 2011 at 9:26 am
You can use and INNER JOIN on the tables, and join on at least the TransactionID and TransactionType fields - maybe more of them. Then include your filters in the where statement. But you only need one query.
edit > for clarity
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
April 15, 2011 at 9:29 am
I guess I am not following what you mean by "payment before date" What fields would be involved in that?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 15, 2011 at 9:54 am
Thanks for answers,
In first you have Where clause: dbo.Invoice.EventDate = '2011/02/22'
As you can see, in the second, 9957047 has Payment at 2011/02/19 (<2011/02/22), which 205017714 doesn't.
So, real query should have 9957047 and not 205017714.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply