0

I'd like to get the last transaction for a cardnumber between a given date with additional criteria.

Select * from table WHERE date <= '6/30/2012' and Type = 'D'

Data:

CardNumber   FirstName      Date           Type
1            John           11/01/2013     F
1            John           10/14/2013     D
1            John           05/01/2012     D
2            Paul           04/01/2012     D
2            Paul           05/15/2013     F
2            Paul           01/01/2012     D

Result:

CardNumber   FirstName      Date      
1            John           05/01/2012     D
2            Paul          04/01/2012     D
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user697698
  • 45
  • 8
  • `SELECT CardNumber, FirstName, MAX(Date), Type FROM table WHERE Date <= '6/30/2012' AND Type = 'D' GROUP BY CardNumber, FirstName, Type` should work, but there may be better (more efficient) ways to achieve the result you're looking for. – Tim Nov 12 '13 at 00:29
  • 1
    I think this one is more efficient: Select TOP 1 * from table WHERE date <= '6/30/2012' and Type = 'D' ORDER BY Date Desc – cha Nov 12 '13 at 00:31
  • @cha - You should post that as an answer. I agree that it would seem to be more efficient than my suggestion. – Tim Nov 12 '13 at 00:33
  • Neither Worked. Tims returns the latest record for dates before 6/30/2012, but skips any dates in 2013. I want it to not return anything of there has been a transaction past 6/30/2013. Cha returns one record, I really need the entire list. – user697698 Nov 12 '13 at 19:10
  • Tim's answer should be the correct one based on your requirements. But to exclude records for a card where there are transactions past a certain date you should use something like a WHERE NOT EXISTS statement. And most likely remove the WHERE data <= '6/30/2012' part. – Tom Dec 28 '15 at 12:36

0 Answers0