WITH Cte AS(
SELECT *,
rn = ROW_NUMBER() OVER(
PARTITION BY cdt, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, cdt), 0)
ORDER BY cdt DESC
)
FROM brands
)
SELECT
brandID, brandname, cdt, udt, brandstatus, addedby
FROM Cte
WHERE rn = 1 and cdt between '2013-11-01 00:00:00' and '2013-11-16 00:00:00' ORDER BY brandId
my table is like below:
ID brandname cdt udt brandstatus addedby
1 khasim 2013-11-01 19:14:18.120 2013-11-15 19:14:18.123 1 1
2 khasim 2013-11-01 19:14:18.121 2013-11-15 19:14:18.123 1 1
3 khasim 2013-11-01 19:14:18.122 2013-11-15 19:14:18.123 1 1
4 khasim 2013-11-01 19:14:18.123 2013-11-15 19:14:18.123 1 1
5 khasim 2013-11-02 19:17:57.700 2013-11-15 19:17:57.700 1 2
6 tanveer 2013-11-03 19:18:05.947 2013-11-15 19:18:05.947 1 2
7 abcdef 2013-11-04 20:50:06.783 2013-11-15 20:50:06.787 1 4
I want to get every n minute data in the range of x hours/days. if n=10 it should return the highest value of 10 minute interval between x=5 hours.
