none
IF LOGIC

    Question

  • hi

    i need to know how to see previous value

    example, i have two coumns

               category       reason        program

                active            open             1

    now for category      reason we can have following value

                 active           open

                 inactive        close

                not avaiable   

                not rechable

    Now if value for category change to inactive and close , i need to do print something.thsi is in stored proc , so each time when u run either it is active or inactive. i  want to know previosuly for that particular program if was open and active

    and now its closed and inactive.

    any help

    Friday, January 24, 2014 11:25 PM

Answers

  • Hi,

    Try like this ,

    DECLARE @tmp TABLE (category VARCHAR(15),reason VARCHAR(15),program INT)
    INSERT @tmp SELECT 'Active','Open',1
    INSERT @tmp SELECT 'InActive','Close',1
    INSERT @tmp SELECT 'Active','Open',2
    INSERT @tmp SELECT 'Active','not avaiable',2
    INSERT @tmp SELECT 'InActive','Close',2
    INSERT @tmp SELECT 'Active','Open',3
    INSERT @tmp SELECT 'Active','not rechable ',3
    INSERT @tmp SELECT 'InActive','Close',3
    INSERT @tmp SELECT 'InActive','Close',4
    
    --SELECT * FROM @tmp
    
    ;With CTE
    AS
    (
    SELECT *,row_number() OVER(PARTITION BY program ORDER BY program) PSeq 
    FROM @tmp
    )
    SELECT MIN(B.category) category,
           MIN(B.reason) reason,
    	   B.program,
    	   MIN(B.PSeq) PSeq FROM Cte A 
    JOIN Cte B ON A.program = B.program
    AND A.PSeq = B.PSeq+1
    GROUP BY B.program
                    
    
               
    
    
    
    


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Saturday, January 25, 2014 6:10 AM
    Moderator

All replies

  • The structure of your tables does not make sense. You should have a column/s that will be the base for sorting the data, in order to know Which record comes before (in short... what is the order).

    Please post a DDL+DML, so we could help you more easily!


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituach Saturday, January 25, 2014 12:15 AM
    Saturday, January 25, 2014 12:13 AM
  • Hi,

    Try like this ,

    DECLARE @tmp TABLE (category VARCHAR(15),reason VARCHAR(15),program INT)
    INSERT @tmp SELECT 'Active','Open',1
    INSERT @tmp SELECT 'InActive','Close',1
    INSERT @tmp SELECT 'Active','Open',2
    INSERT @tmp SELECT 'Active','not avaiable',2
    INSERT @tmp SELECT 'InActive','Close',2
    INSERT @tmp SELECT 'Active','Open',3
    INSERT @tmp SELECT 'Active','not rechable ',3
    INSERT @tmp SELECT 'InActive','Close',3
    INSERT @tmp SELECT 'InActive','Close',4
    
    --SELECT * FROM @tmp
    
    ;With CTE
    AS
    (
    SELECT *,row_number() OVER(PARTITION BY program ORDER BY program) PSeq 
    FROM @tmp
    )
    SELECT MIN(B.category) category,
           MIN(B.reason) reason,
    	   B.program,
    	   MIN(B.PSeq) PSeq FROM Cte A 
    JOIN Cte B ON A.program = B.program
    AND A.PSeq = B.PSeq+1
    GROUP BY B.program
                    
    
               
    
    
    
    


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Saturday, January 25, 2014 6:10 AM
    Moderator
  • For checking those column value transitions you need to have a set of reference field(s) based on which you do the comparison. But you've not given us any information on other columns in table. If you can provide the information and also specify primary key (unique valued columns) we will be able to help.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, January 25, 2014 7:23 AM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you failed completely). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. You do not know anything about basic data modeling. There is no generic “category” ; has to be a particular kind of category. But a category is not a status. 

    >> I have two columns <<

    Then your ASCII picture shows three columns. If you were polite and knew a little data modeling, is this what you would have posted? 

    CREATE TABLE Foobar_Programs
    (program_nbr INTEGER NOT NULL PRIMARY KEY,
     program_status VARCHAR(15) NOT NULL
     CHECK(program_status 
     IN ('active', 'inactive', 'not available', 'not reachable')),
     seating_available CHAR(5) NOT NULL
     CHECK(seating_available IN ('open', 'closed'));

    Since you posted no specs, I will guess (open/closed) has to with available seats. 

    >> Now if value for seating_available changes to inactive and close, I need to print something. This is in stored procedure, so each time when you run either it is active or inactive. I want to know previously for that particular program if was open and active and now its closed and inactive. <<

    Where is the code for this stored procedure? Previously?  Based on what? There is no timestamp in your useless picture.  

    Try again. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, January 25, 2014 5:59 PM