CODEWITHSUNDEEP
sql-servert-sqlif-statementcursorexists
Elizabeth
Elizabeth

Reputation: 765

If Exists in the same SQL table Cursor Update

I need some advice with SQL logic/language. I have a cursor that will cycle through thousands of combinations of year + customer_id + order_no in a table.

Data sample

year customer_id  order_no  markerA   markerB   markerC  MarkerD
2018 32329        523142
2018 32329        523243
2018 39566        523508
2018 42352        523214
2017 17675        470537
2017 21486        479414
2017 39566        479038
2017 42352        479220

and so on

What I need my logic to do is say pull the value of the combination above customer_id + year + order_no

if no customer_no appears again (after the initial pull) then MarkerA - 'Y'.

If however customer_no appears again -- if that year is the same is in the initial pull then MarkerB -'Y'.

If however customer_no appears again -- but the years are different review further if the year is year - 1 then MarkerC -'Y'.

If however customer_no appears again -- but the years are different review further if the year is NOT year - 1 but then a line does exist elsewhere where year-2 or greater exists then MarkerD -'Y'.

declare @order_year int
declare @customer_id int
declare @order_dt datetime
declare @order_no int

BEGIN   

    DECLARE db_cursor CURSOR FOR 
    Select distinct year, customer_id, order_dt, order_no From #Compare_Data 

    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @order_year, @customer_no, @order_dt, @order_no

        WHILE @@FETCH_STATUS = 0  
        BEGIN

... I know i need a series of IF statements but i'm not sure what/how to compare if it exists. It's impossible to say if a value exists because of course a value exists you just pulled that customer_no/season etc. from the table. How do i say if a value exists excluding the one i'm looking at. 

        FETCH NEXT FROM db_cursor INTO @customer_no, @order_dt, @order_no
        END 

    CLOSE db_cursor  
    DEALLOCATE db_cursor

END

Upvotes: 2

Views: 205

Answers (1)

Daniel Gimenez
Daniel Gimenez

Reputation: 20599

If I understand your problem correctly, you're checking customer orders in descending order you went to ascertain the state of orders as the compare to the latest order for the customer.

Honestly, I'm a little iffy about if I understand your requirements, so my solution will should give you the gist of what you need to do.

First don't user cursors. There is rarely a legitimate reason to use them, they're slow and expensive. This problem should be instead solved with window functions.

Window functions let you look into a window of your result set from a certain row, by performing an aggregate function on a partition. So for example if you want the minimum year for all rows with the same customer id, you'd write MIN([Year]) OVER (PARTITION BY CustomerId).

Below is attempt to solve your problem. I could imagine that you're going to have to adjust the CASE expressions to suit your exact criteria.

-- Setup test data
DECLARE @CompareData TABLE ( [Year] INT, CustomerId INT, OrderNo INT );

INSERT INTO @CompareData 
VALUES 
    (2018, 32329, 523142),
    (2018, 32329, 523243),
    (2018, 39566, 523508),
    (2018, 42352, 523214),
    (2017, 17675, 470537),
    (2017, 21486, 479414),
    (2017, 39566, 479038),
    (2017, 42352, 479220),
    (2016, 42352, 479220);

-- solution
WITH src AS (
    SELECT *
        --, ROW_NUMBER() OVER 
        --     (PARTITION BY CustomerId ORDER BY Year DESC, OrderNo DESC) DescOrderIdx
        , COUNT(CustomerId) OVER (PARTITION BY CustomerId) CustCount
        , MIN([Year]) OVER (PARTITION BY CustomerId) MinYear
        , MAX([Year]) OVER (PARTITION BY CUstomerId) MaxYear
    FROM @CompareData
)
SELECT [Year], CustomerId, OrderNo
    , CASE WHEN CustCount = 1 THEN 'Y' ELSE '' END [MarkerA]
    , CASE WHEN CustCount > 1 AND [Year] = MaxYear THEN 'Y' ELSE '' END [MarkerB]
    , CASE WHEN CustCount > 1 AND [Year] = MaxYear - 1 THEN 'Y' ELSE '' END [MarkerC]
    , CASE WHEN CustCount > 1 AND [Year] < MaxYear - 1 THEN 'Y' ELSE '' END [MarkerD]
FROM src

Here's how the case statements work:

  1. Will only be true if only one Customer record exists
  2. Will be true if multiple Customer records exist, but the year equals max year
  3. Will be true if multiple Customer records exist, but the year equals max year - 1
  4. Will be true if multiple Customer records exist, but the year is less that max year - 1.

I commented out the derived column DescOrderIdx because while its not need in my solution, it might be needed to fit your exact requirements. If the first order is not to be marked, then checking if that value isn't equal to 1 (the most recent order index) should be used as additional criteria.

Upvotes: 2

Related Questions