Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Need help in bit complicated SQL query

Author  Topic 

SQLBeginner2013
Starting Member

6 Posts

Posted - 2013-08-15 : 09:40:30
http://sqlfiddle.com/#!2/134bad

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-15 : 09:58:32
[code]declare @climate table (city varchar(10), status char(1), Curdate date);

insert @climate values ('Chennai', 'R', '2013-08-05');
insert @climate values ('Chennai', 'R', '2013-08-06');
insert @climate values ('Chennai', 'R', '2013-08-07');
insert @climate values ('Chennai', 'R', '2013-08-08');
insert @climate values ('Chennai', 'S', '2013-08-09');
insert @climate values ('Chennai', 'S', '2013-08-10');
insert @climate values ('Chennai', 'R', '2013-08-12');
insert @climate values ('Chennai', 'R', '2013-08-13');
insert @climate values ('Chennai', 'S', '2013-08-14');
insert @climate values ('Chennai', 'S', '2013-08-15');
insert @climate values ('Banglore', 'S', '2013-08-05');
insert @climate values ('Banglore', 'S', '2013-08-06');
insert @climate values ('Banglore', 'R', '2013-08-07');
insert @climate values ('Banglore', 'R', '2013-08-08');
insert @climate values ('Banglore', 'R', '2013-08-09');
insert @climate values ('Banglore', 'S', '2013-08-10');
insert @climate values ('Banglore', 'R', '2013-08-12');
insert @climate values ('Banglore', 'R', '2013-08-13');
insert @climate values ('Banglore', 'R', '2013-08-14');
insert @climate values ('Banglore', 'S', '2013-08-15');

WITH cteSource(City, Status, CurDate, s1, s2)
AS (
SELECT City,
Status,
CurDate,
LAG(Status, 1) OVER (PARTITION BY City ORDER BY CurDate) AS s1,
LAG(Status, 2) OVER (PARTITION BY City ORDER BY CurDate) AS s2
FROM @Climate
), cteDisplay(City, Status, CurDate, rn)
AS (
SELECT City,
Status,
CurDate,
ROW_NUMBER() OVER (PARTITION BY City ORDER BY CurDate DESC) AS rn
FROM cteSource
WHERE Status = s1
AND s1 = s2
)
SELECT City,
Status,
CurDate
FROM cteDisplay
WHERE rn = 1;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-15 : 10:02:36
[code]declare @climate table (city varchar(10), status char(1), Curdate date);

insert @climate values ('Chennai', 'R', '2013-08-05');
insert @climate values ('Chennai', 'R', '2013-08-06');
insert @climate values ('Chennai', 'R', '2013-08-07');
insert @climate values ('Chennai', 'R', '2013-08-08');
insert @climate values ('Chennai', 'S', '2013-08-09');
insert @climate values ('Chennai', 'S', '2013-08-10');
insert @climate values ('Chennai', 'R', '2013-08-12');
insert @climate values ('Chennai', 'R', '2013-08-13');
insert @climate values ('Chennai', 'S', '2013-08-14');
insert @climate values ('Chennai', 'S', '2013-08-15');
insert @climate values ('Banglore', 'S', '2013-08-05');
insert @climate values ('Banglore', 'S', '2013-08-06');
insert @climate values ('Banglore', 'R', '2013-08-07');
insert @climate values ('Banglore', 'R', '2013-08-08');
insert @climate values ('Banglore', 'R', '2013-08-09');
insert @climate values ('Banglore', 'S', '2013-08-10');
insert @climate values ('Banglore', 'R', '2013-08-12');
insert @climate values ('Banglore', 'R', '2013-08-13');
insert @climate values ('Banglore', 'R', '2013-08-14');
insert @climate values ('Banglore', 'S', '2013-08-15');

SELECT City,
MAX(CurDate) AS CurDate
FROM (
SELECT City,
MAX(CurDate) AS CurDate
FROM (
SELECT DATEDIFF(DAY, '19000101', CurDate) - DENSE_RANK() OVER (PARTITION BY City, Status ORDER BY CurDate) AS theGrp,
City,
CurDate
FROM @Climate
) AS d
GROUP BY City,
theGrp
HAVING COUNT(*) > 2
) AS d
GROUP BY City;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-08-15 : 11:06:36
[code]
;with aClimate as(
select 'Banglore' as [city], 'R' as [status], '2013-08-09' [curDate] union all
select 'Banglore' as [city], 'R' as [status], '2013-08-10' [curDate] union all
select 'Banglore' as [city], 'R' as [status], '2013-08-12' [curDate] union all
select 'Banglore' as [city], 'S' as [status], '2013-08-15' [curDate] )


select c1.city,c1.[status],c3.[Curdate]
from aClimate as c1
outer apply
(select top 1 city,[status],[curDate]
from aClimate as c2
where c1.city=c2.city
and c2.[Curdate]>c1.[Curdate]
and c1.[status]<>c2.[status]
order by c2.[Curdate] asc) as c2
outer apply
(select top 1 [curDate]
from aClimate as c3
where c1.city=c3.city
and c3.[Curdate]<c2.[Curdate]
and c3.[status]<>c2.[status]
order by c3.[Curdate] desc) as c3
group by c1.city,c1.[status],c3.[Curdate]
having count(*)>2
[/code]

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-08-15 : 11:19:14
inspired by SwePeso


;with aClimate as(
select 'Banglore' as [city], 'R' as [status], '2013-08-09' [curDate] union all
select 'Banglore' as [city], 'R' as [status], '2013-08-10' [curDate] union all
select 'Banglore' as [city], 'R' as [status], '2013-08-12' [curDate] union all
select 'Banglore' as [city], 'S' as [status], '2013-08-15' [curDate] )


SELECT City,
MAX(CurDate) AS CurDate
FROM (
SELECT City,
MAX(CurDate) AS CurDate
FROM (
SELECT
DATEDIFF(DAY, '19000101', dateadd(DAY,rn,'19000101')) - DENSE_RANK() OVER (PARTITION BY City, Status ORDER BY CurDate) AS theGrp,
City,
CurDate
FROM
( select city,status,curDate,row_number() over(partition by city order by curdate) as rn
from aClimate )A
) AS d
GROUP BY City,
theGrp
HAVING COUNT(*) > 2
) AS d
GROUP BY City;



S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-15 : 11:21:21
Here is one more that rips off Peso, but doesn't use a cte.. :)
SELECT 
City,
MAX(CurDate) AS [Date]
FROM
(
SELECT
City,
Status,
CurDate,
LAG(Status, 1) OVER (PARTITION BY City ORDER BY CurDate) AS PrevStatus,
LAG(Status, 2) OVER (PARTITION BY City ORDER BY CurDate) AS PrevPrevStatus
FROM
@Climate
) AS T
WHERE
Status = PrevStatus
AND PrevStatus = PrevPrevStatus
GROUP BY
City
Go to Top of Page

SQLBeginner2013
Starting Member

6 Posts

Posted - 2013-08-15 : 15:31:20
Thanks all very much.. this worked for me...
Go to Top of Page
   

- Advertisement -