question

IT1 avatar image
IT1 asked

SQL Query

Hello I am trying to achieve something that might be very simple but I just cant seem to get my head around it. I have a table controlno encounterdate type 207118 7/16/2013 Store 207118 7/29/2013 Store 207118 8/29/2013 Store 207118 4/3/2014 Website 207118 4/3/2014 Store 207118 4/8/2014 Store 207118 4/8/2014 Website 207118 4/21/2014 Store 207118 4/21/2014 Website 207118 7/8/2014 Website 207118 7/9/2014 Website SO I am trying to figure out if a customer had a website visit 6 months before the store visit. Could you please help me out Thanks guys !!
t-sql
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This web site runs on voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
Dave_Green avatar image
Dave_Green answered
If you mean exactly six months prior, then you can join the table to itself, and put the six months in the join criteria. If you are looking at a more range based query then you will need to adjust appropriately. Something like: select * from table as a inner join table as b on a.controlno=b.controlno --Same customer and a.encounterdate = dateadd(mm,-6,b.encounterdate) --six months difference in time where a.type = 'website' and b.type = 'store'
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

iainrobertson avatar image
iainrobertson answered
I think you need to be a bit clearer on the requirements. Do you mean exactly 6 months, or any visit in the six months preceding the store visit? How about same day visits? Do you just want the latest store visit or all of them? Anyhow, you should be able to get what you need out of this. Note that I've created test data for you. Please post test data in future, this makes it easier for people to help and makes it more likely that you'll get responses. -- setup test data create table #Visits (controlno int not null, encounterdate datetime not null, visittype varchar(7) not null) go -- this might not be appropriate for your data but speeds up the result query alter table #Visits add constraint pk_visits primary key clustered (controlno,visittype,encounterdate desc) set dateformat mdy insert #Visits select 207118, '7/16/2013','Store' union all select 207118, '7/29/2013','Store' union all select 207118, '8/29/2013','Store' union all select 207118, '4/3/2014','Website' union all select 207118, '4/3/2014','Store' union all select 207118, '4/8/2014','Store' union all select 207118, '4/8/2014','Website' union all select 207118, '4/21/2014','Store' union all select 207118, '4/21/2014','Website' union all select 207118, '7/8/2014','Website' union all select 207118, '7/9/2014','Website' go ------------------------------------------------------- -- compare each store visit to preceding website visits ------------------------------------------------------- -- isolate store visits ; with cteStoreVisits as ( select controlno , stv_encounterdate = encounterdate from #Visits stv where visittype = 'Store' ) -- join in any preceding or same day website visits (to make it only preceding, change the join operator to >) , cteCombinedVisits as ( select cte.* , wsv_encounterdate -- this counter lets us pick the first website visit before the store visit , filter = row_number() over (partition by cte.controlno, cte.stv_encounterdate order by wsv.wsv_encounterdate desc) from cteStoreVisits cte left join ( -- left join to pick up store visits without a prior website visit select controlno , wsv_encounterdate = encounterdate from #Visits where visittype = 'Website' ) wsv on cte.controlno = wsv.controlno and cte.stv_encounterdate >= wsv.wsv_encounterdate ) -- return the results select controlno , Store = stv_encounterdate , Website = wsv_encounterdate , StoreWithin6AfterWebsite = case when dateadd(m,-6,stv_encounterdate)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.