Regroupement sur interval de date

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)

Regroupement sur interval de date

Author

Topic

aurelkb
Starting Member

1 Post

Posted - 2014-06-09 : 16:43:06

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
Aged Yak Warrior

545 Posts

Posted - 2014-06-10 : 05:15:08

[code]
;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

[/code]

output:
[code]
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
[/code]

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources