December 28, 2021 at 3:37 am
Hello everyone,
I have such a situation. I need to only show items that have had 3 consecutive weeks of over or under forecast or Bias.
I am trying to understand how I can i can filter data taking into consideration consecutive weeks.
If someone may have any ideas, I will appreciate...
SELECT Location AS 'LOC', DMDUNIT AS 'DMDUNIT',
STARTDATE AS 'Start Date',
FORECAST AS 'Forecast',
ACTUALS AS 'Actuals'
,ABS((SUM(Actuals) - SUM(Forecast))) AS 'Abs Error'
,(1 - (SUM(Actuals) - SUM(Forecast)) / SUM(Actuals)) AS 'FA%'
,SUM(Actuals) / SUM(Forecast) AS 'Bias'
FROM Forecast F FULL OUTER JOIN Actuals A
on f.[DMDUNIT] = a.[DMDUNIT]
and f.[Start Date] = a.[Start Date]
and f.[LOC] = a.[LOC]
GROUP BY Location, DMDUNIT, Start Date
ORDER BY Location, DMDUNIT, Start Date
December 28, 2021 at 1:37 pm
The LAG function takes up to 3 parameters:
1) Column name (required)
2) Row offset (optional)
3) Default value (optional)
To find 3 +/- consecutive rows with the same sign maybe sum the SIGN function 3 times and use the LAG function twice (with offsets 1 and 2)
with
frcst_vs_act_cte as (
SELECT COALESCE(f.[LOC], a.[LOC]) as [LOC],
COALESCE(f.[DMDUNIT], a.[DMDUNIT]) as [DMDUNIT],
COALESCE(f.[STARTDATE], a.[DMDPostDate]) as [Start Date],
SUM(F.[TOTFCST]) as [Forecast],
SUM(A.[HistoryQuantity]) as [Actuals]
,ABS((SUM(A.[HistoryQuantity]) - SUM(f.[TOTFCST]))) as [Abs Error]
,(1 - (SUM(A.[HistoryQuantity]) - SUM(F.[TOTFCST])) / SUM(A.[HistoryQuantity])) as [FA%]
,SUM(A.[HistoryQuantity]) / SUM(F.[TOTFCST]) as [Bias]
FROM Forecast F
FULL OUTER JOIN Actuals A on f.[DMDUNIT] = a.[DMDUNIT]
and f.[STARTDATE] = a.[DMDPostDate]
and f.[LOC] = a.[LOC]
WHERE f.[DMDUNIT]='042229215018'
GROUP BY COALESCE(f.[LOC], a.[LOC]), COALESCE(f.[DMDUNIT], a.[DMDUNIT]), COALESCE(f.[STARTDATE], a.[DMDPostDate])
ORDER BY COALESCE(f.[LOC], a.[LOC]), COALESCE(f.[DMDUNIT], a.[DMDUNIT]), COALESCE(f.[STARTDATE], a.[DMDPostDate])),
lags_cte as (
select *,
sign(Bias) +
sign(lag(Bias, 1, 0) over (partition by [LOC], [DMDUNIT] order by [Start Date])) +
sign(lag(Bias, 2, 0) over (partition by [LOC], [DMDUNIT] order by [Start Date])) lag3
from frcst_vs_act_cte)
select *
from lags_cte
where lag3 in(3, -3);
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
December 28, 2021 at 5:31 pm
In general the SIGN function can be useful for "over and under" type situations. It seems like the question was edited maybe idk. Anyway, it seems you're looking for the binary occurrence of three events in a row. When there's no sample data provided I just assume you're not looking for a tested solution. I cut and pasted your code into an editor and rearranged it to the point where I would normally test it by submitting it. Instead I post on SSC with a comment like "Maybe something like this"
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
December 28, 2021 at 6:32 pm
Since you're not providing minimally representative data here's a minimally representative query. One important thing missed in the previous attempt was the 'Bias' indicates over/under vs budget based on +/-1. So each SIGN function subtracts 1 from the 'Bias'. Maybe something like this. To see it work and not work you could run it with and without the WHERE clause
with frcst_vs_act_cte(start_dt, bias, sgn_one, sgn_two, sgn_thr) as (
select *,
sign(v.bias-1),
sign(lag(v.bias-1, 1, 0) over (order by v.start_dt)),
sign(lag(v.bias-1, 2, 0) over (order by v.start_dt))
from (values ('2021-10-17', 0.25),
('2021-10-24', 1.00),
('2021-10-31', 1.25),
('2021-11-07', 1.25),
('2021-11-14', 1.25),
('2021-11-21', 1.25),
('2021-11-28', 1.25),
('2021-12-05', 0.25),
('2021-12-12', 0.25),
('2021-12-19', 0.25),
('2021-12-26', 0.25)) v(start_dt, bias))
select *
from frcst_vs_act_cte
where (sgn_one+sgn_two+sgn_thr) in(3, -3);
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
December 28, 2021 at 8:30 pm
@steve-2 Collins
I am really sorry I am not that experienced may not understand some things.
This is the minimal code (I trimmed it down as much as I could).
I reproduced your code. But I am not sure I am getting what I need.
December 28, 2021 at 9:44 pm
Questions are offered quite acceptable solutions if only some representative sample data is provided. Make it easy to copy/paste temp table(s) and data. If it's reduced to "here is the input and there is the output" your prospects for a good solution go way way up
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
December 28, 2021 at 10:21 pm
Steve Collins I am not sure I understand what I need to do. In my last post I have provided the minimal code that you suggested to provide. I cannot trim it down anymore. In your first message at 5.37am you did edit my code but I have provided just a very small sample and it didn't work at the end (numbers were completly off). If you know how I can edit my code that I wrote at 12.30pm, I will greatly appreciate.
I cannot trim it down any further because then the whole issue (usage of CTEs in combination with SIGN function) will not be shown (that's where I am struggling right now). If you can help me adjust the code from 12.30pm I will greatly appreciate. If you can't, that's fine. I still appreciate the time that you have invested in my education. Thank you.
December 29, 2021 at 5:03 pm
You need to post CREATE TABLE and INSERT scripts so people who are trying to help you can work on your question and not guess about your table structures etc. helps a lot to provide tested solutions
December 29, 2021 at 8:47 pm
@pietlinden I am not sure how I can provide scripts... I have no idea what is this tbh...
I have similar to what Steve Collins have described. It runs but doesn't produce a final output (runs for over an hour). Do you think there may be a mistake somewhere?
December 30, 2021 at 7:48 pm
CREATE TABLE scripts are the scripts to create the tables that you have in your database. If you right-click on the table in the SQL Browser window, one of the options is CREATE TO... and if you select CLIPBOARD, you can then use the [Insert/Edit Code Sample] button and paste your code in there. Then we'll have your table structure to use. You can also use the same method to generate INSERT scripts for your table. You just have to fill in the values.
Then people can run your code and have a tiny slice of your data so they have something to test against. Otherwise it's harder/impossible to provide tested code. Maybe this article will help explain...
Forum Etiquette: How to post data/code on a forum to get the best help
Get correct answers to your SQL forum questions faster by making it easier to load your sample data and read your code.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply