May 18, 2012 at 11:34 pm
I am trying to create a stored procedure where i would like to fetch the next previous and current rows from a particularl pagename which is stored in a table with sorting of dateadded
like my products table structure
id
iQty
strProductTitle
strPageName
DtAdded
DtApproved
my dummy records are like
1 13 'PCHDD' 'PCHDD' '2009-12-03 04:32:30.363' '2009-12-04 04:32:30.363'
2 30 'SDRAM' 'SD_Ram' '2009-12-03 04:32:30.363' '2009-12-06 04:32:30.363'
3 12 'Pen Drive' 'Pendrive' '2009-12-03 04:32:30.363' '2009-12-05 04:32:30.363'
4 3 'Note Book' 'NoteBook' '2009-12-03 04:32:30.363' '2009-12-08 04:32:30.363'
5 15 'VIO' 'VIO' '2009-12-03 04:32:30.363' '2009-12-06 04:32:30.363'
6 19 'PS2' 'PS_2' '2009-12-03 04:32:30.363' '2009-12-09 04:32:30.363'
7 31 'PS3' 'PS_3' '2009-12-03 04:32:30.363' '2009-12-02 04:32:30.363'
8 23 'WII' 'WII' '2009-12-03 04:32:30.363' '2009-12-10 04:32:30.363'
9 22 'Speakers' 'Speakers' '2009-12-03 04:32:30.363' '2009-12-16 04:32:30.363'
10 12 'Iphone' 'I_phone' '2009-12-03 04:32:30.363' '2009-12-15 04:32:30.363'
now if i query for a record like
I_phone
i should get
Prv Detail I_pad row
11 23 'Ipad' 'I_pad' '2009-12-03 04:32:30.363' '2009-12-11 04:32:30.363'
Current Detail I_phone
10 12 'Iphone' 'I_phone' '2009-12-03 04:32:30.363' '2009-12-15 04:32:30.363'
Next Record Speakers
9 22 'Speakers' 'Speakers' '2009-12-03 04:32:30.363' '2009-12-16 04:32:30.363'
please help in creating a stored procedure
May 19, 2012 at 4:31 am
The sample data you provided has a number of problems. DtAdded is the same for every row. There is no row 11 (specified in the results). To write a solution, I would have work out what you meant to say, fix the data to reflect that, and then write CREATE TABLE and INSERT statements just so I can get some data to play with.
Someone else may have the time to do that for you, but until those issues are corrected it's too hard to help you.
May 19, 2012 at 5:09 am
May 19, 2012 at 6:51 am
Hello
Sorry SSChampion my mistake dtadded is a unique key and i just made the data and forget to change the date
that's correct Old Hand again apologies there is a 11th row
11 23 'Ipad' 'I_pad' '2009-12-03 04:32:30.363' '2009-12-11 04:32:30.363'
"ha ha ha now i am even laughing at my self what blunder i did in asking question" but the logic behind my query is, i want to retrieve 3 rows with all values of previous row current row and next row and the sort parameter should be with dtapproved
a friend of mine suggested me one query but it retrieved only 1 row with values current pagename next pagename previous pagename
SELECT [Previous].strProductTitle AS [Prv],
[Current].strProductTitle AS [Current],
[Next].strProductTitle AS [Next]
FROM YourTable AS [Current]
OUTER APPLY
(SELECT TOP(1) [Previous].strProductTitle
FROM YourTable AS [Previous]
WHERE [Previous].DtApproved < [Current].DtApproved
ORDER BY [Previous].DtApproved DESC) AS [Previous]
OUTER APPLY
(SELECT TOP(1) [Next].strProductTitle
FROM YourTable AS [Next]
WHERE [Next].DtApproved > [Current].DtApproved
ORDER BY [Next].DtApproved) AS [Next]
WHERE [Current].strProductTitle = 'Iphone'
but my main issue is still same i want all the details of these rows not just pagename
May 19, 2012 at 7:20 am
I see you asked the exact same question on SQLteam:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=174825
The reason you are getting an error with the VALUES clause on that thread is because you are not running SQL Server 2008, or are running in a database with a lower compatibility level set. On both sites, you asked your question in the 2008 forums, so you are getting 2008-specific answers.
You will get faster help if you provide accurate data for your question, in a form people without access to your database can quickly use. For example, rather than describing the column names and a comma-separated list of values:
CREATE TABLE #Products
(
ProductID integer PRIMARY KEY CLUSTERED,
Quantity tinyint NOT NULL,
ProductName nvarchar(50) NOT NULL UNIQUE,
PageName nvarchar(20) NOT NULL,
DateAdded date NOT NULL,
DateApproved date NOT NULL
);
INSERT #Products
(ProductID, Quantity, ProductName, PageName, DateAdded, DateApproved)
VALUES
(01, 13, 'PCHDD', 'PCHDD', '', ''),
(02, 30, 'SDRAM', 'SD_Ram', '', ''),
(03, 12, 'Pen Drive', 'Pendrive', '', ''),
(04, 03, 'Note Book', 'NoteBook', '', ''),
(05, 15, 'VIO', 'VIO', '', ''),
(06, 19, 'PS2', 'PS_2', '', ''),
(07, 31, 'PS3', 'PS_3', '', ''),
(08, 23, 'WII', 'WII', '', ''),
(09, 22, 'Speakers', 'Speakers', '', ''),
(10, 12, 'Iphone', 'I_phone', '', ''),
(11, 23, 'Ipad', 'I_pad', '', '');
I haven't filled any date values in there because there is not enough detail in your question to do so.
My other advice to you is to try to learn some of this material for yourself. Building your own skills is going to repay the effort many times over, and will be much more productive than copying questions to every help site you can find and waiting for someone else to do the whole thing for you.
May 19, 2012 at 7:55 am
Thank Paul 😉 I am even searching for some place where i can learn these topics i have knowledge but not on these advanced topics which were introduced in 2005 and 2008 sql servers now one more favor please suggest me some websites where i can update these skills of these new topics .... and sorry for posting on different websites just have some quick requirements.Sometimes u have to if its not in your territory ... and advice taken ....
May 20, 2012 at 9:28 pm
Maybe I'm oversimplifying this but here is one possible way:
DECLARE @Products TABLE
(
ProductID integer PRIMARY KEY CLUSTERED,
Quantity tinyint NOT NULL,
ProductName nvarchar(50) NOT NULL UNIQUE,
PageName nvarchar(20) NOT NULL,
DateAdded DATETIME NOT NULL,
DateApproved DATETIME NOT NULL
);
INSERT @Products
(ProductID, Quantity, ProductName, PageName, DateAdded, DateApproved)
SELECT 01, 13, 'PCHDD', 'PCHDD', '', ''
UNION ALL SELECT 02, 30, 'SDRAM', 'SD_Ram', '', ''
UNION ALL SELECT 03, 12, 'Pen Drive', 'Pendrive', '', ''
UNION ALL SELECT 04, 03, 'Note Book', 'NoteBook', '', ''
UNION ALL SELECT 05, 15, 'VIO', 'VIO', '', ''
UNION ALL SELECT 06, 19, 'PS2', 'PS_2', '', ''
UNION ALL SELECT 07, 31, 'PS3', 'PS_3', '', ''
UNION ALL SELECT 08, 23, 'WII', 'WII', '', ''
UNION ALL SELECT 09, 22, 'Speakers', 'Speakers', '', ''
UNION ALL SELECT 10, 12, 'Iphone', 'I_phone', '', ''
UNION ALL SELECT 11, 23, 'Ipad', 'I_pad', '', ''
SELECT p2.ProductID, rec, p2.Quantity, p2.ProductName, p2.PageName -- etc.
FROM @Products p1
CROSS APPLY (
SELECT -1, 'Next' UNION ALL SELECT 0, 'Current' UNION ALL SELECT 1, 'Previous') x(n,rec)
INNER JOIN @Products p2 ON p2.ProductID = p1.ProductID + x.n
WHERE p1.ProductID = 10
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply