April 3, 2024 at 4:19 pm
Hi everyone
I am working on a query and I am totally stumped. I don't even know where to begin. I will give test data, expected outcome, and explanation of logic.
Test Data:
create table #test_table
(
company char(4),
trade_date date,
type char(1),
level int,
price decimal(6,2)
)
insert into #test_table
values
('ABC','2024-03-21','L','100','23.45'),
('ABC','2024-03-21','L','200','33.12'),
('ABC','2024-03-21','L','300','21.05'),
('ABC','2024-03-21','L','400','21.49'),
('ABC','2024-03-21','L','500','22.01'),
('ABC','2024-03-21','L','600','0.00'),
('ABC','2024-03-21','L','700','3.03'),
('ABC','2024-03-21','L','800','0.00'),
('ABC','2024-03-21','L','900','0.00'),
('ABC','2024-03-21','L','1000','11.03'),
('ABC','2024-03-21','L','1100','19.77'),
('ABC','2024-03-21','L','1200','12.93'),
('ABC','2024-03-21','T','1','22.08'),
('ABC','2024-03-21','T','2','28.95'),
('ABC','2024-03-21','T','3','21.00'),
('ABC','2024-03-21','T','4','30.79'),
('ABC','2024-03-21','T','5','26.90'),
('ABC','2024-03-21','T','6','0.00'),
('ABC','2024-03-21','T','7','0.00'),
('ABC','2024-03-21','T','8','18.93'),
('ABC','2024-03-21','T','9','0.00'),
('ABC','2024-03-21','T','10','33.43'),
('ABC','2024-03-21','T','11','39.07'),
('ABC','2024-03-21','T','12','42.91')
Expected Outcome:
('ABC','2024-03-21','L','100'),
('ABC','2024-03-21','L','200'),
('ABC','2024-03-21','L','300'),
('ABC','2024-03-21','L','400'),
('ABC','2024-03-21','L','500'),
('ABC','2024-03-21','L','700'),
('ABC','2024-03-21','T','8'),
('ABC','2024-03-21','T','10'),
('ABC','2024-03-21','T','11'),
('ABC','2024-03-21','T','12')
Explanation of Logic:
There are two scenarios to consider.
Scenario 1 - Type = "L"
First, sort records by company then trade_date then level.
Second, start with the lowest level = 100 and then work up the different levels. The record is included if the price is not equal to 0. As soon as two consecutive prices prices equal to 0 then stop checking. We see that levels 100 to 500 all have prices not equal to 0 so they are included. Level 600 has price = 0 so skip it. Level 700 has non-zero price so include it. Levels 800 and 900 have two consecutive zero prices so stop checking. It has be to be at least 2 consecutive zero prices so it can be more than 2. Even though Levels 1000,1100,1200 have non-zero prices they are not considered.
Scenario 2 - Type = "T"
First, sort records by company then trade_date then level.
Second, start with the highest level = 12. and then work down the different levels. The logic is smiliar to before but this time you start at the top and work down. Once you get two consecutive zero prices then stop. Levels 10,11,12 have non-zero prices so include them. Level 9 has zero price so exclude. Level 8 has nonzero price so include. Levels 6 and 7 both have zero prices so we stop. It has be to be at least 2 consecutive zero prices so it can be more than 2. Even though Levels 1 to 5 have nonzero prices that doesn't matter. They are not included.
How would I code this?
Thank you
April 3, 2024 at 5:41 pm
Does this work for the first example? I have assumed you need to group by company and trade_date.
;
WITH cte_L
AS
(
SELECT *,
-- get price from previous row
LAG(Price,1,1) OVER (PARTITION BY a.Company, a.trade_date ORDER BY a.[level] ASC) AS PrevPrice
FROM #test_table AS a
WHERE a.[type] = 'L'
)
SELECT b.company, b.trade_date, b.[type], b.[level], b.price
--,b.SumBothZero
FROM (
SELECT a.company, a.trade_date, a.[type], a.[level], a.price,
--running total BothZero
SUM(IIF(Price + PrevPrice = 0,1,0)) OVER (PARTITION BY a.Company, a.trade_date ORDER BY a.[level] ASC) AS SumBothZero
FROM cte_L AS a
) AS b
WHERE b.price > 0 AND b.SumBothZero < 1
ORDER BY b.company, b.trade_date, b.[type], b.[level];
Reverse the order for type T
WITH cte_T
AS
(
SELECT *,
-- get price from previous row
LAG(Price,1,1) OVER (PARTITION BY a.Company, a.trade_date ORDER BY a.[level] DESC) AS PrevPrice
FROM #test_table AS a
WHERE a.[type] = 'T'
)
SELECT b.company, b.trade_date, b.[type], b.[level], b.price
--,b.SumBothZero
FROM (
SELECT a.company, a.trade_date, a.[type], a.[level], a.price,
--running total BothZero
SUM(IIF(Price + PrevPrice = 0,1,0)) OVER (PARTITION BY a.Company, a.trade_date ORDER BY a.[level] DESC) AS SumBothZero
FROM cte_T AS a
) AS b
WHERE b.price > 0 AND b.SumBothZero < 1
ORDER BY b.company, b.trade_date, b.[type], b.[level];
April 3, 2024 at 8:06 pm
I used a calculated field to combine these into a single query.
WITH Stop_Fl AS
(
SELECT *
, CASE WHEN tt.price > 0 THEN 0
WHEN LAG(tt.price,1,1) OVER(PARTITION BY tt.company, tt.trade_date, tt.[type] ORDER BY s.sort_value) = 0 THEN 1
ELSE 0
END AS stop_fl
FROM #test_table AS tt
CROSS APPLY(VALUES(CASE WHEN tt.[type] = 'T' THEN tt.[level] ELSE -tt.[level] END)) s(sort_value)
)
, Stop_Cnt AS
(
SELECT *
, SUM(s.stop_fl) OVER(PARTITION BY s.company, s.trade_date, s.[type] ORDER BY s.sort_value) AS stop_cnt
FROM Stop_Fl AS s
)
SELECT sc.company
, sc.trade_date
, sc.type
, sc.level
, sc.price
FROM Stop_Cnt AS sc
WHERE sc.stop_cnt > 0
AND sc.price > 0
ORDER BY sc.company, sc.trade_date, sc.[type], sc.[level]
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 3, 2024 at 11:33 pm
Thank you both!! I appreciate your help so much!
I tested another scenario. I added a different company in the list so there are now two not one company.
Drew, your solution doesn't list the new company. It only lists the original one. Do you know why? Here is revised test data:
insert into #test_table
values
('ABC','2024-03-21','L','100','23.45'),
('DEF','2024-03-21','L','200','33.12'),
('ABC','2024-03-21','L','300','21.05'),
('ABC','2024-03-21','L','400','21.49'),
('DEF','2024-03-21','L','500','20.01'),
('ABC','2024-03-21','L','500','22.01'),
('ABC','2024-03-21','L','600','0.00'),
('ABC','2024-03-21','L','700','3.03'),
('ABC','2024-03-21','L','800','0.00'),
('ABC','2024-03-21','L','900','0.00'),
('ABC','2024-03-21','L','1000','11.03'),
('ABC','2024-03-21','L','1100','19.77'),
('ABC','2024-03-21','L','1200','12.93'),
('ABC','2024-03-21','T','1','22.08'),
('DEF','2024-03-21','T','2','28.95'),
('DEF','2024-03-21','T','3','21.00'),
('ABC','2024-03-21','T','4','30.79'),
('ABC','2024-03-21','T','5','26.90'),
('ABC','2024-03-21','T','6','0.00'),
('ABC','2024-03-21','T','7','0.00'),
('ABC','2024-03-21','T','8','18.93'),
('ABC','2024-03-21','T','9','0.00'),
('ABC','2024-03-21','T','10','33.43'),
('ABC','2024-03-21','T','11','39.07'),
('ABC','2024-03-21','T','12','42.91')
April 4, 2024 at 12:03 pm
This was removed by the editor as SPAM
April 4, 2024 at 3:11 pm
Drew, your solution doesn't list the new company. It only lists the original one. Do you know why?
Yes, there was an error in my code. I was thinking that the coding was backwards, and that's because I was checking for the wrong final condition. Here is the updated code.
WITH Stop_Fl AS
(
SELECT *
, CASE WHEN tt.price > 0 THEN 0
WHEN LAG(tt.price,1,1) OVER(PARTITION BY tt.company, tt.trade_date, tt.[type] ORDER BY s.sort_value) = 0 THEN 1
ELSE 0
END AS stop_fl
FROM #test_table AS tt
CROSS APPLY(VALUES(CASE WHEN tt.[type] = 'L' THEN tt.[level] ELSE -tt.[level] END)) s(sort_value) -- Not change here.
)
, Stop_Cnt AS
(
SELECT *
, SUM(s.stop_fl) OVER(PARTITION BY s.company, s.trade_date, s.[type] ORDER BY s.sort_value) AS stop_cnt
FROM Stop_Fl AS s
)
SELECT sc.company
, sc.trade_date
, sc.type
, sc.level
, sc.price
FROM Stop_Cnt AS sc
WHERE sc.stop_cnt = 0 -- Note change here.
AND sc.price > 0
ORDER BY sc.company, sc.trade_date, sc.[type], sc.[level]
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 4, 2024 at 4:05 pm
the queries are giving the correct output now.
thank you both so much!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply