time flag 2018-04-18 16:24:08.0000000 0 2018-04-18 16:14:08.0000000 1 2018-04-18 16:04:08.0000000 0 2018-04-18 15:54:08.0000000 0 2018-04-18 15:44:08.0000000 1 2018-04-18 15:34:08.0000000 1 2018-04-18 15:24:08.0000000 0 2018-04-18 15:04:08.0000000 1 2018-04-18 14:54:08.0000000 1 2018-04-18 14:44:08.0000000 0
how to get first occurrence of flag 1 record(only one record that has occurred 1 st time based on time) and the last occurrence of flag 0 record(only one record that has occurred last) Result should be : 2018-04-18 14:54:08.0000000 1 2018-04-18 16:24:08.0000000 0 (two records) or else 2018-04-18 14:54:08.0000000 1 2018-04-18 16:24:08.0000000 0(single record)
SELECT CASE WHEN Flag = 1 THEN MAX([Time]) ELSE MIN([Time]) END AS [Time], Flag FROM MyTable GROUP BY Flag
SELECT * FROM TableNameGoesHere t1 WHERE t1.Flag = 1 AND NOT EXISTS ( SELECT 1 FROM TableNameGoesHere t2 WHERE t2.Flag = 1 AND t2.Time < t1.Time)
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)