SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Need help in bit complicated SQL query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLBeginner2013
Starting Member

6 Posts

Posted - 08/15/2013 :  09:40:30  Show Profile  Reply with Quote
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

Sweden
30282 Posts

Posted - 08/15/2013 :  09:58:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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;



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

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 08/15/2013 :  10:02:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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;



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

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 08/15/2013 :  11:06:36  Show Profile  Reply with Quote

;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


S

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

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 08/15/2013 :  11:19:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/15/2013 :  11:21:21  Show Profile  Reply with Quote
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 - 08/15/2013 :  15:31:20  Show Profile  Reply with Quote
Thanks all very much.. this worked for me...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000