Help Regarding SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help Regarding SP

Author

Topic

namanthakral
Starting Member

4 Posts

Posted - 2012-05-17 : 04:40:27

Please help in my websites i am trying to fetch next record previous record for a perticularl row with DtApproved sorting i am trying this but hard luck kindly help...

my db structure

id
iQty
strProductTitle
strPageName
DtAdded
DtApproved

lets suppose 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'
11 23 'Ipad' 'I_pad' '2009-12-03 04:32:30.363' '2009-12-11 04:32:30.363'

now if i query for a record like
I_phone
i should get
Prv Detail I_pad
Current Detail I_phone
Next Record Speakers

please help me fetching records

Naman Thakral

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-05-17 : 07:05:06

Try something like this:


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'

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

namanthakral
Starting Member

4 Posts

Posted - 2012-05-17 : 07:38:05

Thanks Muhammad Al Pasha u made my day.
if i want to fetch these values in 3 diff rows not in just 1 row then what should i do ???

Naman Thakral

namanthakral
Starting Member

4 Posts

Posted - 2012-05-17 : 07:42:00

like i want to fetch all 3 rows of these records like row 1 for previous row 2 for current and row 3 for next.

Naman Thakral

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-05-17 : 09:04:18

Try something like this:


SELECT T.strProductTitle
  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]
       CROSS APPLY
       (SELECT T.strProductTitle
          FROM (VALUES([Previous].strProductTitle),
                      ([Current].strProductTitle), 
                      ([Next].strProductTitle)) AS T(strProductTitle)) AS T
 WHERE [Current].strProductTitle = 'Iphone';

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

namanthakral
Starting Member

4 Posts

Posted - 2012-05-18 : 04:08:48

Thanks but now i am getting
this error while retrieving values
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'VALUES'.

Naman Thakral

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources