SQL Server Performance

Query Issue

Discussion in 'SQL Server 2008 General DBA Questions' started by AJITH123, Sep 30, 2010.

  1. AJITH123 Member

    Hi Guys,
    I have a table, below is the sample script, i tried to pivot the table based on the status (below mentioned query) i am getting 1 record set,since i used max(date) criteria, but I should get 3 records based on status.
    CREATE TABLE PV (ID INTEGER, STATUS VARCHAR(100),DATE DATETIME)
    INSERT INTO pv VALUES(1,'ACTIVE', '1/1/2010')
    INSERT INTO pv VALUES(1,'INACTIVE', '2/1/2010')
    INSERT INTO pv VALUES(1,'ACTIVE', '3/1/2010')
    INSERT INTO pv VALUES(1,'INACTIVE', '4/1/2010')
    INSERT INTO pv VALUES(1,'ACTIVE', '5/1/2010')
    INSERT INTO pv VALUES(1,'INACTIVE', '6/1/2010')
    SELECT * FROM PV
    SELECT ID, [ACTIVE] AcxtiveDate, [INACTIVE] InactiveDate
    FROM
    (
    SELECT ID,STATUS,DATE FROM PV
    ) P
    PIVOT
    (
    MAX (DATE)
    FOR STATUS
    IN ([ACTIVE], [INACTIVE])
    ) AS PVT
  2. Adriaan New Member

    Not sure why you should choose the PIVOT syntax, which is horrible in itself (certainly compared to the very elegant and simple TRANSFORM syntax from Jet-SQL).
    Unless I'm oversimplifying, this will do the same trick:
    SELECT ID, Status, MAX(Date)
    FROM PV
    GROUP BY ID, Status
    Only difference is you get two rows, instead of two columns.
  3. AJITH123 Member

    I need the result in separate column, i am expecting the below result as per the query
    ID ACTIVE DATE INACTIVE DATE
    ---- ----------------------- -------------------
    1 2010-01-01 00:00:00.000 2010-01-02 00:00:00.000
    1 2010-01-03 00:00:00.000 2010-01-04 00:00:00.000
    1 2010-01-05 00:00:00.000 2010-01-06 00:00:00.000
  4. Adriaan New Member

    So for a given active date, you want to find the next inactive date, for the same ID?
    What about a correlated subquery -
    SELECT x.ID, x.Date AS [ACTIVE DATE],
    (SELECT MIN(z.Date) FROM PV AS z WHERE z.ID = x.ID AND z.Status = 'Inactive' AND z.Date > x.Date) AS [INACTIVE DATE]
    FROM PV as x
    WHERE x.Status = 'Active'

Share This Page