Geek Answers Handbook

Ignore null values ​​when using SQL Server 2012 Last_Value ()

I am using SQL Server 2012 and have a table of values ​​that looks like this. It is filled with event data.

FldType Date                    Price   Size
--------------------------------------------
2       2012-08-22 00:02:01     9140    1048
0       2012-08-22 00:02:02     9140    77
1       2012-08-22 00:02:03     9150    281
2       2012-08-22 00:02:04     9140    1090
0       2012-08-22 00:02:05     9150    1
1       2012-08-22 00:02:06     9150    324
2       2012-08-22 00:02:07     9140    1063

I would like to track the last value for each of the three types of fields (0,1,2) so that the final result looks as follows.

Date                Price0  Size0   Price1  Size1   Price2  Size2
-----------------------------------------------------------------
2012-08-22 00:02:01  NULL   NULL    NULL    NULL    9140    1048
2012-08-22 00:02:02  9140   77      NULL    NULL    9140    1048
2012-08-22 00:02:03  9140   77      9150    281     9140    1048
2012-08-22 00:02:04  9140   77      9150    281     9140    1090
2012-08-22 00:02:05  9150   1       9150    281     9140    1090
2012-08-22 00:02:06  9150   1       9150    324     9140    1090
2012-08-22 00:02:07  9150   1       9150    324     9140    1063

Unfortunately, it does not ignore subsequent null values, so I get this instead.

Date                Price0  Size0   Price1  Size1   Price2  Size2
-----------------------------------------------------------------
2012-08-22 00:02:01  NULL   NULL    NULL    NULL    9140    1048
2012-08-22 00:02:02  9140   77      NULL    NULL    NULL    NULL    
2012-08-22 00:02:03  NULL   NULL    9150    281     NULL    NULL
2012-08-22 00:02:04  NULL   NULL    NULL    NULL    9140    1090
2012-08-22 00:02:05  9150   1       NULL    NULL    NULL    NULL    
2012-08-22 00:02:06  NULL   NULL    9150    324     NULL    NULL    
2012-08-22 00:02:07  NULL   NULL    NULL    NULL    9140    1063

My current request looks like this:

SELECT [Date],
    LAST_VALUE(Price0) OVER (PARTITION BY FldType ORDER BY [Date] ) AS Price0,
    LAST_VALUE(Size0) OVER (PARTITION BY FldType ORDER BY [Date]) AS Size0,
    LAST_VALUE(Price1) OVER (PARTITION BY FldType ORDER BY [Date] ) AS Price1,
    LAST_VALUE(Size1) OVER (PARTITION BY FldType ORDER BY [Date]) AS Size1,
    LAST_VALUE(Price2) OVER (PARTITION BY FldType ORDER BY [Date] ) AS Price2,
    LAST_VALUE(Size2) OVER (PARTITION BY FldType ORDER BY [Date]) AS Size2
FROM ( 
SELECT FldType, [Date], Price, Size,
    CASE WHEN FldType = 0 THEN Price END as Price0,
    CASE WHEN FldType = 0 THEN Size END as Size0,
    CASE WHEN FldType = 1 THEN Price END as Price1,
    CASE WHEN FldType = 1 THEN Size END as Size1,
    CASE WHEN FldType = 2 THEN Price END as Price2,
    CASE WHEN FldType = 2 THEN Size END as Size2
FROM [RawData].[dbo].[Events]   
) as T1
ORDER BY [Date]

Is there a way for SQL Server 2012 to ignore null values ​​when determining the last value? Or is there a better approach not using a function Last_Value()?

To summarize, I am trying to achieve two goals.

  • Divide the columns Priceand Sizeinto 6 columns (2 columns x 3 field types)
  • .

.

+3
1

, LAST_VALUE, PIVOT.

, , . , , .

DECLARE @source TABLE (FldType int, DateCol DateTime, Price int, Size int);

INSERT @source VALUES
    (2, '2012-08-22 00:02:01', 9140, 1048),(0, '2012-08-22 00:02:02', 9140, 77),
    (1, '2012-08-22 00:02:03', 9150, 281),(2, '2012-08-22 00:02:04', 9140, 1090),
    (0, '2012-08-22 00:02:05', 9150, 1),(1, '2012-08-22 00:02:06', 9150, 324),
    (2, '2012-08-22 00:02:07', 9140, 1063);


SELECT
    S.DateCol, Xp0.Price0, Xs0.Size0, Xp1.Price1, Xs1.Size1, Xp2.Price2, Xs2.Size2
FROM
    @source S
    OUTER APPLY
    (SELECT TOP 1 S0.Price AS Price0 FROM @source S0 WHERE S0.FldType = 0 AND S0.DateCol <= S.DateCol ORDER BY S0.DateCol DESC) Xp0
    OUTER APPLY
    (SELECT TOP 1 S1.Price AS Price1 FROM @source S1 WHERE S1.FldType = 1 AND S1.DateCol <= S.DateCol ORDER BY S1.DateCol DESC) Xp1
    OUTER APPLY
    (SELECT TOP 1 S2.Price AS Price2 FROM @source S2 WHERE S2.FldType = 2 AND S2.DateCol <= S.DateCol ORDER BY S2.DateCol DESC) Xp2
    OUTER APPLY
    (SELECT TOP 1 S0.Size AS Size0 FROM @source S0 WHERE S0.FldType = 0 AND S0.DateCol <= S.DateCol ORDER BY S0.DateCol DESC) Xs0
    OUTER APPLY
    (SELECT TOP 1 S1.Size AS Size1 FROM @source S1 WHERE S1.FldType = 1 AND S1.DateCol <= S.DateCol ORDER BY S1.DateCol DESC) Xs1
    OUTER APPLY
    (SELECT TOP 1 S2.Size AS Size2 FROM @source S2 WHERE S2.FldType = 2 AND S2.DateCol <= S.DateCol ORDER BY S2.DateCol DESC) Xs2
ORDER BY
    DateCol;

ETL, .

+4

More articles:


All Articles