CODEWITHSUNDEEP
sqlsql-server
Y.Dean
Y.Dean

Reputation: 23

How to get the min/max time in continuous times and the count of times in this range?

Date:

2015-04-01 12:00
2015-04-01 11:00
2015-04-01 10:
2015-04-01 09:
2015-04-01 08:00 // <---
2015-04-01 05:00
2015-04-01 04:00
2015-04-01 03:00
2015-04-01 02:00
2015-04-01 01:00 // <---
2015-03-31 22:00
2015-03-31 21:00
2015-03-31 20:00
2015-03-31 19:00 // <---

I want to get this result:

MaxTime          | MinTime          | Count
2015-04-01 12:00 | 2015-04-01 08:00 | 5
2015-04-01 05:00 | 2015-04-01 01:00 | 5
2015-03-31 22:00 | 2015-03-31 19:00 | 4

Upvotes: 0

Views: 84

Answers (2)

uzi
uzi

Reputation: 4146

Try this query

select
    max(dateColumn), min(dateColumn), count(dateColumn)
from (
    select 
        dateColumn, datepart(dayofyear, dateColumn)*24 + datepart(hh, dateColumn) - row_number() over (order by dateColumn) grp
    from
        MyTable
) t
group by grp

Upvotes: 1

Sreenu131
Sreenu131

Reputation: 2516

Sample Data

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp

;With cte(Dates)
AS
(
SELECT '2015-04-01 12:00' UNION ALL
SELECT '2015-04-01 11:00' UNION ALL
SELECT '2015-04-01 10:00' UNION ALL
SELECT '2015-04-01 09:00' UNION ALL
SELECT '2015-04-01 08:00' UNION ALL
SELECT '2015-04-01 05:00' UNION ALL
SELECT '2015-04-01 04:00' UNION ALL
SELECT '2015-04-01 03:00' UNION ALL
SELECT '2015-04-01 02:00' UNION ALL
SELECT '2015-04-01 01:00' UNION ALL
SELECT '2015-03-31 22:00' UNION ALL
SELECT '2015-03-31 21:00' UNION ALL
SELECT '2015-03-31 20:00' UNION ALL
SELECT '2015-03-31 19:00' 
)
SELECT * INTO #Temp FROM cte     
SELECT * FROM   #Temp

Your Expected Result script using Row_number()Over() Function

SELECT DISTINCT
        MAX(Dates)OVER(PArtition by BatchSeq Order by (SELECT 1)) AS MaxTime
       ,MIN(Dates)OVER(PArtition by BatchSeq Order by (SELECT 1)) AS MinTime
       ,COUNT(Dates)OVER(PArtition by BatchSeq Order by (SELECT 1)) As [Count]
FROM
(
SELECT Dates  ,  ((ROW_NUMBER()OVER(ORDER BY (SELECT 1))-1)/5+1) AS BatchSeq  FROM
#Temp
)dt
ORDER BY 1 DESC

Your Expected Result script using Group by() Function

SELECT 
        MAX(Dates)   AS MaxTime
       ,MIN(Dates)  AS MinTime
       ,COUNT(Dates) As [Count]
FROM
(
SELECT Dates ,((ROW_NUMBER()OVER(ORDER BY (SELECT 1))-1)/5+1) AS BatchSeq  FROM
#Temp
)dt
GROUP BY BatchSeq
ORDER BY 1 DESC

Result

 MaxTime                MinTime         Count
  --------------------------------------------
  2015-04-01 12:00  2015-04-01 08:00    5
  2015-04-01 05:00  2015-04-01 01:00    5
  2015-03-31 22:00  2015-03-31 19:00    4

Upvotes: 0

Related Questions