July 16, 2009 at 6:58 am
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
1 01/jan/09 null 1
2 04/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
July 16, 2009 at 7:08 am
dcarpenter (7/16/2009)
I have a table in my database like the one belowTable - 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
1 01/jan/09 null 1
2 04/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:
PlacementID UserID CreatedOn
1 1 2009-01-01 00:00:00
2 2 2009-01-04 00:00:00
3 2 2009-01-05 00:00:00
4 2 2009-01-08 00:00:00
5 1 2009-01-12 00:00:00
6 1 2009-01-15 00:00:00
PlacementID Date1 Date2 UserID
1 2009-01-01 00:00:00 NULL 1
2 2009-01-04 00:00:00 NULL 2
3 2009-01-05 00:00:00 2009-01-04 00:00:00 2
4 2009-01-08 00:00:00 2009-01-05 00:00:00 2
5 2009-01-12 00:00:00 2009-01-01 00:00:00 1
6 2009-01-15 00:00:00 2009-01-12 00:00:00 1
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
WHERE pc2.UserID = pc1.UserID
AND pc2.CreatedOn < pc1.CreatedOn
ORDER BY CreatedOn DESC
) AS Date2,
UserID
FROM @PlacementConsultant pc1
July 16, 2009 at 7:41 am
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
July 16, 2009 at 2:42 pm
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