SQL Query to find number of changes in ParamValue column per Paramaterid

  • How do I sql script to get the number of changes happened (False To True & True - False) per Paramterid with following versionids.

    CREATE TABLE IntegrationParameters

    (

    Parameterid INT,

    Paramvalue VARCHAR(10),

    versionid int

    )

    INSERT INTO IntegrationParameters VALUES(1,'False',1)

    INSERT INTO IntegrationParameters VALUES(1,'False',1)

    INSERT INTO IntegrationParameters VALUES(1,'True',2)

    INSERT INTO IntegrationParameters VALUES(1,'True',2)

    INSERT INTO IntegrationParameters VALUES(1,'False',3)

    INSERT INTO IntegrationParameters VALUES(2,'False',1)

    INSERT INTO IntegrationParameters VALUES(2,'False',1)

    INSERT INTO IntegrationParameters VALUES(2,'True',2)

    INSERT INTO IntegrationParameters VALUES(2,'True',2)

    INSERT INTO IntegrationParameters VALUES(2,'True',3)

    INSERT INTO IntegrationParameters VALUES(2,'False',3)
    INSERT INTO IntegrationParameters VALUES(2,True,4)

    With the above data, I am expecting output like Parameterid=1, changes=2 & Parameterid=2, Changes=3)

    Thanks
    Vishwanath

  • ;
    WITH param_changes AS
    (
        SELECT *, CASE WHEN LAG(ParamValue) OVER(PARTITION BY ParameterID ORDER BY VersionID) <> ParamValue THEN 1 ELSE 0 END AS change
        FROM IntegrationParameters
    )
    SELECT ParameterID, SUM(change)
    FROM param_changes
    GROUP BY ParameterID

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Thanks Drew. That worked!!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply