Need help in bit complicated SQL query

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

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

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

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

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

SQLBeginner2013
Starting Member

6 Posts

Posted - 2013-08-15 : 15:31:20

Thanks all very much.. this worked for me...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources