Author |
Topic  |
|
aurelkb
Starting Member
1 Posts |
Posted - 06/09/2014 : 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
Romania
545 Posts |
Posted - 06/10/2014 : 05:15:08
|
;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 |
 |
|
|
Topic  |
|
|
|