Last transaction date for all items

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

SQL Server 2008 Forums

Transact-SQL (2008)

Last transaction date for all items

Author

Topic

Cousboy
Starting Member

7 Posts

Posted - 2010-09-16 : 15:20:37

Please help,

I would like to be able to see the last transaction date for each individual item in my transaction table.

I have a transaction table that contains items A,B,C and D along with other information about transaction dates and types.

To see the transaction information in date descending order for item A, I would use the SELECT statement below

SELECT item, transdate, transtype
FROM transactions
WHERE item = 'A'
GROUP BY item, transdate, transtype
ORDER BY transdate DESC

My results will look something like this

ITEM TRANSDATE TRANSTYPE
A 01/01/2010 FULFILLMENT
A 01/06/2009 RECEIPT
A 01/05/2009 FULFILLMENT
A 01/01/2009 RECEIPT

To just see the very latest result I would use this statement

SELECT TOP 1 item, transdate, transtype
FROM transactions
WHERE item = 'A'
GROUP BY item, transdate, transtype
ORDER BY transdate DESC

Where my results will now look like:

ITEM TRANSDATE TRANSTYPE
A 01/01/2010 FULFILLMENT

This is fine if I want to search item by item but I would like to show the item, transaction type and last transaction date for each item similar to the report below:

ITEM TRANSDATE TRANSTYPE
A 01/01/2010 FULFILLMENT
B 31/06/2009 FULFILLMENT
C 22/05/2009 FULFILLMENT
D 19/01/2009 RECEIPT

Any ideas?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-16 : 15:25:47

this is one way

SELECT	t.ITEM, t.TRANSDATE, t.TRANSTYPE
FROM	transactions t
JOIN	(
	SELECT	ITEM, MAX(TRANSDATE) dt
	FROM	transactions
	GROUP BY ITEM
) x
On	x.ITEM = t.ITEM
And	x.dt = t.TRANSDATE

by the way, in your queries that you posted, there is no reason for the GROUP BY clause, since you aren't doing any aggregation

Cousboy
Starting Member

7 Posts

Posted - 2010-09-16 : 16:18:49

quote:
Originally posted by russell

this is one way
SELECT t.ITEM, t.TRANSDATE, t.TRANSTYPE
FROM transactions t
JOIN (
SELECT ITEM, MAX(TRANSDATE) dt
FROM transactions
GROUP BY ITEM
) x
On x.ITEM = t.ITEM
And x.dt = t.TRANSDATE

by the way, in your queries that you posted, there is no reason for the GROUP BY clause, since you aren't doing any aggregation

Hi Russell,

Thank you for the reply. I'm a bit of a newbie and the only thing about your script that I don't quite understand is the "dt", is it the same as when you say "FROM transactions t" ?

Thanks for tip on the GROUP BY clause, it was a run over from where I was trying to write my original script befor compressing it to post here

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-16 : 18:57:28

yes, it's an alias.

Cousboy
Starting Member

7 Posts

Posted - 2010-09-17 : 04:31:15

Hi Russell,

That worked perfectly but now I need to add a WHERE clause to filter the results a little. I have just tested this script and it doesn't quite give me the results that i was hoping for:

/*
SELECT g.artcode AS 'Item',
(CONVERT (varchar(20),g.datum,105)) AS 'TransDate',
g.transsubtype AS 'TransType'
FROM gtran g
JOIN (SELECT artcode, MAX(datum)dt FROM gtran GROUP BY artcode) x ON x.artcode = g.artcode AND x.dt = g.datum
WHERE g.transsubtype <> 'G'
*/

This script gives me all of information that I need to see however if the last transaction happened to be transubtype='G' then it will ignore the item which is what I don't want to do. What I now need to report is the latest transaction that wasn't transubtype='G'.

As the g.reknr = 21100 then the most likely last transubtypes are likley to be, but not limited to A or B so I don't want to tie it down just to those two.

I'm thinking that maybe I could create a VIEW with all the transubtype='G' removed and then extract my data from this instead of the db table but i don't know if this is the most efficient way of doing this, any thoughts?

I really appreciate your help so far, you have been a lifesaver.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-17 : 07:19:01

put the WHERE <> 'G' in the subquery

JOIN (
	SELECT artcode, MAX(datum) dt
	FROM 	gtran
	WHERE 	g.transsubtype <> 'G' 
	GROUP BY
		artcode
) x
ON 	x.artcode = g.artcode
AND 	x.dt = g.datum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources