January 23, 2013 at 3:45 pm
Hi all,
My data contains something like this: userid, web_survey_date, rating, comment.
We send surveys out to our customers for each service done, the customer replies with good/bad etc..Somehow (either process/computer issue, I am not sure), the ratings and comments broken out into 2 surveys ending up looking like 2 completely different surveys.
user id | web_survey_date| rating_1| rating_2| comment|
1 | 01/23/2013 11:56 | Good | Good | No Comment
1 | 01/23/2013 13:43 | N/A | N/A | This guy was good
Esentially, I want to be able to write an sql using rating1 and rating 2 from the earliest web_survey_date, and the comment from the latest web_survey date, can somebody help please?? There are about 10000+ records. 🙁
What I have so far is:
With CTE AS(
Select userid, web_survey_date, rating_1, rating_2, comment
min(web_survey_date) over (partition by user id) min_date
from table_1)
Select userid, web_survey_date, rating_1, rating_2, comment
from CTE
where web_survey_date = min_date
group by userid, web_survey_date, rating_1, rating_2, comment
Thanks,
Alan
January 23, 2013 at 4:17 pm
Hi,
Try:
With CTE AS
(
Select
userid,
web_survey_date,
rating_1,
rating_2,
comment,
ROW_NUMBER() over(partition by userid order by web_survey_date) RowNumAsc,
ROW_NUMBER() over(partition by userid order by web_survey_date desc) RowNumDesc
from table_1
)
Select
a.userid,
a.web_survey_date,
a.rating_1,
a.rating_2,
d.comment
from CTE as a
inner join CTE as d
on d.userid = a.userid and
d.RowNunDesc = 1
where a.RowNumAsc = 1
Hope this help.
January 23, 2013 at 5:05 pm
Hi imex, thanks for your quick reply.
I just discovered I only need the latest web_survey_date for each user id, how to do that?
Is it possible to complete this within a single SELECT statement and avoid the WITH statement in the beginning? I find that the performance takes WAY longer with the WITH statement.
January 23, 2013 at 5:58 pm
Can you post a sample of data of this table and its expected result?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply