CODEWITHSUNDEEP
sql-server
B-Wint
B-Wint

Reputation: 177

How to combine fields from 2 columns to create a "matrix"?

I have a logging table in my application that only logs changed data, and leaves the other columns NULL. What I'm wanting to do now is create a view that takes 2 of those columns (Type and Status), and create a resultset that returns the Type and Status on the entry of that log row, assuming that either one or both columns could be null.

For example, with this data:

Type Status AddDt

A    1      7/8/2013
NULL 2      7/7/2013
NULL 3      7/6/2013
NULL NULL   7/5/2013
B    NULL   7/4/2013
C    NULL   7/3/2013
C    4      7/2/2013

produce the resultset:
Type Status AddDt
A    1      7/8/2013
A    2      7/7/2013
A    3      7/6/2013
A    3      7/5/2013
B    3      7/4/2013
C    3      7/3/2013
C    4      7/2/2013

From there I'm going to figure out the first time in these results the Type and Status meet certain conditions, such as a Type of B and Status 3 (7/4/2013) and ultimately use that date in a calculation, so performance is a huge issue with this.

Here's what I was thinking so far, but it doesn't get me where I need to be:

SELECT
Type.TypeDesc
, Status.StatusDesc
, *
FROM
jw50_Item c
OUTER APPLY (SELECT TOP 10000 * FROM jw50_ItemLog csh WHERE csh.ItemID = c.ItemID AND csh.StatusCode = 'OPN' ORDER BY csh.AddDt DESC) [Status]
OUTER APPLY (SELECT TOP 10000 * FROM jw50_ItemLog cth WHERE cth.ItemID = c.ItemID AND cth.ItemTypeCode IN ('F','FG','NG','PF','SXA','AB') ORDER BY cth.AddDt DESC) Type
WHERE
c.ItemID = @ItemID

So with the help provided below, I was able to get where I needed. Here is my final solution:

SELECT 
OrderID
, CustomerNum
, OrderTitle
, ItemTypeDesc
, ItemTypeCode
, StatusCode
, OrdertatusDesc 
FROM 
    jw50_Order c1
    OUTER APPLY (SELECT TOP 1 [DateTime] FROM
                    (SELECT c.ItemTypeCode,     c.OrderStatusCode, c.OrderStatusDt as [DateTime] FROM jw50_Order c     WHERE c.OrderID = c1.OrderID
                    UNION
                    select (select top 1     c2.ItemTypeCode
                            from     jw50_OrderLog c2
                            where     c2.UpdatedDt >= c.UpdatedDt and c2.ItemTypeCode is not null and     c2.OrderID = c.OrderID
                            order by     UpdatedDt DESC
                           ) as type,
                           (select top 1     c2.StatusCode
                            from     jw50_OrderLog c2
                            where     c2.UpdatedDt >= c.UpdatedDt and c2.StatusCode is not null and     c2.OrderID = c.OrderID
                            order by     UpdatedDt DESC
                           ) as status,
                           UpdatedDt as     [DateTime]

                    from jw50_OrderLog c
                    where c.OrderID =     c1.OrderID AND (c.StatusCode IS NOT NULL OR c.ItemTypeCode IS NOT     NULL)
                    ) t
                    WHERE t.ItemTypeCode IN     ('F','FG','NG','PF','SXA','AB') AND t.StatusCode IN ('OPEN')
                    order by [DateTime]) quart
WHERE quart.DateTime <= @FiscalPeriod2 AND c1.StatusCode =     'OPEN'
Order By c1.OrderID

The union is to bring in the current data in addition to the log table data to create the resultset, since the current data maybe what meets the conditions required. Thanks again for the help guys.

Upvotes: 0

Views: 120

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270683

Here is an approach that uses correlated subqueries:

select (select top 1 c2.type
        from jw50_Item c2
        where c2.AddDt >= c.AddDt and c2.type is not null
        order by AddDt
       ) as type,
       (select top 1 c2.status
        from jw50_Item c2
        where c2.AddDt >= c.AddDt and c2.status is not null
        order by AddDt
       ) as status,
       (select AddDt
from jw50_Item c

If you have indexes on jw50_item(AddDt, type) and jw50_item(AddDt, status), then the performance should be pretty good.

Upvotes: 1

gaborsch
gaborsch

Reputation: 15758

I suppose you want to "generate a history": for those dates that has some data missing, the next available data should be set.

Something similar should work:

Select i.AddDt, t.Type, s.Status
from Items i
join Items t on (t.addDt = 
    (select min(t1.addDt) 
     from Items t1 
     where t1.addDt >= i.addDt 
       and t1.Type is not null))
join Items s on (s.addDt = 
    (select min(s1.addDt) 
      from Items s1 
     where s1.addDt >= i.addDt
       and s1.status is not null))

Actually I'm joining the base table to 2 secondary tables and the join condition is that we match the smallest row where the respective column in the secondary table is not null (and of course smaller than the current date).

I'm not absolutely sure that it will work, since I don't have an SQL Server in front of me but give it a try :)

Upvotes: 0

Related Questions