February 5, 2014 at 2:55 am
Hi all
I've got to be able to pick up the first and last records from a list and I need to split them by reference number.
I can't post any data becuase it's sensitive but I can explain what each item is if necessary.
Here's my code so far:-
;with cte as (
SELECT
AD.VisitID
,AD.UnitNumber AS UnitNumber
,AD.AccountNumber AS AccountNumber
,ws.WardID AS Ward
,ws.WardLocation
,ws.ServiceSeqID AS ServiceSeqID
,ws.WardSeqID AS WardSeqID
,row_number() over (partition by
AD.UnitNumber
,AD.AccountNumber
order by
AD.VisitID) as [Index]
FROM
livedb_daily.dbo.AbstractData AD
INNER JOIN livedb_daily.dbo.AbsPatUkWardStays ws
ON ws.AbstractID = AD.AbstractID
WHERE
AD.AccountNumber IN ('RA0001054857','RA0000057844')
)
select
VisitID
,UnitNumber
,AccountNumber
,Ward
,WardLocation
,ServiceSeqID
,WardSeqID
,[Index]
from
cte
where
[Index]=1
The [Index] field is pulling back the correct values and is being split as I want it.
I need to pick up the first and last [Index] number for each of the UnitNumber and AccountNumber fields.
The first one is easy (as in the code above) but I can't figure out a way to get the last [Index] field for each UnitNumber/AccountNumber.
Anyone any ideas please?
February 5, 2014 at 3:48 am
Hia ll
I've got the query sorted out but it's horrendously slow!
This is my updated query:-
;with cte as (
SELECT
AD.VisitID
,AD.UnitNumber AS UnitNumber
,AD.AccountNumber AS AccountNumber
,ws.WardID AS Ward
,ws.WardLocation
,ws.ServiceSeqID AS ServiceSeqID
,ws.WardSeqID AS WardSeqID
,row_number() over (partition by
AD.UnitNumber
,AD.AccountNumber
order by
AD.VisitID) as [Index]
FROM
livedb_daily.dbo.AbstractData AD
INNER JOIN livedb_daily.dbo.AbsPatUkWardStays ws
ON ws.AbstractID = AD.AbstractID
WHERE
AD.AccountNumber IN ('RA0001054857','RA0000057844','RA0000058290')
)
select
a.VisitID
,a.UnitNumber
,a.AccountNumber
,a.Ward
,a.WardLocation
,a.ServiceSeqID
,a.WardSeqID
,a.[Index]
,case
when a.[index]=1 then 'First'
else 'Last'
end as [Flag]
from
cte a
where
a.[Index]=1
or (a.visitid+cast(a.[index] as varchar) in (select
b.visitid+cast(max(b.[index]) as varchar)
from
cte b
group by
b.visitid))
order by
a.visitid
,a.[index]
The cte section takes less than 2 seconds to pull back 24 rows (I added an extra item in the where clause).
It then takes another 20+ seconds to sort out the bits I actually want.
I think it's down to the subquery inn the main SELECT statement.
Can anyone have a look at the code I'm using and help speed it up please?
::edit::
And now with the execution plan attached.
The fields are as follows:-
a.VisitID - varchar
a.UnitNumber - varchar
a.AccountNumber - varchar
a.Ward - varchar
a.WardLocation - varchar
a.ServiceSeqID - int
a.WardSeqID - int
February 5, 2014 at 6:36 am
For the last index you can use ROW_NUMBER function again with a small change in the OVER clause:
,row_number() over (
partition by AD.UnitNumber ,AD.AccountNumber order by AD.VisitID) as [Index]
,row_number() over (
partition by AD.UnitNumber ,AD.AccountNumber order by AD.VisitID DESC) as [IndexDesc]
When you query cte you can use Index = 1 for the first and IndexDesc = 1 for the last index.
From the execution plan you posted it seems that an index is missing on the column AbstractID in the table AbsPatUkWardStays. Therefore this table is scanned (in your plan twice) and this take significant amount of time and ressources.
So, you can try to add an index in mentioned table and add another ROW_NUMBER() function.
___________________________
Do Not Optimize for Exceptions!
February 5, 2014 at 6:42 am
Another way:
,row_number() over (
partition by AD.UnitNumber,AD.AccountNumber order by AD.VisitID) as [Index]
,COUNT(*) over (partition by AD.UnitNumber,AD.AccountNumber) as [MaxIndex]
filter on [Index] = 1 OR [Index] = [MaxIndex]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 5, 2014 at 6:49 am
milos - Couldn't quite get yours to reorder the index descending.
ChrisM = Works perfectly, thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply