Post reply

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

  • February 14, 2017 at 11:36 am

    #335285

    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

  • February 14, 2017 at 3:09 pm

    #1928506

    ;
    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

  • February 14, 2017 at 3:18 pm

    #1928508

    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