SQL multiple rows calculate

I have a table in SQL Server and I am trying to calculate if a number is bigger than a selected row, but only if the rows that exist are greater than 1.

I’ve looked online and looked into Union, Intersect and Exist but cannot see a way to do it and of course I may have to program the calculation in C++ but would rather do it in SQL as it will be faster.

The example is as follows: the table lists a number of horse races and data (needs to sorted by date and time in the selection):

Date        Time        Course      OF  Win
-------------------------------------------
2018-10-16  15:45:00    Huntingdon  5   LC        
2018-10-16  15:45:00    Huntingdon  3   W         
2018-10-16  16:10:00    Punchtown   1   LC        
2018-10-16  16:10:00    Punchtown   2   W         
2018-10-16  16:20:00    Huntingdon  3   LC        
2018-10-16  16:20:00    Huntingdon  2   W         
2018-10-16  16:30:00    Hereford    5   W         
2018-10-16  16:30:00    Hereford    4   LC        
2018-10-16  16:45:00    Punchtown   3   W         

In the above data I wouldn’t be interested in the last record as only one for that date and time exists. From the other four races I would like to calculate how many races that have a ‘W’, have a ‘OF’ value greater than the ‘LC’ line. The output would count how many races have this condition.

A further complication is there may be more than two records for that date and time. I’ve looked at this for two days now without success so just before I do program it I thought I’d ask.

SG

Answer

Based on your sample data, the following should do the trick…

IF OBJECT_ID('tempdb..#TestData', 'U') IS NULL 
BEGIN   -- DROP TABLE #TestData;
    CREATE TABLE #TestData (
        [Date] DATE NOT NULL,
        [Time] TIME(0) NOT NULL,
        Course VARCHAR(20) NOT NULL,
        [OF] INT NOT NULL,
        Win CHAR(2) NOT NULL 
        );

    INSERT #TestData (Date, Time, Course, [OF], Win)
    VALUES
    ('2018-10-16', '15:45:00', 'Huntingdon', 5, 'LC'),       
    ('2018-10-16', '15:45:00', 'Huntingdon', 3, 'W'),       
    ('2018-10-16', '16:10:00', 'Punchtown ', 1, 'LC'),       
    ('2018-10-16', '16:10:00', 'Punchtown ', 2, 'W'),       
    ('2018-10-16', '16:20:00', 'Huntingdon', 3, 'LC'),       
    ('2018-10-16', '16:20:00', 'Huntingdon', 2, 'W'),       
    ('2018-10-16', '16:30:00', 'Hereford  ', 5, 'W'),       
    ('2018-10-16', '16:30:00', 'Hereford  ', 4, 'LC'),       
    ('2018-10-16', '16:45:00', 'Punchtown ', 3, 'W')
END;

--========================================================

WITH
    cte_LagVal AS (
        SELECT 
            td.Date,
            td.Time,
            td.Course,
            td.[OF],
            td.Win,
            LC_OF = LAG(td.[OF], 1) OVER (PARTITION BY td.Date, td.Time, td.Course ORDER BY td.Win)
        FROM
            #TestData td
        )
SELECT 
    lv.Date,
    lv.Time,
    lv.Course,
    lv.[OF],
    lv.Win,
    lv.LC_OF
FROM
    cte_LagVal lv
WHERE 
    lv.LC_OF IS NOT NULL;