data manipulation in T-sql

Question

165297-book3.txt

Hi Guys,
I need some help to obtain the below output. I have attached the input(book3.txt)

logic explanation - For every visitorid if there is a event_sequence with values 1 and 4 then Quote_complete=1

Answer 1

The data you post is difficult to work with as there seems to be a lot of duplicates. But to extract the visitorids with all four values for event_sequence, you can use this query:

SELECT vistorid
FROM   tbl
WHERE event_sequence IN (1, 2, 3, 4)
GROUP BY visitorid
HAVING COUNT(DISTINCT event_sequence) = 4

Note: the way to provide sample data is CREATE TABLE + INSERT statements, as that makes it easy to copy and paste into a query window to develop a tested solution.

Answer 2

Hi,@rdboy_9727
Please check this:

;with CTE as  
(  
 select event_day ,visitorid ,udo_session_id ,event_sequence,row_number()over(partition by visitorid order by event_sequence desc) as RNum  
 from #tablename  
)  
select event_day,visitorid,udo_session_id,event_sequence,  
       case when RNum = 1 and visitorid in   
	             (select visitorid from #tablename where event_sequence = 1   
                  intersect   
                  select visitorid from #tablename where event_sequence = 4  
				 )  
	        then '1' else '' end as Quote_complete  
from CTE   
order by event_day ,visitorid ,event_sequence,Quote_complete  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.