CODEWITHSUNDEEP
sql-serversql-server-2008
user2664051
user2664051

Reputation: 21

Need help in bit complicated SQL query

http://sqlfiddle.com/#!2/134bad

Data if you were not able to access the link:

create table climate (city varchar(10), status char(1), Curdate date);

insert into climate values ('Chennai', 'S', '2013-08-05');
insert into climate values ('Chennai', 'S', '2013-08-06');
insert into climate values ('Chennai', 'S', '2013-08-07');
insert into climate values ('Chennai', 'S', '2013-08-08');

insert into climate values ('Chennai', 'R', '2013-08-09');
insert into climate values ('Chennai', 'R', '2013-08-10');

insert into climate values ('Chennai', 'S', '2013-08-12');
insert into climate values ('Chennai', 'S', '2013-08-13');

insert into climate values ('Chennai', 'R', '2013-08-14');
insert into climate values ('Chennai', 'R', '2013-08-15');

insert into climate values ('Banglore', 'S', '2013-08-05');
insert into climate values ('Banglore', 'S', '2013-08-06');
insert into climate values ('Banglore', 'R', '2013-08-07');
insert into climate values ('Banglore', 'R', '2013-08-08');

insert into climate values ('Banglore', 'R', '2013-08-09');
insert into climate values ('Banglore', 'S', '2013-08-10');

insert into climate values ('Banglore', 'R', '2013-08-12');
insert into climate values ('Banglore', 'R', '2013-08-13');

insert into climate values ('Banglore', 'R', '2013-08-14');
insert into climate values ('Banglore', 'S', '2013-08-15');

The link has approximate data.

From the table we need to retrieve city name and latest maximum date when the status ('R' / 'S') remained same for more than 2 days.

ie. R-Raining S-Sunny

We need to retrieve City and maximum date when the city was Rainy or Sunny continuously for more than 2 days.

eg: from the example data,

Query should retrieve 

City                  Date
Banglore           2013-08-14
Chennai            2013-08-08

Thanks in advance for your help

Upvotes: 1

Views: 86

Answers (3)

GalacticJello
GalacticJello

Reputation: 11445

If using SQL 2005 or above:

select city, status, max(endDate) as Date
from
(
  select city, status, MIN(curdate) as startDate, MAX(curDate) as endDate
  from
  (
    select city, status, curdate,
    DATEADD(dd, - ROW_NUMBER() OVER (PARTITION by city, status ORDER BY city, curdate), curdate)
    from climate
    group by city, status, curdate
  ) consecutiveDates(city, status, curdate, grp)
  group by city, status, grp
  having COUNT(*) > 2
) groupedConsecutiveDates
group by city, status

Upvotes: 0

user121489
user121489

Reputation:

This is similar to the Islands and Gaps problem and you can use Common Table Expressions to solve it as well:

;WITH DateIslandByCityStatus_CTE (City, Status, CurDate, Island) AS
(
    SELECT City
         , Status
         , CurDate
         , Island = DATEADD(DAY, -ROW_NUMBER() OVER (PARTITION BY City, Status ORDER BY CurDate), CurDate) 
      FROM Climate
),
DateIslandWithTwoDaysOfWeather (City, Status, MaxDate) AS
(
    SELECT City
         , Status
         , MAX(CurDate)
      FROM DateIslandByCityStatus_CTE
      GROUP BY City, Status, Island
      HAVING COUNT(*) > 2
)
SELECT City
     , Max(MaxDate)
  FROM DateIslandWithTwoDaysOfWeather
 GROUP BY City
 ORDER BY City

See Also: "The SQL of Gaps and Islands in Sequences - Dwain Camps"

Upvotes: 1

GriGrim
GriGrim

Reputation: 2921

For SQL Server 2005/2008:

select city, max(dt) max_dt
from (
    select city
        , dateadd(dd, x, Curdate) dt
        , min(case x when 0 then status end) s0
        , min(case x when 1 then status end) s1
        , min(case x when 2 then status end) s2
    from climate c
    cross join (select 0 x union all select 1 union all select 2)x
    group by city, dateadd(dd, x, Curdate)
) t
where s0 = s1 and s1 = s2
group by city

If you use SQL Server 2012 query will be much simplier. Look for LAG/LEAD functions.

Upvotes: 1

Related Questions