April 18, 2013 at 2:46 pm
Hi all,
I have a documents database that has 2 tables, the main table contains the documents general information and the other tracks changes to the documents.
I need to know, for each document, the oldest code status from the 2 table based on the InsertedDate.
Note: Every time a document is renewed a new line is inserted in the main table... but the document name is the same. At the same time a new status is created in the 2nd table.
Here's the example:
--remove temp table if necessary
IF OBJECT_ID('tempdb..#temp_main') IS NOT NULL
DROP TABLE #temp_main
IF OBJECT_ID('tempdb..#temp_status') IS NOT NULL
DROP TABLE #temp_status
--make temp_main
CREATE TABLE #temp_main (
Uniq_ID int NOT NULL,
DocCode char(32) NOT NULL,
EffectiveDate datetime NULL
)
--make temp_status
CREATE TABLE #temp_status (
Uniq_ID int NOT NULL,
CodeStatus char(2) NOT NULL,
InsertedDate datetime NULL
)
--poplulate temp tables
INSERT INTO #temp_main (Uniq_ID, DocCode, EffectiveDate) VALUES (65977,123456,'2012-07-19 00:00:00.000')
INSERT INTO #temp_main (Uniq_ID, DocCode, EffectiveDate) VALUES (65977,123456,'2012-07-19 00:00:00.000')
INSERT INTO #temp_main (Uniq_ID, DocCode, EffectiveDate) VALUES (65977,123456,'2012-07-19 00:00:00.000')
INSERT INTO #temp_main (Uniq_ID, DocCode, EffectiveDate) VALUES (66065,123456,'2013-07-18 00:00:00.000')
INSERT INTO #temp_status (Uniq_ID, CodeStatus, InsertedDate) VALUES (65977,'N','2012-07-19 18:23:44.637')
INSERT INTO #temp_status (Uniq_ID, CodeStatus, InsertedDate) VALUES (65977,'E','2012-07-19 18:36:16.027')
INSERT INTO #temp_status (Uniq_ID, CodeStatus, InsertedDate) VALUES (65977,'E','2012-07-25 15:44:47.553')
INSERT INTO #temp_status (Uniq_ID, CodeStatus, InsertedDate) VALUES (66065,'R','2013-04-18 16:01:02.510')
select a.Uniq_ID, a.DocCode, a.EffectiveDate,
b.Uniq_ID, b.CodeStatus, b.InsertedDate
from #temp_main as a
left join #temp_status as b on b.Uniq_ID = a.Uniq_ID
group by a.Uniq_ID, a.DocCode, a.EffectiveDate,
b.Uniq_ID, b.CodeStatus, b.InsertedDate
Uniq_ID DocCode EffectiveDate Uniq_ID CodeStatus InsertedDate
----------- -------------------------------- ----------------------- ----------- ---------- -----------------------
65977 123456 2012-07-19 00:00:00.000 65977 E 2012-07-19 18:36:16.027
65977 123456 2012-07-19 00:00:00.000 65977 E 2012-07-25 15:44:47.553
65977 123456 2012-07-19 00:00:00.000 65977 N 2012-07-19 18:23:44.637
66065 123456 2013-07-18 00:00:00.000 66065 R 2013-04-18 16:01:02.510
(4 row(s) affected)
What I want to achieve is :
Uniq_ID DocCode EffectiveDate Uniq_ID CodeStatus InsertedDate
----------- -------------------------------- ----------------------- ----------- ---------- -----------------------
65977 123456 2012-07-19 00:00:00.000 65977 N 2012-07-19 18:23:44.637
66065 123456 2013-07-18 00:00:00.000 66065 R 2013-04-18 16:01:02.510
Thank you all for your time!!
April 18, 2013 at 3:10 pm
Hi,
you can try this.
select a.Uniq_ID, a.DocCode, a.EffectiveDate,
b.Uniq_ID, b.CodeStatus, b.InsertedDate
from #temp_main as a
left join #temp_status as b on b.Uniq_ID = a.Uniq_ID
inner join (select uniq_id, min(InsertedDate) as InsertedDate from #temp_status group by uniq_id) x
on a.Uniq_ID = x.Uniq_ID and b.InsertedDate=x.InsertedDate
group by a.Uniq_ID, a.DocCode, a.EffectiveDate,
b.Uniq_ID, b.CodeStatus, b.InsertedDate
April 18, 2013 at 3:17 pm
That worked!
Thank you very much!!
April 18, 2013 at 3:36 pm
You could also do this with a CTE and Row_Number to eliminate the subquery.
;with cte as
(
select a.Uniq_ID, a.DocCode, a.EffectiveDate, b.CodeStatus, b.InsertedDate, ROW_NUMBER() OVER(partition by a.Uniq_ID order by InsertedDate) as RowNum
from #temp_main as a
left join #temp_status as b on b.Uniq_ID = a.Uniq_ID
)
select * from cte where RowNum = 1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply