CODEWITHSUNDEEP
sqlsql-server
stkflmb12
stkflmb12

Reputation: 165

sql query group by data of certain range in continuous date

I want to Write a sql to query group by data within a certain range in continuous date.I'm usig T-SQL.

Table:

Date     |  MeterNo|   Data  |
---------|---------|---------|
01-06-15 |  12345  |      10 |
02-06-15 |  12345  |      12 |
03-06-15 |  12345  |      51 |
04-06-15 |  12345  |      56 |
05-06-15 |  12345  |      16 |  
06-06-15 |  12345  |      15 |
07-06-15 |  12345  |      9  |
08-06-15 |  12345  |      53 |
09-06-15 |  12345  |      55 |
10-06-15 |  12345  |      62 |
11-06-15 |  12345  |      8  |
12-06-15 |  12345  |      18 |

I would like to have group by result such as follow:

|MeterNo| GroupName| StartDate |  EndDate |
|-------|----------|-----------|--------- |
| 12345 |  0(<50)  |  01-06-15 |  02-06-15| 
| 12345 |  1(>=50) |  03-06-15 |  04-06-15| 
| 12345 |  0(<50)  |  05-06-15 |  07-06-15| 
| 12345 |  1(>=50) |  08-06-15 |  10-06-15| 
| 12345 |  0(<50)  |  11-06-15 |  12-06-15|

I need to group Data to be less than 50 and larger than 50 in continuous date as below. How can I get SQL query to get the result like second result table? thanks in advance!

Upvotes: 0

Views: 775

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Some alternative solution:

declare @t table([Date] date, [MeterNo] int, [Data] int);

INSERT INTO @t VALUES
    ('2015-06-01', 12345, 10),
    ('2015-06-02', 12345, 12),
    ('2015-06-03', 12345, 51),
    ('2015-06-04', 12345, 56),
    ('2015-06-05', 12345, 16),
    ('2015-06-06', 12345, 15),
    ('2015-06-07', 12345, 9),
    ('2015-06-08', 12345, 53),
    ('2015-06-09', 12345, 55),
    ('2015-06-10', 12345, 62),
    ('2015-06-11', 12345, 8),
    ('2015-06-12', 12345, 18);

with cte as(select *,
                   case when data < 50 then 0 else 1 end gr1,
                   ROW_NUMBER() over(partition by MeterNo order by date)-
                   ROW_NUMBER() over(partition by MeterNo, 
                                                  case when data < 50 then 0 else 1 end 
                                     order by date) gr2
           from @t)
select MeterNo, 
       MIN(date) StartDate, 
       MAX(date) EndDate,
       case when gr1 = 0 then '0(<50)' else '1(>=50)' end as GroupName
from cte
group by MeterNo, gr1, gr2
order by StartDate

Upvotes: 1

James Z
James Z

Reputation: 12317

This is basically gaps and islands problem and you can group the date ranges together by using row number and checking that against the distance for each date from a fixed date, for example date 0 (=1.1.1900). So you can get the result by this:

select
  MeterNo,
  Grp,
  min(Date) as StartDate,
  max(Date) as EndDate
from (
  select
    MeterNo,
    Date,
    Grp,
    datediff(day, 0, Date) - RN as DateGroup
  from (
    select
      Date,
      MeterNo,
      case when Data >= 50 then 1 else 0 end as Grp,
      row_number() over (partition by MeterNo, 
          case when Data >= 50 then 1 else 0 end order by date asc) as RN
    from
      table1
    ) X
  ) Y
group by
  MeterNo,
  Grp,
  DateGroup
Order by
  MeterNo,
  StartDate

You can test this in SQL Fiddle

Upvotes: 3

Related Questions