March 21, 2019 at 9:40 am
Hi, I have managed to get so far with creating a central line that changes depending upon trend that is noted by 8 consecutive vales
so this is my result so far,
CL is the central line, T1 is the first trend which is the lower flag, T2 is the next trend
So I need to create the column called Outcome Required, the trend is the consecutive count column where there are 8 points lower or higher then the CL.
if any one has any ideas that would be great thanks
declare @Values table
(
Area varchar(1)
,SubArea varchar(1)
,MonthDate Date
,Visits int
,CL float
,T1 int
,T2 int
,bflag varchar(8)
,consecutivecount int
,OutcomeRequired int
)
insert into @Values (Area,SubArea,MonthDate,Visits,CL,T1,T2,bflag,consecutivecount,OutcomeRequired)
values
('A','E','2015-04-01','16','12.2','10','18','NULL','0','12')
,('A','E','2015-05-01','16','12.2','10','18','NULL','0','12')
,('A','E','2015-06-01','12','12.2','10','18','NULL','0','12')
,('A','E','2015-07-01','12','12.2','10','18','NULL','0','12')
,('A','E','2015-08-01','5','12.2','10','18','NULL','0','12')
,('A','E','2015-09-01','7','12.2','10','18','NULL','0','12')
,('A','E','2015-10-01','18','12.2','10','18','NULL','0','12')
,('A','E','2015-11-01','13','12.2','10','18','NULL','0','12')
,('A','E','2015-12-01','14','12.2','10','18','NULL','0','12')
,('A','E','2016-01-01','11','12.2','10','18','NULL','0','12')
,('A','E','2016-02-01','17','12.2','10','18','NULL','0','12')
,('A','E','2016-03-01','18','12.2','10','18','NULL','0','12')
,('A','E','2016-04-01','13','12.2','10','18','NULL','0','12')
,('A','E','2016-05-01','24','12.2','10','18','NULL','0','12')
,('A','E','2016-06-01','11','12.2','10','18','NULL','0','12')
,('A','E','2016-07-01','8','12.2','10','18','NULL','0','12')
,('A','E','2016-08-01','17','12.2','10','18','NULL','0','12')
,('A','E','2016-09-01','11','12.2','10','18','NULL','0','12')
,('A','E','2016-10-01','25','12.2','10','18','NULL','0','12')
,('A','E','2016-11-01','15','12.2','10','18','NULL','0','12')
,('A','E','2016-12-01','9','12.2','10','18','NULL','0','12')
,('A','E','2017-01-01','18','12.2','10','18','NULL','0','12')
,('A','E','2017-02-01','12','12.2','10','18','Lower','8','10')
,('A','E','2017-03-01','11','12.2','10','18','Lower','8','10')
,('A','E','2017-04-01','9','12.2','10','18','Lower','8','10')
,('A','E','2017-05-01','10','12.2','10','18','Lower','8','10')
,('A','E','2017-06-01','10','12.2','10','18','Lower','8','10')
,('A','E','2017-07-01','8','12.2','10','18','Lower','8','10')
,('A','E','2017-08-01','2','12.2','10','18','Lower','8','10')
,('A','E','2017-09-01','8','12.2','10','18','Lower','8','10')
,('A','E','2017-10-01','13','12.2','10','18','Over','8','18')
,('A','E','2017-11-01','15','12.2','10','18','Over','8','18')
,('A','E','2017-12-01','20','12.2','10','18','Over','8','18')
,('A','E','2018-01-01','20','12.2','10','18','Over','8','18')
,('A','E','2018-02-01','23','12.2','10','18','Over','8','18')
,('A','E','2018-03-01','24','12.2','10','18','Over','8','18')
,('A','E','2018-04-01','13','12.2','10','18','Over','8','18')
,('A','E','2018-05-01','13','12.2','10','18','Over','8','18')
,('A','E','2018-06-01','9','12.2','10','18','NULL','0','18')
,('A','E','2018-07-01','14','12.2','10','18','NULL','0','18')
,('A','E','2018-08-01','12','12.2','10','18','NULL','0','18')
,('A','E','2018-09-01','10','12.2','10','18','NULL','0','18')
,('A','E','2018-10-01','18','12.2','10','18','NULL','0','18')
,('A','E','2018-11-01','18','12.2','10','18','NULL','0','18')
,('A','E','2018-12-01','15','12.2','10','18','NULL','0','18')
,('A','E','2019-01-01','26','12.2','10','18','NULL','0','18')
,('A','E','2019-02-01','8','12.2','10','18','NULL','0','18')
,('A','E','2019-03-01','10','12.2','10','18','NULL','0','18')
Select * from @Values
March 21, 2019 at 10:00 am
You say that the central line depends on the trend, but you have two trends, and you don't specify which trend it depends on. Furthermore, I don't see any variation in those trends, so I don't see why your central line should change.
Also, a picture of data is almost useless. You should post sample data as outlined in the first link in my signature.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 21, 2019 at 10:19 am
drew.allen - Thursday, March 21, 2019 10:00 AMYou say that the central line depends on the trend, but you have two trends, and you don't specify which trend it depends on. Furthermore, I don't see any variation in those trends, so I don't see why your central line should change.Also, a picture of data is almost useless. You should post sample data as outlined in the first link in my signature.
Drew
ok I will submit a revised post, the trend is that the visits is either greater or lower then the Central Line for at least 8 consecutive points
March 25, 2019 at 6:52 am
drew.allen - Thursday, March 21, 2019 10:00 AMYou say that the central line depends on the trend, but you have two trends, and you don't specify which trend it depends on. Furthermore, I don't see any variation in those trends, so I don't see why your central line should change.Also, a picture of data is almost useless. You should post sample data as outlined in the first link in my signature.
Drew
sql added above thanks
March 25, 2019 at 10:41 am
There are several things I don't understand about how your trend line is working.
1) I don't understand why you've hard-coded values for the various trend lines and why those values don't adjust based on the current trend.
2) I don't understand why you're looking forward for your trend instead of looking back. This requires retroactively changing the central trend line once you hit the eighth day below the trend.
I think of a trend line as something that adjusts more gradually instead of having these sharp jumps. Something like the following.
Select *
, AVG(VISITS) OVER(PARTITION BY Area, SubArea ORDER BY MonthDate ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS Central_Line
, MIN(VISITS) OVER(PARTITION BY Area, SubArea ORDER BY MonthDate ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS Lower_Trend
, MAX(VISITS) OVER(PARTITION BY Area, SubArea ORDER BY MonthDate ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS Upper_Trend
from @Values
This can be modified to do what you want, but it will be more complicated and, I think, less informative.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 25, 2019 at 10:57 am
drew.allen - Monday, March 25, 2019 10:41 AMThere are several things I don't understand about how your trend line is working.1) I don't understand why you've hard-coded values for the various trend lines and why those values don't adjust based on the current trend.
2) I don't understand why you're looking forward for your trend instead of looking back. This requires retroactively changing the central trend line once you hit the eighth day below the trend.I think of a trend line as something that adjusts more gradually instead of having these sharp jumps. Something like the following.
Select *
, AVG(VISITS) OVER(PARTITION BY Area, SubArea ORDER BY MonthDate ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS Central_Line
, MIN(VISITS) OVER(PARTITION BY Area, SubArea ORDER BY MonthDate ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS Lower_Trend
, MAX(VISITS) OVER(PARTITION BY Area, SubArea ORDER BY MonthDate ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS Upper_Trend
from @ValuesThis can be modified to do what you want, but it will be more complicated and, I think, less informative.
Drew
Thank you I don't want to recalculate the trend line I just want a way to pick up the different values as they change so when the flag changes from NULL to Lower, I want it to pick up T1, then T2 when the flag changes to Over, continuing for the rest of the months as there is no other flag following, I have added in the expected outcome in the outcome required column.
Thank you
March 25, 2019 at 11:31 am
TrooperMichelleM - Monday, March 25, 2019 10:57 AMdrew.allen - Monday, March 25, 2019 10:41 AMThere are several things I don't understand about how your trend line is working.1) I don't understand why you've hard-coded values for the various trend lines and why those values don't adjust based on the current trend.
2) I don't understand why you're looking forward for your trend instead of looking back. This requires retroactively changing the central trend line once you hit the eighth day below the trend.I think of a trend line as something that adjusts more gradually instead of having these sharp jumps. Something like the following.
Select *
, AVG(VISITS) OVER(PARTITION BY Area, SubArea ORDER BY MonthDate ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS Central_Line
, MIN(VISITS) OVER(PARTITION BY Area, SubArea ORDER BY MonthDate ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS Lower_Trend
, MAX(VISITS) OVER(PARTITION BY Area, SubArea ORDER BY MonthDate ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS Upper_Trend
from @ValuesThis can be modified to do what you want, but it will be more complicated and, I think, less informative.
Drew
Thank you I don't want to recalculate the trend line I just want a way to pick up the different values as they change so when the flag changes from NULL to Lower, I want it to pick up T1, then T2 when the flag changes to Over, continuing for the rest of the months as there is no other flag following, I have added in the expected outcome in the outcome required column.
Thank you
I told you it was more complicated.
With Lastest_Values AS
(
Select *
, CASE SUM(SIGN(VISITS - CL)) OVER(PARTITION BY Area, SubArea ORDER BY MonthDate ROWS BETWEEN CURRENT ROW AND 7 FOLLOWING)
WHEN 8 THEN T2
WHEN -8 THEN T1
END AS Latest_Value
from @Values
)
SELECT lv.Area
, lv.SubArea
, lv.MonthDate
, lv.Visits
, lv.CL
, lv.T1
, lv.T2
, CAST(COALESCE(SUBSTRING(MAX(CAST(CAST(lv.MonthDate AS DATETIME) AS BINARY(6)) + CAST(lv.Latest_Value AS BINARY(6))) OVER(PARTITION BY lv.Area, lv.SubArea ORDER BY lv.MonthDate ROWS UNBOUNDED PRECEDING), 7, 6), CAST(CAST(lv.CL AS INT) AS BINARY(6))) AS INT)
FROM Lastest_Values lv
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply