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)
 Regroupement sur interval de date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aurelkb
Starting Member

1 Posts

Posted - 06/09/2014 :  16:43:06  Show Profile  Reply with Quote
Salut,
voila j'ai les données suivantes :
INSERT INTO Sales(mnth, qty) VALUES
('20071201', 100),
('20080101', 110),
('20080201', 120),
('20080301', 130),
('20080401', 140),
('20080501', 140),
('20080601', 130),
('20080701', 120),
('20080801', 110),
('20080901', 100),
('20081001', 110),
('20081101', 100),
('20081201', 120),
('20090101', 130),
('20090201', 140),
('20090301', 100),
('20090401', 100),
('20090501', 100),
('20090601', 110),
('20090701', 120),
('20090801', 110),
('20090901', 120),
('20091001', 130),
('20091101', 140),
('20091201', 100);
GO

et j'aimerais faire un regroupement sur interval de date afin d'avoir le résultat suivant :

Start_Range End_Range Trend
200712 200712 unknown
200801 200804 UP
200805 200805 SAME
200806 200809 DOWN
200810 200810 UP
200811 200811 DOWN
200812 200812 UP
200903 200903 DOWN
200904 200905 SAME
200906 200907 UP
200908 200908 DOWN
200909 200911 UP
200912 200912 DOWN

Merci pour vos réponses

Aurel

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 06/10/2014 :  05:15:08  Show Profile  Reply with Quote

;With Sales(mnth, qty)
AS
	(SELECT  * FROM(VALUES ('20071201', 100),
				('20080101', 110),
				('20080201', 120),
				('20080301', 130),
				('20080401', 140),
				('20080501', 140),
				('20080601', 130),
				('20080701', 120),
				('20080801', 110),
				('20080901', 100),
				('20081001', 110),
				('20081101', 100),
				('20081201', 120),
				('20090101', 130),
				('20090201', 140),
				('20090301', 100),
				('20090401', 100),
				('20090501', 100),
				('20090601', 110),
				('20090701', 120),
				('20090801', 110),
				('20090901', 120),
				('20091001', 130),
				('20091101', 140),
				('20091201', 100))x(mnth, qty)
)

, aCTE
AS (
	SELECT 
		mnth
		,qty
		,LAG(qty,1,qty) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) as LastQty
		,LAG(mnth,1,mnth) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) as LastMnth

		,LEAD(qty,1,qty) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) as NextQty
		,LEAD(mnth,1,mnth) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) as NextMnth

		,CASE WHEN qty >  LAG(qty,1,NULL) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) 
					OR LAG(qty,1,NULL) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) IS NULL THEN 1 
			  WHEN qty =  LAG(qty,1,NULL) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) THEN 0
			  WHEN qty <  LAG(qty,1,NULL) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) THEN -1
				ELSE NULL END AS Tnd

		,CASE WHEN qty >  LAG(qty,1,NULL) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth)
				OR LAG(qty,1,NULL) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) IS NULL  THEN 'UP' 
			WHEN qty =  LAG(qty,1,NULL) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) THEN 'SAME'
			WHEN qty <  LAG(qty,1,NULL) OVER (PARTITION BY YEAR(mnth) ORDER BY mnth) THEN 'DOWN'
				ELSE 'UNKOWN' END AS TndStr	
		,YEAR(mnth) as [YEAR]	
	FROM SALES )




select 
	distinct
		LEFT(COALESCE(StartRange,StartRangeSecond),6) as Start_Range
		,LEFT(COALESCE(EndRange,StartRange,StartRangeSecond),6) as End_Range
		,TndStr
		
from aCTE AS A

	outer apply
	(
		SELECT TOP (1)
			B.NextMnth as StartRange
		FROM
			aCTE as B
		WHERE 
			A.Mnth > = B.Mnth
			AND A.TndStr<>B.TndStr
			AND A.[Year]=B.[Year]
		ORDER BY
			B.Mnth DESC )N

	outer apply
	(
		SELECT TOP (1)
			C.LastMnth as  EndRange
		FROM
			aCTE as C
		WHERE 
			A.Mnth < = C.Mnth
			AND A.TndStr <> C.TndStr
			AND A.[Year]=C.[Year]
		ORDER BY
			C.Mnth  ASC )Y

	outer apply
	(
		SELECT TOP (1)
			B.Mnth as StartRangeSEcond
		FROM
			aCTE as B
		WHERE 
			A.Mnth > = B.Mnth
			AND A.TndStr = B.TndStr
			AND A.[Year]=B.[Year]
		ORDER BY
			B.Mnth ASC )R



output:

Start_Range	End_Range	TndStr
200712	200712	UP
200801	200804	UP
200805	200805	SAME
200806	200809	DOWN
200810	200810	UP
200811	200811	DOWN
200812	200812	UP
200901	200902	UP
200903	200903	DOWN
200904	200905	SAME
200906	200907	UP
200908	200908	DOWN
200909	200911	UP
200912	200912	DOWN




sabinWeb MCP
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.06 seconds. Powered By: Snitz Forums 2000