Help to find previous transaction

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)

Help to find previous transaction

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-01-14 : 04:52:25

Hi,
I have a query that would need to get the last touched or transaction after workid "FJ_CBDEF".
Below is my initial query and DDL. Any idea is very much appreciated.
The result for the 2 Item code should be "FJ_LMDADS" and "FJ_CBVFI2"

Thank you in advance.


Create table #maintable
(Item nvarchar(35), Itemcode nvarchar(35))
Insert into #maintable(item,itemcode) values ('AAAAA','RPT00000001')
Insert into #maintable(item,itemcode) values ('BBBBB','RPT00000002')


Create table #Table1
(Itemcode nvarchar(35), RankNum int, WorkID nvarchar(35), CreatedDatetime datetime)
Insert into #table1(itemcode, RankNum, WorkID,CreatedDatetime) values ('RPT00000001','5','FJ_Pentry','2015-01-12 23:50:48.000')
Insert into #table1(itemcode, RankNum, WorkID,CreatedDatetime) values ('RPT00000001','15','FJ_CBDEF','2015-01-14 05:23:10.000')
Insert into #table1(itemcode, RankNum, WorkID,CreatedDatetime) values ('RPT00000001','10','FJ_LMDADS','2015-01-13 15:03:56.000')

Insert into #table1(itemcode, RankNum, WorkID,CreatedDatetime) values ('RPT00000002','0','FJ_Pentry','2015-01-12 23:53:47.000')
Insert into #table1(itemcode, RankNum, WorkID,CreatedDatetime) values ('RPT00000002','10','FJ_LMDADS','2015-01-13 14:52:15.000')
Insert into #table1(itemcode, RankNum, WorkID,CreatedDatetime) values ('RPT00000002','15','FJ_DiAss','2015-01-13 16:24:35.000')
Insert into #table1(itemcode, RankNum, WorkID,CreatedDatetime) values ('RPT00000002','20','FJ_CBVFI2','2015-01-13 19:47:36.000')
Insert into #table1(itemcode, RankNum, WorkID,CreatedDatetime) values ('RPT00000002','25','FJ_CBDEF','2015-01-14 05:22:52.000')

Create table #Table2
(Itemcode nvarchar(35), RankNum int, WorkID nvarchar(35), CreatedDatetime datetime, FCode nvarchar(5))
Insert into #table2(itemcode, RankNum, WorkID, CreatedDatetime, FCode) values ('RPT00000001','10','FJ_LMDAD','2015-01-12 23:50:48.000','FJ001')
Insert into #table2(itemcode, RankNum, WorkID, CreatedDatetime, FCode) values ('RPT00000002','20','FJ_CBVFI2','2015-01-13 19:47:36.000','FJ004')


Select m.Item, m.Itemcode, t1.RankNum, t1.WorkID, t1.CreatedDatetime, t2.FCode,
rn=ROW_NUMBER() OVER (partition by m.Itemcode order by m.Itemcode , t1.CreatedDatetime desc )
From #maintable m
Left join #Table1 t1
On t1.Itemcode = m.Itemcode 
Left Join #Table2 t2
On t2.Itemcode = t1.Itemcode and t2.RankNum = t1.RankNum 
Order by   m.Itemcode, t1.RankNum

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-15 : 13:42:53

Would you please post some sample output? I'm getting:


Item	Itemcode	RankNum	WorkID	        CreatedDatetime	        FCode	rn
AAAAA	RPT00000001	5	FJ_Pentry	2015-01-12 23:50:48.000	NULL	3
AAAAA	RPT00000001	10	FJ_LMDADS	2015-01-13 15:03:56.000	FJ001	2
AAAAA	RPT00000001	15	FJ_CBDEF	2015-01-14 05:23:10.000	NULL	1
BBBBB	RPT00000002	0	FJ_Pentry	2015-01-12 23:53:47.000	NULL	5
BBBBB	RPT00000002	10	FJ_LMDADS	2015-01-13 14:52:15.000	NULL	4
BBBBB	RPT00000002	15	FJ_DiAss	2015-01-13 16:24:35.000	NULL	3
BBBBB	RPT00000002	20	FJ_CBVFI2	2015-01-13 19:47:36.000	FJ004	2
BBBBB	RPT00000002	25	FJ_CBDEF	2015-01-14 05:22:52.000	NULL	1

What would you rather see?

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-01-16 : 00:28:17

I would like to see these result:

Item	Itemcode	RankNum	WorkID	        CreatedDatetime	        FCode	
AAAA	RPT00000001	10	FJ_LMDADS	2015-01-13 15:03:56.000	FJ001	
BBBBB	RPT00000002	20	FJ_CBVFI2	2015-01-13 19:47:36.000	FJ004

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources