How do I "flatten" this? 1

[OverDraft]

I have 3 tables with data structures as shown below:

TBL-1 (contains some current data about tasks)
TskID TskDescription AreaID
9345 ItemDescription-1 Area-1
4940 ItemDescription-2 Area-2

TBL-2 (contains descriptions of what area is responsible for a task)
AreaID AreaDescription
Area-1 AreaDescription-1
Area-2 AreaDescription-2


TBL-3 (contains trans history about tasks)
TskID FromState ToState TransDate
9345 State-A State-B Date-1
9345 State-B State-C Date-2
9345 State-C State-D Date-3
4990 State-A State-B Date-5
4940 State-B State-C Date-6
4940 State-C State-B Date-7
4940 State-B State-C Date-8
4940 State-C State-D Date-9


What I need is a single row returned for each of the two TskIDs which contain the following values:

9345, ItemDescription-1, AreaDescription-1, Date-2, Date-3
4940, ItemDescription-2, AreaDescription-2, Date-8, Date-9

The problem I'm having concerns TBL-3. A task can iterate through a state more than once (as seen in task 4940, ToState B&C). I am only interested in retrieving the most recent date of ToState values C and D.

How do I create a single row showing the max(TransDate) for ToState values of C and D for a particular TskID?

 

[simian336]

is TransDate an actual date?

Simi

 

[OverDraft]

Yes, in the table I'm actually processing Transdate contains real date values.

 

[Qik3Coder]

Overdraft,

You're going need to run a sub query on table 3 to order the max's to the top, then do a row number to get the first one for that particular "state"
I recommend putting that logic in a table UDF, so you can reuse it.

After that you're going to wind up with something similar to this: thread183-1649930
Start working on it, and let us know when you get stuck.

Lodlaiden

You've got questions and source code. We want both!

 

[OverDraft]

Well, I played around with it some and I certainly got stuck. That link and some additional searches gave me lots of ideas to try but I haven't been able to get any of them to work. I have something that will assign a RowNum to each row in the third table but I seem to be missing the point about how to pick the rows I want.

Here's the setup I'm using to model this along with some SQL to assign a row number.

--Sample tables & data for testing

DECLARE @TBL1 TABLE
(
TskID char(4)
,TskDescription char(17)
,AreaID char(6)
);
DECLARE @TBL2 TABLE
(
AreaID char(6)
,AreaDescription char(17)
);
DECLARE @TBL3 TABLE
(
TskID char(4)
,FromState char(10)
,ToState char(10)
,TransDate date
);

INSERT INTO @Tbl1 (TskID, TskDescription, AreaID)
Values ('9345', 'ItemDescription-1', 'Area-1');
INSERT INTO @Tbl1 (TskID, TskDescription, AreaID)
Values ('4940', 'ItemDescription-2', 'Area-2');
INSERT INTO @Tbl2 (AreaID, AreaDescription)
Values ('Area-1', 'AreaDescription-1');
INSERT INTO @Tbl2 (AreaID, AreaDescription)
Values ('Area-2', 'AreaDescription-2');
INSERT INTO @Tbl3 (TskID, FromState, ToState, TransDate)
Values ('9345', 'State-A', 'State-B', '2011-01-01');
INSERT INTO @Tbl3 (TskID, FromState, ToState, TransDate)
Values ('9345', 'State-B', 'State-C', '2011-02-01');
INSERT INTO @Tbl3 (TskID, FromState, ToState, TransDate)
Values ('9345', 'State-C', 'State-D', '2012-03-01');
INSERT INTO @Tbl3 (TskID, FromState, ToState, TransDate)
Values ('4940', 'State-A', 'State-B', '2012-05-01');
INSERT INTO @Tbl3 (TskID, FromState, ToState, TransDate)
Values ('4940', 'State-B', 'State-C', '2012-06-01');
INSERT INTO @Tbl3 (TskID, FromState, ToState, TransDate)
Values ('4940', 'State-C', 'State-B', '2012-07-01');
INSERT INTO @Tbl3 (TskID, FromState, ToState, TransDate)
Values ('4940', 'State-B', 'State-C', '2012-08-01');
INSERT INTO @Tbl3 (TskID, FromState, ToState, TransDate)
Values ('4940', 'State-C', 'State-D', '2012-09-01');

;with Data as
(select row_number() over (order by TransDate desc) as RowNum
,TskID
,ToState
,TransDate

FROM @TBL3
group by TskID, ToState, TransDate
)

select *
from Data as TblA
inner join Data as TblB
on TblA.RowNum = TblB.RowNum

 

[JonFer]

See what this returns (using Partition By with Row_Number Over() ) when run with your history table.

Select A.*
From 
( Select *, Row_Number() Over([b]Partition By TaskID [/b] Order By TaskID, TransDate Desc) as RowNum
From TBL3 ) as A
Where RowNum < 3

 

[OverDraft]

Sorry for the delayed response but I was out of the office for a while and I'm still playing "catch-up". I added the code Jonfer suggested and it certainly returns the 4 rows with the data I need. But I'm still having trouble figuring out how to "flatten" those into just 2 rows - one for each value of TskID. I know the resulting rows I want:

TskID State-C State-C Date State-D State-D Date
4940 State-C 2012-08-01 State-D 2012-09-01
9345 State-C 2011-02-01 State-D 2012-03-01

I'm just not sure how to get there. . .

 

[drlex]

On your test data, stated requirements and amending JonFer's code:

;with cte as (
Select A.* From ( Select *, Row_Number() Over(Partition By TskID  Order By TskID, TransDate Desc) as RowNum From @TBL3 ) as A Where RowNum < 3
)

Select T1.TskID, T1.FromState, T2.TransDate, T1.ToState, T1.TransDate from cte T1 inner join cte T2 on T1.TskID = T2.TskID and T1.RowNum = 1 and T2.RowNum = 2

soi là, soi carré

 

[OverDraft]

Thanks drlex! That's exactly what I needed. I think I tried all of those elements at one time or another (creating a row #, CTE, inner joins, etc). But I just hadn't figured out how to put it all together. As with most things, the solution isn't even all that complicated once you see it in front of ur face. Thanks again.

 

[OverDraft]

So, just for the sake of wrapping this up, here's the final SQL I used. This joins 2 regular tables with the results of a Common Table Expression which "flattened" the multiple rows from a 3rd table containing transaction log data.

--Sample tables & data for testing

DECLARE @TBL1 TABLE
(
TskID char(4)
,TskDescription char(17)
,AreaID char(6)
);
DECLARE @TBL2 TABLE
(
AreaID char(6)
,AreaDescription char(17)
);
DECLARE @TBL3 TABLE
(
TskID char(4)
,FromState char(10)
,ToState char(10)
,TransDate date
);

INSERT INTO @Tbl1 (TskID, TskDescription, AreaID)
Values ('9345', 'TskDescription1', 'Area1');

INSERT INTO @Tbl1 (TskID, TskDescription, AreaID)
Values ('4940', 'TskDescription2', 'Area2');

INSERT INTO @Tbl2 (AreaID, AreaDescription)
Values ('Area1', 'AreaDescription1');

INSERT INTO @Tbl2 (AreaID, AreaDescription)
Values ('Area2', 'AreaDescription2');

INSERT INTO @Tbl3 (TskID, FromState, ToState, TransDate)
Values ('9345', 'StateA', 'StateB', '2011-01-01');

INSERT INTO @Tbl3 (TskID, FromState, ToState, TransDate)
Values ('9345', 'StateB', 'StateC', '2011-02-01');

INSERT INTO @Tbl3 (TskID, FromState, ToState, TransDate)
Values ('9345', 'StateC', 'StateD', '2012-03-01');

INSERT INTO @Tbl3 (TskID, FromState, ToState, TransDate)
Values ('4940', 'StateA', 'StateB', '2012-05-01');

INSERT INTO @Tbl3 (TskID, FromState, ToState, TransDate)
Values ('4940', 'StateB', 'StateC', '2012-06-01');

INSERT INTO @Tbl3 (TskID, FromState, ToState, TransDate)
Values ('4940', 'StateC', 'StateB', '2012-07-01');

INSERT INTO @Tbl3 (TskID, FromState, ToState, TransDate)
Values ('4940', 'StateB', 'StateC', '2012-08-01');

INSERT INTO @Tbl3 (TskID, FromState, ToState, TransDate)
Values ('4940', 'StateC', 'StateD', '2012-09-01');


-- SQL to join the 1st two tables and "flatten" the transaction
-- date from the 3rd table

;with cteLogTable --(TskID, TskDescription, AreaDescription)
as
(Select A.* From ( Select *, Row_Number() Over(Partition By TskID Order By TskID, TransDate Desc) as RowNum
From @TBL3 ) as A
Where RowNum < 3)

Select T1.TskID
,tbl1.TskDescription
,tbl2.AreaDescription
,t1.FromState
,T2.TransDate as CDate
,T1.ToState
,T1.TransDate as DDate

from @TBL1 as tbl1
,@TBL2 as tbl2
,cteLogTable T1 inner join cteLogTable T2 on T1.TskID = T2.TskID and T1.RowNum = 1 and T2.RowNum = 2

WHERE T1.TskID = tbl1.TskID
and tbl1.AreaID = tbl2.AreaID