July 18, 2011 at 1:10 pm
USE [DB-7-18-11]
GO
/****** Object: StoredProcedure [dbo].[GetJobListIndexBrowse] Script Date: 07/18/2011 14:57:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetJobListIndexBrowse]
-- Add the parameters for the stored procedure here
@Title varchar(250),
@Industry int,
@JobTitle int,
@State int,
@City int,
@fromRec int,
@toRec int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Begin
WITH resumes AS
(
SELECT
p.Id,
--e.Title,
--p.Name as CompanyName,
p.PostalCode,
(select w.Position from workexperiences w where w.ProfileId=p.id and w.endyear = (select max(endyear) from workexperiences we where we.ProfileId = p.id)) as Position,
(select top 1 documentname from documents d where d.profileid = p.id and documentstatusid=1) as documentname,
(select i.name from industries i inner join workexperiences w on w.industryid = i.id where w.ProfileId=p.id and w.endyear = (select max(endyear) from workexperiences we where we.ProfileId = p.id)) as Industry,
isnull((select ci.[Name] from cities ci where ci.id=p.cityid),'') as City,
isnull((select r.[AbbreviatedName] from regions r where r.id=p.regionid), '') as State,
isnull((select co.[Abbreviatedname] from countries co where co.id=p.countryid),'') as Country,
-- substring(cast(e.deadline as varchar(20)),1,12) as deadline,
ROW_NUMBER() OVER (ORDER BY p.id DESC) AS RowNumber
from dbo.Profiles p
where
--CASE
-- WHEN @Title <> '' THEN
--
-- CASE WHEN Position like '%' + @Title + '%' THEN 1
-- ELSE 0 END
-- ELSE 1 END = 1
--and
--CASE
-- WHEN @Industry <> 0 THEN
-- CASE WHEN c.industryid = @Industry THEN 1
-- ELSE 0 END
-- ELSE 1 END = 1
--and
CASE
WHEN @State <> 0 THEN
CASE WHEN p.regionid = @State THEN 1
ELSE 0 END
ELSE 1 END = 1
and
CASE
WHEN @City <> 0 THEN
CASE WHEN p.CityId = @City THEN 1
ELSE 0 END
ELSE 1 END = 1
)
SELECT
Id,
industry,
Position,
documentname,
City + ', ' + State + '. ' + PostalCode + ', ' + Country as Location,
RowNumber,
(select count(Position) from Resumes
where
CASE
WHEN @Title <> '' THEN
CASE WHEN Position like '%' + @Title + '%' THEN 1
ELSE 0 END
ELSE 1 END = 1
and
CASE
WHEN @Industry <> 0 THEN
CASE WHEN industry = @Industry THEN 1
ELSE 0 END
ELSE 1 END = 1
) as TotalCount
FROM Resumes
WHERE RowNumber BETWEEN @fromRec AND @toRec
and
CASE
WHEN @Title <> '' THEN
CASE WHEN Position like '%' + @Title + '%' THEN 1
ELSE 0 END
ELSE 1 END = 1
and
CASE
WHEN @Industry <> 0 THEN
CASE WHEN industry = @Industry THEN 1
ELSE 0 END
ELSE 1 END = 1
--GROUP By CompanyName
--ORDER BY RowNumber DESC
ORDER BY RowNumber ASC
end
end
sample input data
@Title varchar(250), - CEO
@Industry int, -0
@JobTitle int, -0
@State int, -0
@City int, -0
@fromRec int, -1
@toRec int -10
Actual Results
D4685790-16AF-4970-A73D-FC26DD756C9AEnergy and UtilitiesCEOResume-6-1-2011 1-41-50 PM.docxTampa, FL. 33619, USA16
12FD827A-34E1-4F58-8B07-DFCE18C5F6C6Engineering ServicesCEOResume-6-1-2011 1-48-18 PM.docxTampa, FL. 33619, USA46
A86FF600-55EC-4324-A0E8-5F5A5B78CD3CEnergy and UtilitiesCEOResume-6-1-2011 1-38-15 PM.docxTampa, FL. 33619, USA96
I am missing three records. When i expand the ToRec to '20' i get the following results:
D4685790-16AF-4970-A73D-FC26DD756C9AEnergy and UtilitiesCEOResume-6-1-2011 1-41-50 PM.docxTampa, FL. 33619, USA16
12FD827A-34E1-4F58-8B07-DFCE18C5F6C6Engineering ServicesCEOResume-6-1-2011 1-48-18 PM.docxTampa, FL. 33619, USA46
A86FF600-55EC-4324-A0E8-5F5A5B78CD3CEnergy and UtilitiesCEOResume-6-1-2011 1-38-15 PM.docxTampa, FL. 33619, USA96
920AC5E0-19D8-4718-818C-4507FFA4E326Energy and UtilitiesCEOResume-6-1-2011 1-31-06 PM.docxTampa, FL. 33619, USA116
4921007A-AEE6-48C8-BD17-4222E5C4570EEngineering ServicesCEOResume-6-1-2011 1-54-31 PM.docxTampa, FL. 33619, USA126
C1C1C35B-DD32-40B8-B479-1C3A0C5314F1Aerospace and DefenseCEOResume-6-6-2011 12-25-59 PM.docxTampa, FL. 33619, USA146
How do i get ALL six(6) records when i use fromRec=1 and ToRec=10?
It seems that if the RowNumber is > than 10 it does not display all the records on the first page, with page count = 10.
July 18, 2011 at 1:50 pm
Without seeing your sample data, I had to make some assumptions. Don't where clause AFTER you initiate the ROW_NUMBER(). That's what's removing the rows on you. ROW_NUMBER() should be generated in the last statement before display, and where claused there.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 18, 2011 at 1:53 pm
a few issues:
a) what do you mean by "not display all the records on the first page, with page count = 10"? Are we talking about a Reporting Services issue or a problem with the stored procedure?
b) we don't have anything to simulate the result set you get. There are no table def nor sample data nor anything like that. How are we supposed to find any possible cause of the issue you're struggling with? Please have a look at the first link in my signature on how to post ready to use sample data.
c) There are a bunch of rows that seem to be the result set. Where are the differences? Is this some kind of a puzzle you're expecting us to solve?
Please note: neither do we know the business case you're struggling with nor can we look over your should to see more details. You need to help us help you by providing more detailed information. Otherwise you'll end up with another two dozend people looking at your question and simply move on...
July 18, 2011 at 1:55 pm
Craig Farrell (7/18/2011)
Without seeing your sample data, I had to make some assumptions. Don't where clause AFTER you initiate the ROW_NUMBER(). That's what's removing the rows on you. ROW_NUMBER() should be generated in the last statement before display, and where claused there.
@Craig: since the OP is looking for 6 out of 10, the WHERE clause might just be what is needed. :unsure:
July 18, 2011 at 2:27 pm
Craig Farrell (7/18/2011)
Without seeing your sample data, I had to make some assumptions. Don't where clause AFTER you initiate the ROW_NUMBER(). That's what's removing the rows on you. ROW_NUMBER() should be generated in the last statement before display, and where claused there.
Thanks!
July 18, 2011 at 2:32 pm
Using
@fromRec int, -1
@toRec int -10
you are going to get row_numbers 1 through 10
on the between statement @fromRec and @toRec
So your results are correct, there is no way to get the last three records unless you expand the between
to encapsulate which would be
@fromRec int, -1
@toRec int -14 (or 20 like you have shown us).
What you might need to do is add this column:
ROW_NUMBER() OVER (ORDER BY RowNumber) AS 'FinalRowCount'
in your select statement against the CTE and then change the between to reflect.
FinalRowCount BETWEEN @fromRec AND @toRec
July 18, 2011 at 3:17 pm
LutzM (7/18/2011)
Craig Farrell (7/18/2011)
Without seeing your sample data, I had to make some assumptions. Don't where clause AFTER you initiate the ROW_NUMBER(). That's what's removing the rows on you. ROW_NUMBER() should be generated in the last statement before display, and where claused there.@Craig: since the OP is looking for 6 out of 10, the WHERE clause might just be what is needed. :unsure:
Hrm, yes, but I was assuming the second set of records was between rownumbers 11-20, but there's only 6 results, so that's why it's off.
This would take sample data and tweaking to really nail down. Too many assumptions. Very good point though. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 18, 2011 at 4:03 pm
The second set of records was:
FromRec = 1
ToRec = 20
Thanks!
July 18, 2011 at 4:12 pm
jwbart06 (7/18/2011)
Using@fromRec int, -1
@toRec int -10
you are going to get row_numbers 1 through 10
on the between statement @fromRec and @toRec
So your results are correct, there is no way to get the last three records unless you expand the between
to encapsulate which would be
@fromRec int, -1
@toRec int -14 (or 20 like you have shown us).
What you might need to do is add this column:
ROW_NUMBER() OVER (ORDER BY RowNumber) AS 'FinalRowCount'
in your select statement against the CTE and then change the between to reflect.
FinalRowCount BETWEEN @fromRec AND @toRec
Do you mean like this ?
USE [DB]
GO
/****** Object: StoredProcedure [dbo].[GetJobListIndexBrowse] Script Date: 07/18/2011 18:03:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetJobListIndexBrowse]
-- Add the parameters for the stored procedure here
@Title varchar(250),
@Industry int,
@JobTitle int,
@State int,
@City int,
@fromRec int,
@toRec int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Begin
WITH resumes AS
(
SELECT
p.Id,
--e.Title,
--p.Name as CompanyName,
p.PostalCode,
(select w.Position from workexperiences w where w.ProfileId=p.id and w.endyear = (select max(endyear) from workexperiences we where we.ProfileId = p.id)) as Position,
(select top 1 documentname from documents d where d.profileid = p.id and documentstatusid=1) as documentname,
(select i.name from industries i inner join workexperiences w on w.industryid = i.id where w.ProfileId=p.id and w.endyear = (select max(endyear) from workexperiences we where we.ProfileId = p.id)) as Industry,
isnull((select ci.[Name] from cities ci where ci.id=p.cityid),'') as City,
isnull((select r.[AbbreviatedName] from regions r where r.id=p.regionid), '') as State,
isnull((select co.[Abbreviatedname] from countries co where co.id=p.countryid),'') as Country,
-- substring(cast(e.deadline as varchar(20)),1,12) as deadline,
ROW_NUMBER() OVER (ORDER BY p.id DESC) AS RowNumber,
ROW_NUMBER() OVER (ORDER BY RowNumber) AS 'FinalRowCount'
from dbo.Profiles p
where
--CASE
-- WHEN @Title <> '' THEN
--
-- CASE WHEN Position like '%' + @Title + '%' THEN 1
-- ELSE 0 END
-- ELSE 1 END = 1
--and
--CASE
-- WHEN @Industry <> 0 THEN
-- CASE WHEN c.industryid = @Industry THEN 1
-- ELSE 0 END
-- ELSE 1 END = 1
--and
CASE
WHEN @State <> 0 THEN
CASE WHEN p.regionid = @State THEN 1
ELSE 0 END
ELSE 1 END = 1
and
CASE
WHEN @City <> 0 THEN
CASE WHEN p.CityId = @City THEN 1
ELSE 0 END
ELSE 1 END = 1
)
SELECT
Id,
industry,
Position as Title,
documentname,
City + ', ' + State + '. ' + PostalCode + ', ' + Country as Location,
RowNumber,
(select count(Position) from Resumes
where
CASE
WHEN @Title <> '' THEN
CASE WHEN Position like '%' + @Title + '%' THEN 1
ELSE 0 END
ELSE 1 END = 1
and
CASE
WHEN @Industry <> 0 THEN
CASE WHEN industry = @Industry THEN 1
ELSE 0 END
ELSE 1 END = 1
) as TotalCount
FROM Resumes
WHERE
--RowNumber BETWEEN @fromRec AND @toRec
FinalRowCount BETWEEN @fromRec AND @toRec
and
CASE
WHEN @Title <> '' THEN
CASE WHEN Position like '%' + @Title + '%' THEN 1
ELSE 0 END
ELSE 1 END = 1
and
CASE
WHEN @Industry <> 0 THEN
CASE WHEN industry = @Industry THEN 1
ELSE 0 END
ELSE 1 END = 1
--GROUP By CompanyName
--ORDER BY RowNumber DESC
ORDER BY RowNumber ASC
end
end
Thanks!
July 18, 2011 at 5:38 pm
You are close but I believe you need to remove the statement
ROW_NUMBER() OVER (ORDER BY RowNumber)AS 'FinalRowCount'
from the CTE expression and add it to the outer Select statement
for the between to work.
Like this:
USE [DB]
GO
/****** Object: StoredProcedure [dbo].[GetJobListIndexBrowse] Script Date: 07/18/2011 18:03:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetJobListIndexBrowse]
-- Add the parameters for the stored procedure here
@Title varchar(250),
@Industry int,
@JobTitle int,
@State int,
@City int,
@fromRec int,
@toRec int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Begin
WITH resumes AS
(
SELECT
p.Id,
--e.Title,
--p.Name as CompanyName,
p.PostalCode,
(select w.Position from workexperiences w where w.ProfileId=p.id and w.endyear = (select max(endyear) from workexperiences we where we.ProfileId = p.id)) as Position,
(select top 1 documentname from documents d where d.profileid = p.id and documentstatusid=1) as documentname,
(select i.name from industries i inner join workexperiences w on w.industryid = i.id where w.ProfileId=p.id and w.endyear = (select max(endyear) from workexperiences we where we.ProfileId = p.id)) as Industry,
isnull((select ci.[Name] from cities ci where ci.id=p.cityid),'') as City,
isnull((select r.[AbbreviatedName] from regions r where r.id=p.regionid), '') as State,
isnull((select co.[Abbreviatedname] from countries co where co.id=p.countryid),'') as Country,
-- substring(cast(e.deadline as varchar(20)),1,12) as deadline,
ROW_NUMBER() OVER (ORDER BY p.id DESC) AS RowNumber
from dbo.Profiles p
where
--CASE
-- WHEN @Title <> '' THEN
--
-- CASE WHEN Position like '%' + @Title + '%' THEN 1
-- ELSE 0 END
-- ELSE 1 END = 1
--and
--CASE
-- WHEN @Industry <> 0 THEN
-- CASE WHEN c.industryid = @Industry THEN 1
-- ELSE 0 END
-- ELSE 1 END = 1
--and
CASE
WHEN @State <> 0 THEN
CASE WHEN p.regionid = @State THEN 1
ELSE 0 END
ELSE 1 END = 1
and
CASE
WHEN @City <> 0 THEN
CASE WHEN p.CityId = @City THEN 1
ELSE 0 END
ELSE 1 END = 1
)
SELECT
Id,
industry,
Position as Title,
documentname,
City + ', ' + State + '. ' + PostalCode + ', ' + Country as Location,
RowNumber,
(select count(Position) from Resumes
where
CASE
WHEN @Title <> '' THEN
CASE WHEN Position like '%' + @Title + '%' THEN 1
ELSE 0 END
ELSE 1 END = 1
and
CASE
WHEN @Industry <> 0 THEN
CASE WHEN industry = @Industry THEN 1
ELSE 0 END
ELSE 1 END = 1
) as TotalCount ,
ROW_NUMBER() OVER (ORDER BY RowNumber)AS 'FinalRowCount'
FROM Resumes
WHERE
--RowNumber BETWEEN @fromRec AND @toRec
FinalRowCount BETWEEN @fromRec AND @toRec
and
CASE
WHEN @Title <> '' THEN
CASE WHEN Position like '%' + @Title + '%' THEN 1
ELSE 0 END
ELSE 1 END = 1
and
CASE
WHEN @Industry <> 0 THEN
CASE WHEN industry = @Industry THEN 1
ELSE 0 END
ELSE 1 END = 1
--GROUP By CompanyName
--ORDER BY RowNumber DESC
ORDER BY RowNumber ASC
end
end
July 18, 2011 at 6:10 pm
FinalRowCount BETWEEN @fromRec AND @toRec
I keep getting invalid column name(FinalRowCount) on this line. Any idea why?
Thanks!
July 19, 2011 at 7:39 am
This is a quick example of what I mean, you must put FinalRowCount on the outer Select
create table #thisISit
(
id smallint null,
name nvarchar(6) null
)
insert into #thisISit
select 1 as 'id' ,'dave' as 'name'
union select 2, 'ted'
union select 3, 'greg'
union select 4, 'dan'
union select 5,'james'
union select 6,'bob'
GO
with rowsRus
as
(
select ID,Name,ROW_NUMBER() OVER (order by id desc) as rowsNumber
from #thisISit
)
select ID,Name,rowsNumber,ROW_NUMBER() OVER (order by rowsNumber ) as 'FinalRowCount' from rowsRus
WHERE rowsNumber in ( 1,3,5)
GO
drop table #thisISit
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply