April 16, 2010 at 12:30 pm
i have a table :
Level1 Level2 Level2_ID Status Date
1234 345 222 Active 4/10/10
1234 345 111 InActive 4/10/10
1234 346 444 Active 4/8/10
1234 345 111 Active 4/8/10
1234 344 333 Active 4/8/10
using this table as example..on 4/8 level1 part 1234, has level2 parts 333, 111, and 444 assigned to it..on 4/10, level2 parts are changed...111 is turned inactive and 222 is now active...
i want to be able to search by level1...the return the active parts..so, if i search for level1 part 1234, i would get :
Level1 Level2 Level2_ID Status Date
1234 345 222 Active 4/10/10
1234 346 444 Active 4/8/10
1234 344 333 Active 4/8/10
what would be the best way to do this without updating records. i need to have traceability into what changed.
thanks
April 16, 2010 at 1:39 pm
Something like this? I'm not sure if it will meet your requirement but it should get you started...
Side note: please notice how I did the setup of DDL and sample data... Makes it a lot easier to work on...
DECLARE @tbl TABLE
(
Level1 INT,Level2 INT,Level2_ID INT, Status VARCHAR(10),DATE DATETIME
)
INSERT INTO @tbl
SELECT 1234, 345, 222 ,'Active ','4/10/10' UNION ALL
SELECT 1234, 345, 111 ,'InActive ','4/10/10' UNION ALL
SELECT 1234, 346, 444 ,'Active ','4/8/10' UNION ALL
SELECT 1234, 345 ,111 ,'Active ','4/8/10' UNION ALL
SELECT 1234, 344, 333 ,'Active ','4/8/10'
;WITH cte AS
(
SELECT Level1,Level2,MAX(DATE) AS mDate
FROM @tbl
WHERE Status='active'
AND Level1=1234
GROUP BY Level1,Level2
)
SELECT
cte.Level1,cte.Level2,
t.Level2_id,
cte.mDate
FROM cte
INNER JOIN @tbl t
ON cte.Level1=t.Level1
AND cte.Level2=t.Level2
AND cte.mDate=t.Date
WHERE Status='active'
ORDER BY t.date DESC, t.Level2_id DESC
April 16, 2010 at 1:58 pm
Here is an alternative solution:
DECLARE @tbl TABLE
(
Level1 INT,Level2 INT,Level2_ID INT, Status VARCHAR(10),Date DATETIME
)
INSERT INTO @tbl
SELECT 1234, 345, 222 ,'Active ','4/10/10' UNION ALL
SELECT 1234, 345, 111 ,'InActive ','4/10/10' UNION ALL
SELECT 1234, 346, 444 ,'Active ','4/8/10' UNION ALL
SELECT 1234, 345 ,111 ,'Active ','4/8/10' UNION ALL
SELECT 1234, 344, 333 ,'Active ','4/8/10'
;
with LatestProds as (
select
row_number() over (partition by Level1, Level2, Level2_ID order by Date desc) as RowNum,
Level1,
Level2,
Level2_ID,
Status,
Date
from
@tbl
)
select
Level1,
Level2,
Level2_ID,
Status,
Date
from
LatestProds
where
RowNum = 1 and
Status = 'Active'
order by
Date desc,
Level2_ID desc;
April 16, 2010 at 2:08 pm
Did you notice that there were almost 50 people reading your post and actually moved on?
But as soon as there are ready to use sample data available, you already have two solutions...
@Lynn: I'm guessing you decided to post a solution since there were sample data available... Correct me if I'm wrong. 😉
April 16, 2010 at 2:26 pm
lmu92 (4/16/2010)
@dachen:Did you notice that there were almost 50 people reading your post and actually moved on?
But as soon as there are ready to use sample data available, you already have two solutions...
@Lynn: I'm guessing you decided to post a solution since there were sample data available... Correct me if I'm wrong. 😉
@Lutz, Yes, since you took the time to create readily usable sample data and table(s), I decided to take a stab at the problem. As I am currently at work and only taking a quick break (getting tired of reading PDF file documentation, and not finding what I need), I wouldn't have time to create the data myself.
April 16, 2010 at 2:47 pm
thanks for all your help..next time, i'll know to set up a table first...
dan
April 18, 2010 at 7:20 pm
dachen (4/16/2010)
what would be the best way to do this without updating records. i need to have traceability into what changed.
Looks like you are looking to audit changes here. Arguably the simplest and most reliable way would be to use the built-in Change Data Capture or Change Tracking features.
See:
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply