January 23, 2014 at 6:23 am
set dateformat ymd
Create table #Visits
(
OrderNo int
,CrewOn datetime
,VisitType varchar (30)
)
------Set up visit data. All visits will have the same first visit type, subsequent ones will differ.
insert into #Visits (OrderNo ,CrewOn ,VisitType)
select 10000,'2014-01-01 19:00:00','Weigh Anchor' union all
select 10001,'2014-01-01 19:01:00','Weigh Anchor' union all
select 10002,'2014-01-01 19:01:00','Weigh Anchor' union all
select 10003,'2014-01-01 19:01:00','Weigh Anchor' union all
select 10004,'2014-01-01 19:02:00','Weigh Anchor' union all
select 10003,'2014-01-05 19:01:00','Set sail' union all
select 10003,'2014-01-05 19:01:00','Splice the Mainbrace' union all
select 10003,'2014-01-05 19:01:00','Mutiny' union all
select 10003,'2014-01-10 19:01:00','Reach the Spanish Main' union all
select 10004,'2014-01-11 19:02:00','Hit submerged reef' union all
select 10004,'2014-01-11 19:02:00','Shipwrecked'
-----Find the time of the first visit and the time of the second visit
select
sv.OrderNo
,FirstVisit = min(sv.CrewOn)
,SecondVisit = (
select min(v.crewon) from #Visits v
where v.OrderNo= sv.OrderNo
and v.CrewOn > (
select min(v2.crewon) from #Visits v2 where v2.OrderNo = v.OrderNo
)
)
into #times
from #Visits sv
group by sv.OrderNo
------Find the visit types that took place on the second visit
select
RowNo = ROW_NUMBER () over (partition by vv.orderno order by vv.crewon)
,vv.OrderNo
,visittype = vv.VisitType
into #multi
from #Visits vv
join #times ti on vv.OrderNo = ti.OrderNo
where vv.CrewOn = ti.SecondVisit
and vv.VisitType is not null
-----Pivot visit rows into columns
select
OrderNo = m.OrderNo
,[1] = max(case when m.RowNo = 1 then m.visittype else null end)
,[2] = max(case when m.RowNo = 2 then m.visittype else null end)
,[3] = max(case when m.RowNo = 3 then m.visittype else null end)
,[4] = max(case when m.RowNo = 4 then m.visittype else null end)
into #vString
from #multi m
group by m.OrderNo
------Edited highlight of main query; concatenate the visit type columns into one column to appear in SSRS
select
ti.OrderNo
,ti.FirstVisit
,ti.SecondVisit
,SecondVisitType = -------The bit I want to improve
case
when vs.[1] is nullthen 'None'
when vs.[2] is nullthen vs.[1]
when
vs.[2] is not null
and
vs.[3] is null
then vs.[1]+', '+ vs.[2]
when
vs.[3] is not null
and
vs.[4] is null
then vs.[1]+', '+ vs.[2]+', '+vs.[3]
when vs.[4] is not null
then vs.[1]+', '+ vs.[2]+', '+vs.[3]+', '+vs.[4]
end
from #times ti
left join #vString vs on vs.OrderNo = ti.OrderNo
Drop table #Visits,#multi,#times,#vString
I have a table of visit data where I am interested in the visit type or types that took place on the second visit. Each visit type from the Visits table has its own row (which is another problem, I'll fix that later) and I have to put them into the SecondVisitType column. I've found a way to do it, as shown above, and it's reasonably quick through the low thousands of rows I have in production. The thing is, I just don't like it, it just seems very clumsy and inelegant. I tried using coalesce and isnull to create the column but I ended up with commas after the last entry and in some cases there was a visit type missing. How can this be improved?
Edit Tidied up the code a bit
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 23, 2014 at 7:57 am
Here is another to do this using cte's. This produces the same results as your query except that for OrderNo 1003 it includes "Reach the Spanish Main" which is the fourth visit. The code you posted misses that one.
with VisitsCounted as
(
select OrderNo, CrewOn, VisitType, ROW_NUMBER() over (Partition by OrderNo order by CrewOn) as RowNum
from #Visits
)
, FirstVisits as
(
select CrewOn, VisitType, OrderNo
from VisitsCounted
where RowNum = 1
)
, SecondVisits as
(
select CrewOn, OrderNo,
STUFF((select ', ' + VisitType
from VisitsCounted vc1
where vc1.OrderNo = vc2.OrderNo
and vc1.RowNum >= 2
order by vc2.RowNum
for xml path('')), 1, 1, '') as VisitType
from VisitsCounted vc2
where RowNum = 2
group by CrewOn, OrderNo, RowNum
)
select fv.OrderNo, fv.CrewOn as FirstVisit, sv.CrewOn as SecondVisit, isnull(sv.VisitType, 'none') as SecondVisitType
from FirstVisits fv
left join SecondVisits sv on fv.OrderNo = sv.OrderNo
order by fv.OrderNo
In addition to using a couple of ctes to build this I am also creating a comma separated list of values using STUFF. Here is an excellent article that explains that in more detail.
http://www.sqlservercentral.com/articles/71700/[/url]
BTW, Excellent job posting ddl and sample data!!! Working on problems like this are a real joy because you made the effort to make it easy for others to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 23, 2014 at 8:14 am
Thanks Sean. I did try using CTE's when I was testing but oh boy did it slow down. I'd forgotten about STUFF, I'm very new to SQL and I'm doing a lot of feeling my way blindly.
One minor point though, 'Reach Spanish Main' isn't required in the SecondVisitType column. That actually took place on the third visit for that order number on the 10th of January. I included the later dates to make the Visits table more like the one I'm using in production. The main business points are that the first and second visits to an order, which actually represents a person, need to be made within certain timescales and everything after that can take place whenever suits.
I'll head away and try the STUFF approach on my test proc now.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 23, 2014 at 8:45 am
BWFC (1/23/2014)
Thanks Sean. I did try using CTE's when I was testing but oh boy did it slow down. I'd forgotten about STUFF, I'm very new to SQL and I'm doing a lot of feeling my way blindly.One minor point though, 'Reach Spanish Main' isn't required in the SecondVisitType column. That actually took place on the third visit for that order number on the 10th of January. I included the later dates to make the Visits table more like the one I'm using in production. The main business points are that the first and second visits to an order, which actually represents a person, need to be made within certain timescales and everything after that can take place whenever suits.
I'll head away and try the STUFF approach on my test proc now.
Hopefully it will work for you. Performance testing on the real tables will be key to be sure. Let me know what you find out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 24, 2014 at 4:14 am
Hi Sean
I've had a tinker with my proc and using the CTE approach works just as quickly as temp tables when using my method to create the combined column. I think that the slowness I experienced yesterday was down to a couple of rookie errors.
However, when I put the STUFF in it, it slows down horrendously. It's currently been running for 26 minutes, while the Cross Tab(?) approach runs in just over a minute. I'm going to keep exploring down that route though. One of the problems with my approach is that it only cope with as many Visit Types as I allow it to, although if there's ever any more than four I would be very surprised/annoyed. Could this be done dynamically or is that for another question?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 24, 2014 at 7:35 am
BWFC (1/24/2014)
Hi SeanI've had a tinker with my proc and using the CTE approach works just as quickly as temp tables when using my method to create the combined column. I think that the slowness I experienced yesterday was down to a couple of rookie errors.
However, when I put the STUFF in it, it slows down horrendously. It's currently been running for 26 minutes, while the Cross Tab(?) approach runs in just over a minute. I'm going to keep exploring down that route though. One of the problems with my approach is that it only cope with as many Visit Types as I allow it to, although if there's ever any more than four I would be very surprised/annoyed. Could this be done dynamically or is that for another question?
Not sure why it slowed down so much. Could be you can improve that with some indexing. Hard to say for sure without knowing the structure of your real tables.
You can do dynamic cross tabs. Take a look at the article in signature about dynamic cross tabs. It explains how to it in great detail.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply