Seleting prev. N rows from current row (ad-hoc)

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)

Seleting prev. N rows from current row (ad-hoc)

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2012-02-06 : 10:50:57

Hello,

I have data that looks similar to this:


Table TblData

DataID     Data                           Type
000001     [01/01/01]Here is some text    Name
000001     This is a search engine        Content         
000001     www.google.com                 URL
000002     [02/10/11]Again, some text     Name
000002     This is a portal               Content
000002     www.yahoo.com                  URL

Some initial conditions:
DataID is a unique field, and there are always three rows per DataID number and each row will have a Type of Name, Content, or URL. Whenever there is a date in the Data column, the Type is Name. So, in each set of three rows, you have one row of Type Name, one row of Type Content, and one row of type URL: That is for sure.

I want to use an ad-hoc query, not some T-SQL method like a stored procedure.

What I am trying to do is select all rows where the date is within two years of the current date AND those rows associated with it, via the DataID field. For example, with today's date as 02/06/2012, the query should return all rows with DataID of 000002 and look something like this:


Results:

DataID     Data                           Type           DateCreated
000002     [02/10/11]Again, some text     Name           2011-02-10
000002     This is a portal               Content        2011-02-10
000002     www.yahoo.com                  URL            2011-02-10

Here is the query I have so far and the results follow it:


SELECT
        DataID,
        Data,
        Type,
        ROW_NUMBER() OVER(ORDER BY DataID) RN,
        CASE
          WHEN Data LIKE '[[][0-9][0-9]/[0-9][0-9]/[0-9][0-9]]%' THEN CAST(SUBSTRING(Data, 2, 8) as DATE)
        END 'DateCreated'
FROM 
        TblData


Results:

DataID     Data                           Type         RN      DateCreated
000001     [01/01/01]Here is some text    Name         1       2001-01-01
000001     This is a search engine        Content      2       NULL
000001     www.google.com                 URL          3       NULL
000002     [02/10/98]Again, some text     Name         4       2011-02-10
000002     This is a portal               Content      5       NULL
000002     www.yahoo.com                  URL          6       NULL

Now, I know how to write the expression for querying a date between today and two years from today. The major concern I have is how do I include the rows in the query that don't have a date in the Data field? I thought that, perhaps, there is a way to compare the previous N rows with the current row in one ad-hoc query, without writing a stored procedure. If it's a match, the DateCreated field gets a date. If there is no match, it's left as NULL. Then, I select all rows where the DateCreated field is not NULL.

Is what I'm trying to achieve possible with a (complex) ad-hoc query? Or, must I write a stored procedure?

Thank you very much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 11:47:26

[code]
;With Temp_Data
AS
(
SELECT *
FROM
(
SELECT
DataID,
Data,
Type,
CAST(SUBSTRING(Data, 2, 8) as DATE) AS [DateCreated]
FROM
TblData
WHERE Type = 'Name'
)t
WHERE DateCreated > DATEADD(yy,DATEDIFF(yy,0,GETDATE())-2,0)
UNION ALL
SELECT tbl.DataID,
tbl.Data,
tbl.Type,
NULL
FROM Temp_Data t
INNER JOIN TblData tbl
ON tbl.DataID = t.DataID
AND tbl.[Type] IN ('URL','Content')
)
SELECT DataID,
Data,
Type
FROM Temp_Data
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2012-02-06 : 15:44:57

Thank you very much! I think this will work; however, I am receiving an error:

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

I set the maxrecursion to 0 for the query, but it's taking a very, very long time and still has not finished executing ( I eventually stopped it).

Is this expected or normal?

Thank you.

Update: I'll write out my query and observe if that will offer any insight. Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 16:03:14

dont recurse then!


;With Temp_Data
AS
(
SELECT *
FROM
(
SELECT
        DataID,
        Data,
        Type,
        CAST(SUBSTRING(Data, 2, 8) as DATE) AS [DateCreated]
FROM 
        TblData
WHERE Type    = 'Name'    
)t
WHERE DateCreated > DATEADD(yy,DATEDIFF(yy,0,GETDATE())-2,0)
)
SELECT *
FROM Temp_Data 
UNION ALL
SELECT  tbl.DataID,
        tbl.Data,
        tbl.Type,
        NULL
FROM Temp_Data t
INNER JOIN TblData tbl
ON tbl.DataID = t.DataID
AND tbl.[Type] IN ('URL','Content')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2012-02-06 : 16:35:01

Thank you very much for your help (No need to shout, by the way). I modified the query to the above; in both implementations, however, the DateCreated field is NULL for those of Type 'URL' and 'Content'. Please allow me to post the exact query employed in the database below.

Once again, thank you and any one else who may respond for their help. It is appreciated.


;With Temp_Data
AS
(
SELECT *
FROM (
SELECT
        DataID,
        Data,
        Type,
        CAST(SUBSTRING(Data, 2, 8) AS DATE) AS [DateCreated]
FROM 
        PrimaryData PD
          INNER JOIN
        SecondaryData SD ON PD.DataID=SD.DataID and PD.StatusCode=SD.StatusCode
WHERE 
        PD.StatusCode=0 AND PD.Type = 'Name'
)t
WHERE DateCreated > DATEADD(YY,DATEDIFF(YY,0,GETDATE())-2,0)
)
SELECT *
FROM Temp_Data
UNION ALL
SELECT  
  PD.DataID,
  PD.Data,
  PD.Type,
  NULL
FROM Temp_Data t
INNER JOIN PrimaryData PD
ON PD.DataID = t.DataID
AND PD.Type IN ('URL','Content')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 16:53:11

sorry didnt understand the issue. Your sample data also showed date value as NULL for them right?


DataID     Data                           Type         RN      DateCreated
000001     [01/01/01]Here is some text    Name         1       2001-01-01
000001     This is a search engine        Content      2       NULL
000001     www.google.com                 URL          3       NULL

...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2012-02-06 : 17:02:14

My apologies if I was unclear in stating the issue.

Yes, that is correct, the sample data I have currently shows NULL for the DateCreated field.

However, what I would like the result set to look like is the following:


Results:

DataID     Data                           Type           DateCreated
000002     [02/10/11]Again, some text     Name           2011-02-10
000002     This is a portal               Content        2011-02-10
000002     www.yahoo.com                  URL            2011-02-10

If there is no viable solution, it's no problem. I could always write a stored proc.

I know nobody gets paid to write posts; so, I sincerely appreciate you or anybody else who may respond, taking time out of their lives for me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 17:03:54

[code]
;With Temp_Data
AS
(
SELECT *
FROM (
SELECT
DataID,
Data,
Type,
CAST(SUBSTRING(Data, 2, 8) AS DATE) AS [DateCreated]
FROM
PrimaryData PD
INNER JOIN
SecondaryData SD ON PD.DataID=SD.DataID and PD.StatusCode=SD.StatusCode
WHERE
PD.StatusCode=0 AND PD.Type = 'Name'
)t
WHERE DateCreated > DATEADD(YY,DATEDIFF(YY,0,GETDATE())-2,0)
)
SELECT *
FROM Temp_Data
UNION ALL
SELECT
PD.DataID,
PD.Data,
PD.Type,
t.DateCreated
FROM Temp_Data t
INNER JOIN PrimaryData PD
ON PD.DataID = t.DataID
AND PD.Type IN ('URL','Content')
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2012-02-06 : 17:08:49

Yes, that did the trick. Thank you for your help, sir.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 18:25:19

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources