Sql-server – SQL problem with checking next and previous rows

sql-server-2008

I have a list and the returned table looks like this. I took the preview of only one car but there are many more.

enter image description here

What I need to do now is check that the current KM value is larger then the previous and smaller then the next. If this is not the case I need to make a field called Trustworthy and should fill it with either 1 or 0 (true/ false).

The result that I have so far is this:

enter image description here

validKMstand and validkmstand2 are how I calculate it. It did not work in one list so that is why I separated it.

In both of my tries my code does not work.

15756 should be 0 in validkmstand2 as it is larger than the next value.
15757 should be 0 in validKMstand as it is smaller than the previous value

Here is the code that I have so far.

 FullList as (
SELECT
    *
FROM
eMK_Mileage as Mileage
)


, ValidChecked1 as (
SELECT 
        UL1.*,
        CASE WHEN EXISTS(
            SELECT TOP(1)UL2.* 
            FROM FullList AS UL2
            WHERE
                UL2.FK_CarID = UL1.FK_CarID AND
                UL1.KM_Date > UL2.KM_Date AND
                UL1.KM > UL2.KM
            ORDER BY UL2.KM_Date DESC
        )
        THEN 1
        ELSE 0
        END AS validkmstand
FROM FullList as UL1
)

, ValidChecked2 as (
SELECT 
        List1.*,
        (CASE   WHEN List1.KM > ulprev.KM
                THEN 1
                ELSE 0
                END
        ) AS validkmstand2
FROM ValidChecked1 as List1 outer apply
    (SELECT TOP(1)UL3.* 
            FROM ValidChecked1 AS UL3
            WHERE
                UL3.FK_CarID = List1.FK_CarID AND
                UL3.KM_Date <= List1.KM_Date AND
                List1.KM > UL3.KM
            ORDER BY UL3.KM_Date DESC) ulprev
)

SELECT * FROM ValidChecked2 order by FK_CarID, KM_Date

Best Answer

How about a rewrite of it?

;with eMK_Mileage
AS (SELECT 15755 AS ID, '8E79F' AS FK_CarID, 69646 AS KM , '20140716' AS KM_Date UNION ALL
SELECT 15756,'8E79F' , 70393 , '20140726' UNION ALL
SELECT 15757,'8E79F' , 58258 , '20140822' UNION ALL
SELECT 15758,'8E79F' , 72055 , '20140822')
,FullList 
AS (
 SELECT  * FROM eMK_Mileage as Mileage
)
-- FINAL SELECT
SELECT
*
,CASE WHEN FL.KM < ISNULL(P.KM_PREV,FL.KM) THEN 0 ELSE 1 END AS validKMstand
,CASE WHEN FL.KM > ISNULL(N.KM_NEXT,FL.KM) THEN 0 ELSE 1 END AS validKMstand2   
FROM 
FullList AS FL
OUTER APPLY 
--PREVIOUS ROW
(SELECT TOP 1 
        KM AS KM_PREV
    FROM FullList AS PREV
    WHERE
        FL.FK_CarID = PREV.FK_CarID
        AND FL.KM_Date>=PREV.KM_Date
        AND FL.ID>PREV.ID
    ORDER BY 
        KM_Date DESC, ID DESC
)AS P
--NEXT ROW
OUTER APPLY 
(SELECT TOP 1 
        KM AS KM_NEXT
    FROM FullList AS NXT
    WHERE
        NXT.FK_CarID = FL.FK_CarID
        AND NXT.KM_Date>=FL.KM_Date
        AND NXT.ID > FL.ID
    ORDER BY 
        KM_Date ASC, ID ASC
)AS N
ORDER BY ID,KM_Date

the output, for my sample is this:

ID  FK_CarID    KM  KM_Date KM_PREV KM_NEXT validKMstand    validKMstand2
15755   8E79F   69646   20140716    NULL    70393   1   1
15756   8E79F   70393   20140726    69646   58258   1   0
15757   8E79F   58258   20140822    70393   72055   0   1
15758   8E79F   72055   20140822    58258   NULL    1   1

Related Solutions

Sql-server – How to calculate values based on the previous row after skipping the first 12 rows

So this isn't a great answer, this is kind of a starting answer for somebody else to take on and refine this better. But I'll make a stab at it.

First I have a question: Are you trying to retain this in a view? I don't think you can for what you're wanting to do, it's kinda complicated, so let's examine the operations that you need to do to actually do what you want.

You stated that you want the first 12 rows to be static every time, and they should always have their last column set as NULL, and the others should retain their value. So that's a business rule that we need to encode in SQL. But before we encode this as a rule, let's ask if there's a way to ENSURE that those 12 rows are the RIGHT rows every time. If we can make that assumption, then we can do this as part of the next step.

You're next requirement is to do a calculation on each row with the previous row. Since the first 12 rows are static (and I presume not calculated) then we don't have to ask "what about the first row". So the easiest way to do calculations on the previous row is to assign a rownum to each row, then use the rownum ID in a comparison. This meshes with the previous requirement.

So we should start by doing our select and assigning a rownum as well, like this:

SELECT     
    ROW_NUMBER() OVER (ORDER BY in.I_Date) AS rownum,
    in.I_Date  ,--Date
    in.I_O_P   ,--Money
    in.I_O_H   ,--Money
    in.I_O_L   ,--Money
    in.I_C_O   ,--Money 
    c.AMPS12_C  --Money
    CAST(0.0 AS Money) AS C12WR
FROM
    dbo.IC_Raw_In in
INNER JOIN 
    dbo.AMPS12_C c ON in.I_Serial = c.i_serial

But for the way I would do this, I would funnel these values into a temp table, and then use that to work out what I need. That way you can just refer to the columns in subsequent calls, like this:

UPDATE t 
SET C12WR = NULL
FROM temptable t
WHERE t.rownum < 12 -- see how we set the values = null here?

UPDATE t 
SET C12WR = 510.3958
FROM temptable t
WHERE t.rownum = 12 -- see how we set the value to something static? 
                    -- If this were a stored procedure we could use a value passed in here

and then we continue with:

UPDATE t 
SET C12WR = ( ( t2.C12WR * 11.0 ) + t.I_C_O ) / 12.0
FROM temptable t
INNER JOIN temptable t2 ON t.rownum = (t2.rownum - 1) -- this let's us get the previous row
WHERE t.rownum > 12

Using this logic: After the 13th row, the C12WR column = (prevrow.C12WR * 11 + currow.I_C_O Column) / 12

And then you would just return the values that you wanted from the temptable.

Notice: the things I left off. I did not define the temp table, I did not get rid of the temptable. I did not use appropriate syntax for the temptable addressing. I did not validate anything. I presumed that this was going to be used in a stored procedure. I did not illustrate how to use the static value as a stored procedure passed parameter.

Hope this helps. Hope someone else helps make this a better answer ;)

Sql-server – Performance differences between executing via Linked Server vs. SSMS

The difference between SSMS and .Net client performance could well be down to how plans are cached. Part of the cache key is the session options - for me, at least, SSMS sets ARITHABORT, whereas .Net SqlClient does not, by default. You sound like you're ending up in the situation where a bad plan is cached vs. one cache key, and a good plan vs. another.

Best Answer

Related Solutions

Sql-server – How to calculate values based on the previous row after skipping the first 12 rows

Sql-server – Performance differences between executing via Linked Server vs. SSMS

Related Question