how to do this in SQL?

  • 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

  • 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];

    • This reply was modified 1 week, 4 days ago by  Ed B. Reason: Added T
  • 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

  • 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')

    • This reply was modified 1 week, 4 days ago by  water490.
  • This was removed by the editor as SPAM

  • water490 wrote:

    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

  • 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