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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy