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
Advertisement
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;