April 2, 2009 at 12:45 am
I need to get the latest records of each userID for each month. The following scenario is furnished below.
TABLE
-----------------------
UserIDparam_valueEnterDtm
10011512008-02-26
10011822008-02-28
10021022008-02-28
10021522008-03-02
10031142008-03-15
10011322008-04-01
10051642008-04-05
10051452008-04-15
10031652008-04-15
10011202009-01-02
10011912009-01-25
---------------------------------
Expected results
---------------------------------
UserIDparam_valueEnterDtm
10011822008-02-28
10021522008-03-02
10021522008-03-02
10031142008-03-15
10011322008-04-01
10051452008-04-15
10031652008-04-15
10011912009-01-25
---------------------------------
Thanks in advance.
I simplified my current code as per below. From the code below, I am only able to get the latest record for each userID.
SELECT DISTINCT UserID, Param_value, performedDTM
FROM TABLE_1 a
WHERE a.performedDTM = (select top 1 performedDtm from TABLE_A Where UserID = a.UserID ORDER BY performedDTM desc)
ORDER BY a.clientDisplayName, o.PerformedDtm
April 2, 2009 at 1:28 am
Assuming that you're applying the query on SQL 2005 OLEDB Component, you can use the ranking function "ROW_NUMBER" to order the resultset. For e.g.
; WITH Users
AS
(
SELECT ROW_NUMBER() OVER( PARTITION BY UserID, DATEADD( MONTH, DATEDIFF( MONTH, 0, EnterDtm ), 0 ) ORDER BY param_value DESC ) AS RowNumber, *
FROM TABLE_1
)
SELECT UserID, param_value, EnterDtm
FROM Users
WHERE RowNumber = 1
--Ramesh
April 2, 2009 at 3:54 am
Thanks a lot!
I get the needed records! :-D:-D:-D
April 7, 2009 at 1:58 am
Another question...
How do I change the row data into column? Refer below:
Expected results (By Rows)
---------------------------------
UserID param_value EnterDtm
1001 182 2008-02-28
1002 152 2008-03-02
1003 114 2008-03-15
1001 132 2008-04-01
1005 145 2008-04-15
1003 165 2008-04-15
1001 191 2009-01-25
---------------------------------
Expected results (By Columns)
---------------------------------
UserID 2008-02 2008-03 2008-04 2009-01
1001 182 null 132 191
1002 null 152 null null
1003 null 114 165 null
1005 null null 145 null
---------------------------------
I know that we can use the pivot function, but I am not sure how to create the code...
Thanks in advance!
April 8, 2009 at 12:48 am
Anyone can help me?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply