Can this be done????

  • I have a table in my database like the one below

    Table - placementconsultant

    placementid PK

    userid

    createdon

    I'm trying to write a statment that select the createdon

    How do I turn this ---

    placementid userid createdon

    1 1 01/jan/09

    2 2 04/jan/09

    3 2 05/jan/09

    4 2 08/jan/09

    5 1 12/jan/09

    6 1 15/jan/09

    into this ---

    placementid date1 date2 userid

    101/jan/09 null 1

    204/jan/09 null 2

    3 05/jan/09 04/jan/09 2

    4 08/jan/09 05/jan/09 2

    5 12/jan/09 01/jan/09 1

    6 15/jan/09 12/jan/09 1

  • dcarpenter (7/16/2009)


    I have a table in my database like the one below

    Table - placementconsultant

    placementid PK

    userid

    createdon

    I'm trying to write a statment that select the createdon

    How do I turn this ---

    placementid userid createdon

    1 1 01/jan/09

    2 2 04/jan/09

    3 2 05/jan/09

    4 2 08/jan/09

    5 1 12/jan/09

    6 1 15/jan/09

    into this ---

    placementid date1 date2 userid

    101/jan/09 null 1

    204/jan/09 null 2

    3 05/jan/09 04/jan/09 2

    4 08/jan/09 05/jan/09 2

    5 12/jan/09 01/jan/09 1

    6 15/jan/09 12/jan/09 1

    If I read this right, what you want is to have a query which shows you the date of the placementID, as well as the last date for the same user. In that case, something like this should work:

    Output:

    PlacementIDUserIDCreatedOn

    112009-01-01 00:00:00

    222009-01-04 00:00:00

    322009-01-05 00:00:00

    422009-01-08 00:00:00

    512009-01-12 00:00:00

    612009-01-15 00:00:00

    PlacementIDDate1Date2UserID

    12009-01-01 00:00:00NULL1

    22009-01-04 00:00:00NULL2

    32009-01-05 00:00:002009-01-04 00:00:002

    42009-01-08 00:00:002009-01-05 00:00:002

    52009-01-12 00:00:002009-01-01 00:00:001

    62009-01-15 00:00:002009-01-12 00:00:001

    DECLARE @PlacementConsultant TABLE

    (

    PlacementID INT PRIMARY KEY IDENTITY,

    UserID INT,

    CreatedOn SMALLDATETIME

    )

    INSERT INTO @PlacementConsultant (UserID, CreatedOn)

    VALUES (1, '2009-01-01')

    INSERT INTO @PlacementConsultant (UserID, CreatedOn)

    VALUES (2, '2009-01-04')

    INSERT INTO @PlacementConsultant (UserID, CreatedOn)

    VALUES (2, '2009-01-05')

    INSERT INTO @PlacementConsultant (UserID, CreatedOn)

    VALUES (2, '2009-01-08')

    INSERT INTO @PlacementConsultant (UserID, CreatedOn)

    VALUES (1, '2009-01-12')

    INSERT INTO @PlacementConsultant (UserID, CreatedOn)

    VALUES (1, '2009-01-15')

    SELECT * FROM @PlacementConsultant

    SELECT

    PlacementID,

    CreatedOn AS Date1,

    (

    SELECT TOP 1 CreatedOn

    FROM @PlacementConsultant pc2

    WHEREpc2.UserID = pc1.UserID

    AND pc2.CreatedOn < pc1.CreatedOn

    ORDER BY CreatedOn DESC

    ) AS Date2,

    UserID

    FROM @PlacementConsultant pc1

  • or

    SELECT T1.placementId, T1.userId, T1.createdOn

    ,MAX(T2.createdOn) AS PrevDate

    FROM placementconsultant T1

    LEFT JOIN placementconsultant T2

    ON T2.userId = T1.UserId

    AND T2.createdOn < T1.createdOn

    GROUP BY T1.placementId, T1.userId, T1.createdOn

    or

    CREATE TABLE #temp

    (

    placementId int NOT NULL

    ,userId int NOT NULL

    ,createdOn datetime NOT NULL

    ,RowNo int NOT NULL

    ,PRIMARY KEY CLUSTERED (userId, RowNo)

    )

    INSERT INTO #temp

    SELECT placementId, userId, createdOn

    ,ROW_NUMBER() OVER (PARTITION BY userId ORDER BY createdOn)

    FROM placementconsultant

    SELECT T1.placementId, T1.userId, T1.createdOn

    ,T2.createdOn AS PrevDate

    FROM #temp T1

    LEFT JOIN #temp T2

    ON T2.userId = T1.UserId

    AND T2.RowNo = T1.RowNo -1

  • dcarpenter,

    If I'm understand correctly, you might want to try this....

    MAX(CASE WHEN table.column = 'value' THEN table.column ELSE NULL END) as [new column name]

    This will esentially pivot a static value that you specify into the new column.

    Otherwise, if you have dynamic values you might want to try a crosstab query.

    Michelle

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

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