June 20, 2013 at 2:09 am
hello,
Im having a bit of trouble trying to return the current users position, iv been looking at this for the past 3 days browsed many forums tried many situations but with still no luck.
I have a table called prospectlead which has a column called ReviewedBy this gets populated when the user checks a record etc
the desired output would be
1st John
2nd Me
3rd Sarah
This will obviously change throughout the day depending on how the individuals get on, so mid morning i could go from 2nd position down to 10th
but i need to return my position plus the person above me and the person below me, the way im getting the amount of records checked is by using a COUNT, the parameter passed in to the stored procedure is the ShortAbbr which would be 'D13' (again thats hard coded to get it working, it would actaully be @ShortAbbr passed in from the front end)
Can any one help me on this please?
Select ROW_NUMBER() over(order by u.UserID) as RowNumber,
count(p.ID) as TotalCount, u.Firstname + ' ' + u.Surname as DataChecker
from ProspectLead p join UserAccount u on p.reviewedby = u.ShortAbbr
where p.ReviewedBy is not null
and convert(date, p.ReviewedDate) = convert(date, SYSDATETIME())
and u.Responsibility = 16
and u.ShortAbbr = 'D13'
group by u.Firstname, u.Surname, u.UserID order by TotalCount desc
June 20, 2013 at 2:15 am
Position as per what column? What column in the table records that order?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2013 at 2:33 am
Hi GilaMontser,
The position would be created depending on how many records the user has checked(ReviewedBy in prospectLead), there is a total of 15 users that check the data on a daily basis
so out of them 15 users i need to say your current
position 12th (User Above me)
position 13th (Me)
position 14th (user below me)
obviously the position will change throughout the day
I hope i answered your question correctly.
June 20, 2013 at 2:45 am
Position as determined by the data in what column?
If a user is number 15, what column determines that? What column value determines if a user is before that or after that?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2013 at 2:55 am
The count is by the Column ReviewedBy which stores the users shortabbr vachar(3) (So i count how many records the users has done by that column by passing in the shortabbr and out of the 15 users i want to say your position is... the person above you is.... the person below you is....)
there isnt a column to say the user is 15 that's just how many users i currently have in the DB that check the records (I added that additional information purely because if someone asked me how many people are doing the checking)
What column value determines if a user is before that or after that?
It all depends on the total records checked by the user so i didnt no if i could get all the RowNumbers, then find the users by the shortabbr passed in then select that one plus the one above and the one below?
June 20, 2013 at 3:03 am
Ok, so the 1st user is the one who has the most rows with their name in the ReviewedBy, the 2nd user is the one with the second most rows with their name in the ReviewedBy?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2013 at 3:05 am
Could you provide some sample data ("create table" and "insert" statements) and state the desired output using this sample data. That way we can help you better and there would be probably less discussion.
June 20, 2013 at 3:06 am
GilaMonster (6/20/2013)
Ok, so the 1st user is the one who has the most rows with their name in the ReviewedBy, the 2nd user is the one with the second most rows with their name in the ReviewedBy?
Thats correct!
But again out of all the users i could be position 9 or 4 or 11 or depends on how many records have been checked by each individual (again this will change as the day progresses)
June 20, 2013 at 3:18 am
i tried creating this on SQLFiddle but again this didnt work for some reason,
its a cut down version of the prospectlead table and this table has loads of columns
CREATE TABLE ProspectLead (id bigint IDENTITY(1,1) NOT NULL,
ReviewedBy varchar(3));
INSERT INTO ProspectLead VALUES ('1', 'D13'),
('2', 'D11'),
('3', 'D12'),
('4', 'D14'),
('5', 'D09');
('6', 'D13');
('7', 'D13');
('8', 'D15');
CREATE TABLE Users (id INTEGER IDENTITY(1,1) NOT NULL,
ShortAbbr varchar(3),
firstname varchar(3),
surname varchar(3));
INSERT INTO Users VALUES (1, 'D13', 2),
(2, 'D11', 1),
(3, 'D12', 9),
(3, 'D15', 9),
(3, 'D09', 9),
(3, 'D14', 9);
June 20, 2013 at 3:33 am
Will this do the trick?
select
ROW_NUMBER() over (order by count(ReviewedBy) desc) as rownr
, ShortAbbr
, firstname
, surname
, count(ReviewedBy)
from dbo.Users
inner join dbo.ProspectLead
on firstname = ReviewedBy
group by ShortAbbr
, firstname
, surname
order by
count(ReviewedBy) desc
You could wrap the above code in another select if you only want a selection of the resultset.
June 20, 2013 at 3:42 am
HanShi (6/20/2013)You could wrap the above code in another select if you only want a selection of the resultset.
This is the code if you want only the row for a specific person (including the row before and after):
;with cte_select as
(select
ROW_NUMBER() over (order by count(ReviewedBy) desc) as rownr
, ShortAbbr
, firstname
, surname
, count(ReviewedBy) as total
from dbo.Users
inner join dbo.ProspectLead
on firstname = ReviewedBy
group by ShortAbbr
, firstname
, surname
)
select *
from cte_select
WHERE rownr >= (SELECT rownr - 1
from cte_select
where firstname = 'D09')
and rownr <= (SELECT rownr + 1
from cte_select
where firstname = 'D09')
June 20, 2013 at 3:44 am
Hi Thanks for the snippet,
That returns all individuals + the amount they have checked,
Im going to be passing in the shortabbr from the front end which will then call this stored procedure and in return ideally i want
my position 9th
the person above 8th
the person below me 10th
Not all records, I looked at top 3 but that would only return me the top three (as expected so that isnt suitable)
June 20, 2013 at 3:48 am
HanShi (6/20/2013)
HanShi (6/20/2013)You could wrap the above code in another select if you only want a selection of the resultset.
This is the code if you want only the row for a specific person (including the row before and after):
;with cte_select as(select
ROW_NUMBER() over (order by count(ReviewedBy) desc) as rownr
, ShortAbbr
, firstname
, surname
, count(ReviewedBy) as total
from dbo.Users
inner join dbo.ProspectLead
on firstname = ReviewedBy
group by ShortAbbr
, firstname
, surname
)
select *
from cte_select
WHERE rownr >= (SELECT rownr - 1
from cte_select
where firstname = 'D09')
and rownr <= (SELECT rownr + 1
from cte_select
where firstname = 'D09')
Hi thanks for the snippet,
im getting no results returned?
iv amended the query
;with cte_select as
(select
ROW_NUMBER() over (order by count(p.ReviewedBy) desc) as rownr
, u.ShortAbbr
, u.firstname
, u.surname
, 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 firstname = 'd13')
and rownr <= (SELECT rownr + 1
from cte_select
where firstname = 'd13')
When i do the main select i.e this
select
ROW_NUMBER() over (order by count(p.ReviewedBy) desc) as rownr
, u.ShortAbbr
, u.firstname
, u.surname
, 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
It returns all which is fine, but as soon as i introduce the second select it returns nothing
June 20, 2013 at 3:55 am
Your provided sample code is probably a bit different from your actual data. The values 'D09' and 'D13' in your sample code are put in the column "Firstname". In you actual data it will be in column "ShortAbbr". You have to adjust my code to match your actual situation.
June 20, 2013 at 3:58 am
Correct, i changed firstname to shortabbr seems to be working 🙂
I cant thank you all enough for helping me on this.
Really was rattling my brain.
Many thanks!
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply