June 20, 2013 at 4:10 am
Sorry to be a pain, can you tell me how i can return the top performer as well as the additional three rows?
June 20, 2013 at 4:18 am
.Netter (6/20/2013)
Sorry to be a pain, can you tell me how i can return the top performer as well as the additional three rows?
The top performer would have rownr = 1 (the resultset is in descending order by number of occurrances). So to get you desired result change the WHERE clause to "WHERE rownr <= 4"
June 20, 2013 at 4:29 am
hi thanks,
This is the snippet, i think i may have the replaced something incorrectly as now it returns 10 rows.....
;with cte_select as
(select
ROW_NUMBER() over (order by count(p.ReviewedBy) desc) as rownr
, u.ShortAbbr
, u.firstname + ' ' + u.surname as Datachecker
, count(p.ReviewedBy) as total
from dbo.UserAccount u
inner join dbo.ProspectLead p
on p.ReviewedBy = u.ShortAbbr
and convert(date, p.ReviewedDate) = convert(date, SYSDATETIME())
and u.Responsibility = 16
group by u.ShortAbbr
, u.firstname + ' ' + u.surname
)
select *
from cte_select
WHERE rownr <= (SELECT rownr - 1
from cte_select
where shortAbbr = 'DA7')
and rownr <= (SELECT rownr + 1
from cte_select
where shortabbr = 'DA7')
I changed the where clause from >= to <=
June 20, 2013 at 4:44 am
.Netter (6/20/2013)
WHERE rownr <= (SELECT rownr - 1
from cte_select
where shortAbbr = 'DA7')
and rownr <= (SELECT rownr + 1
from cte_select
where shortabbr = 'DA7')
You have changed the WHERE clause to a double comparison, both using the same "smaller then or equal to". So this results in a single select of everything smaller then or equal to the largest number.
To select the top 4 performers it's independent from a specific name (ShortAbbr). So you have to remove that column from the WHERE clause. Replace the complete WHERE clause above to the WHERE clause below to get only the top 4 performers:
WHERE rownr <= 4
June 20, 2013 at 5:00 am
I ok i understand,
But doing that will return the first top 4 agents,
I still need to have
My position
person above me
person below me
+ the top performer
?
June 20, 2013 at 5:12 am
I'm sorry, I didn't understood you wanted all the results in one set. To get this you need to add an OR statement to the WHERE clause;
select *
from cte_select
WHERE
(rownr >= (SELECT rownr - 1
from cte_select
where shortAbbr = 'DA7')
and rownr <= (SELECT rownr + 1
from cte_select
where shortabbr = 'DA7')
)
OR
rownr <= 4 -- to select the TOP 4 performers
June 20, 2013 at 5:18 am
thanks,
I made the amendments, in the code you provided you had <= 4 which again returned more then the desired results
so i done this
;with cte_select as
(select
ROW_NUMBER() over (order by count(p.ReviewedBy) desc) as rownr
, u.ShortAbbr
, u.firstname + ' ' + u.surname as Datachecker
, count(p.ReviewedBy) as total
from dbo.UserAccount u
inner join dbo.ProspectLead p
on p.ReviewedBy = u.ShortAbbr
and convert(date, p.ReviewedDate) = convert(date, SYSDATETIME())
and u.Responsibility = 16
group by u.ShortAbbr
, u.firstname + ' ' + u.surname
)
select *
from cte_select
WHERE (rownr >= (SELECT rownr - 1
from cte_select
where shortAbbr = 'DA7')
and rownr <= (SELECT rownr + 1
from cte_select
where shortabbr = 'DA7')
)
OR
rownr = 1 -- to select the TOP 4 performers
rownr = 1 and added an order by rownr and it looks good 🙂
Thanks for your time really do appreciate it.
June 24, 2013 at 2:23 am
Hi Sorry to bring this post back to life, but is there a way i can also return the users that have a count of 0?
As this query requires the individual to have a count greater then 0?
So say i logged in today and i haven't checked anything so the ReviewedBy Column inside prospect lead would be nulll
Because at the moment when i join Prospectlead.ReviewedBy = useraccount.ShortAbbr (it doesnt return anything purely because i havent reviewed anything yet)
I should be at the bottom of the table so
14th someone 0
15th Me 0
June 24, 2013 at 2:33 am
To get a list of users without a lead, you'll have to change the INNER JOIN to a LEFT OUTER JOIN. You'll also need to change the join-criteria to accept NULL values. left outer join dbo.ProspectLead p
on p.ReviewedBy = u.ShortAbbr
and (convert(date, p.ReviewedDate) = convert(date, SYSDATETIME())
OR
p.ReviewedDate is NULL)
To make the code more readable, you can move the additional join-criteria (the ones that don't include both tables) to the WHERE clause.
To test your code first execute it without a WHERE to see the complete result. Change any ordering when required and start adding the WHERE filter bit by bit untill the result is what you want.
June 24, 2013 at 3:07 am
Hi thanks for the infomation seems to be working correctly when i choose admin.1 being the top performer
but as soon as i choose the last admin user for example d13 i get indivduals that arent even in the same department as the datacheckers?
I should only be seeing datacheckers im not sure what im actualy doing wrong here, iv extended the And clause inside the main select to reflect the datacheckers responsibility and location,Department
but it still fails?
;with cte_select as
(select
ROW_NUMBER() over (order by count(p.ID) desc) as CurrentPosition,
u.ShortAbbr,
case u.ShortAbbr
when 'd13' then 'You'
else u.firstname + ' ' + u.surname
end as Datachecker,
count(p.ReviewedBy) as total
from dbo.UserAccount u
left outer join dbo.ProspectLead p
on p.ReviewedBy = u.ShortAbbr
and (convert(date, p.ReviewedDate) = convert(date, SYSDATETIME()) OR p.ReviewedDate is NULL)
and (u.Responsibility = 16 and u.Department = 7 and u.Location = 7)
group by u.ShortAbbr, u.firstname + ' ' + u.surname
)
select *
from cte_select
WHERE (CurrentPosition >= (SELECT CurrentPosition - 1 -- Get the agent before @ShortAbbr passed in
from cte_select
where shortAbbr = 'd13')
and CurrentPosition <= (SELECT CurrentPosition + 1 -- Get the agent after @ShortAbbr passed in
from cte_select
where shortabbr = 'd13')
)
OR
CurrentPosition = 1 -- Get the Top Performance with row number 1
order by CurrentPosition
June 24, 2013 at 3:16 am
Its working i ammended the second select statement
select *
from cte_select
WHERE (CurrentPosition >= (SELECT CurrentPosition - 1 -- Get the agent before @ShortAbbr passed in
from cte_select join UserAccount u on cte_select.shortabbr = u.ShortAbbr
where cte_select.shortAbbr = 'd12' and u.Responsibility = 16)
and CurrentPosition <= (SELECT CurrentPosition + 1 -- Get the agent after @ShortAbbr passed in
from cte_select join UserAccount u on cte_select.shortabbr = u.ShortAbbr
where cte_select.shortabbr = 'd12' and u.Responsibility = 16)
)
OR
CurrentPosition = 1 -- Get the Top Performance with row number 1
order by CurrentPosition
Thanks for your additional help!
June 24, 2013 at 3:21 am
OK i spoke to soon,
Iv just run the snippet which returned this
1DA1Admin 15
250D11Admin 110
251D12You0
252D13Admin 130
first column being the position?
it should say
1DA1Admin 15
11D11Admin 110
12D12You0
13D13Admin 130
June 24, 2013 at 6:37 am
In the main select is returns every single user i have in the DB even though i have specified what users i want i.e
Reponsibility, Department and location as soon as i introduced the left outer join it looks like it disregards the Responsibility,Department and location and returns all users,
When it comes to the second select to filter out the information, as its returned all the users in the previous select (cte_Select) it will display what I have mentioned previously.
How can i stop this from happening? so instead of returning all users, just return the users by the particular Responsibility, Department and location ?
June 24, 2013 at 6:57 am
.Netter (6/24/2013)
In the main select is returns every single user i have in the DB even though i have specified what users i want i.eReponsibility, Department and location as soon as i introduced the left outer join it looks like it disregards the Responsibility,Department and location and returns all users,
When it comes to the second select to filter out the information, as its returned all the users in the previous select (cte_Select) it will display what I have mentioned previously.
How can i stop this from happening? so instead of returning all users, just return the users by the particular Responsibility, Department and location ?
Hi
I am sure that this can be solved....but reading back thro all your posts and trying to determine your actual problem....takes a lot of time.
suggestion....post some code (create table and insert data scripts) along with your expected results for this sample data.
will be a lot easier for someone to give you a tried and tested solution.
best wishes
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 24, 2013 at 6:57 am
Move the "(u.Responsibility = 16 and u.Department = 7 and u.Location = 7)" part to a WHERE clause, just before the GROUP BY. You could also move it to a HAVING clause, just after the GROUP BY, but I don't think that will give better performance.
Because this filter only references the [UserAccount] table, the JOIN isn't the correct place for this.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply