select previous date record - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

select previous date record

Author

Topic

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2012-05-30 : 02:47:52

Dear Member

Following is my table structure
Table1

item wt seq.no date
item1 wt1 1 5/30/2012
item2 wt2 1 5/30/2012
item3 wt3 1 5/30/2012
item4 wt4 2 5/30/2012
item5 wt5 2 5/30/2012
item6 wt6 1 5/30/2012

Table 2

item wt seq.no date
item1 wt1 1 5/29/2012
item2 wt2 1 5/29/2012
item3 wt3 1 5/29/2012
item4 wt4 2 5/29/2012
item5 wt5 2 5/29/2012
item6 wt6 1 5/29/2012
item1 wt1 1 5/28/2012
item2 wt2 1 5/28/2012
item3 wt3 1 5/28/2012
item4 wt4 2 5/28/2012
item5 wt5 2 5/28/2012
item6 wt6 1 5/28/2012
item1 wt1 1 5/27/2012
item2 wt2 1 5/27/2012
item3 wt3 1 5/27/2012
item4 wt4 2 5/27/2012
item5 wt5 2 5/27/2012
item6 wt6 1 5/27/2012

Both table def is same table1 is inserted only today records and table2 is history table.
At the end of the transfer all the table1 record is transfer in table2.
table1 records is available in table2 records with diff date.
i want to select a column item,wt,seq no. to table1 and wt column of table2

i want to find a wt in table2 which are last inserted date to matching a item and seq.no of table1 tables. so how can i will write a select query to find all record in one select query

thanks in advance

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-06-01 : 12:19:26

[CODE]declare @tbl1 table (
item varchar(10),
wt varchar(10),
seqno int,
[date] date
)

declare @tbl2 table (
item varchar(10),
wt varchar(10),
seqno int,
[date] date
)

insert into @tbl1
values
('item1', 'wt1', 1, '5/30/2012'),
('item2', 'wt2', 1, '5/30/2012'),
('item3', 'wt3', 1, '5/30/2012'),
('item4', 'wt4', 2, '5/30/2012'),
('item5', 'wt5', 2, '5/30/2012'),
('item6', 'wt6', 1, '5/30/2012')

insert into @tbl2
values
('item1', 'wt1', 1, '5/29/2012'),
('item2', 'wt2', 1, '5/29/2012'),
('item3', 'wt3', 1, '5/29/2012'),
('item4', 'wt4', 2, '5/29/2012'),
('item5', 'wt5', 2, '5/29/2012'),
('item6', 'wt6', 1, '5/29/2012'),
('item1', 'wt1', 1, '5/28/2012'),
('item2', 'wt2', 1, '5/28/2012'),
('item3', 'wt3', 1, '5/28/2012'),
('item4', 'wt4', 2, '5/28/2012'),
('item5', 'wt5', 2, '5/28/2012'),
('item6', 'wt6', 1, '5/28/2012'),
('item1', 'wt1', 1, '5/27/2012'),
('item2', 'wt2', 1, '5/27/2012'),
('item3', 'wt3', 1, '5/27/2012'),
('item4', 'wt4', 2, '5/27/2012'),
('item5', 'wt5', 2, '5/27/2012'),
('item6', 'wt6', 1, '5/27/2012')[/CODE][SOAPBOX]You should be supplying the above SQL scriptlet. It makes it easier for people to help you.[/SOAPBOX][CODE];with LatestHistory
as(
select a.item, a.wt, a.seqno
from (
select item, wt, seqno, row_number() over(partition by item, seqno order by [date] DESC) rn
from @tbl2
) a
where a.rn = 1
)
select
today.item, today.wt, today.seqno, history.wt
from
@tbl1 today
inner join
LatestHistory history
on today.item = history.item
and today.seqno = history.seqno[/CODE]

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 12:34:54

[code]
SELECT t1.*,t2.[LatestInsertedDate]
FROM @tbl1 t1
CROSS APPLY (SELECT MAX([date]) AS LatestInsertedDate
FROM @tbl2
WHERE item = t1.item
AND wt = t1.wt
AND [seq no] = t1.[seq no]
)t2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources