Sql Query to get data of previous of data exist

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

General SQL Server Forums

New to SQL Server Programming

Sql Query to get data of previous of data exist

Author

Topic

hameedf
Starting Member

27 Posts

Posted - 2012-06-15 : 07:30:54

Hello GURU's

I need a query that work on event data suppose event data is exist on date 1/1/2001 . The query check the date and go back to this date and fetch that row where the event data row exists.

Iam able to right some but it get the data of exist date or its previous date.


SELECT ITEM_NAME,CONVERT(VARCHAR(24),(START_DATETIME),3)
, CONVERT(Varchar, DATEPART(HH,START_DATETIME))+':00' 
,WELL_HEAD_PRES  
FROM TEST WHERE ITEM_NAME ='HF-01'  
AND VALID_TEST IN ('True','TRUE')  
AND CONVERT(Varchar(10),START_DATETIME,102)= '2011.11.01'  
AND DATEPART(HH,START_DATETIME)>=0

Any help would be highly apperciated thanks alot

regards
hammedf

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-15 : 07:40:49

select *
from tbl
where dte = '20010101'

Will give you the events from that date.
You don't say what you want to do if there isn't a row for that date.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hameedf
Starting Member

27 Posts

Posted - 2012-06-15 : 08:47:43

Thanks for your reply nigerlrivett.

Problem i have is that the data of (WELL_HEAD_PRES) I suppose my last row of data exists at 11/1/2011 and i pass the date 11/5/2011 the query look for last row exist then go back and get that row which is present before 11/1/2011?

Sorry if i cannot understand well?

Thanks
hameedf

Hameed

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-15 : 09:32:59

You mean you want to use a date and there is no row for that date you want the row for the second latest date before the input date?

so if there is data for
20100101
20100102
20100103
20100120
20100121
and yoou pass in the date 20100119 then you want the row for 20100102?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hameedf
Starting Member

27 Posts

Posted - 2012-06-15 : 10:24:19

Exactly means same means same if i run the there is no data on 20100121 so query exclude the row of data 20100120
and get the record of 20100103 but if the record is exist at 20100121 then query get the data of 20100120
?

Hameed

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-15 : 10:55:00

;with cte as (select *, seq = row_number() over (order by dte desc) from tbl where dte < @dte)
select * from cte
where seq = 2

I've done it using a cte and row number because I suspect you'll be wanting more from this.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hameedf
Starting Member

27 Posts

Posted - 2012-06-15 : 11:22:57

Can you please elobrate in my above query please ?

Hameed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-15 : 16:07:38

[code]
SELECT ITEM_NAME,CONVERT(VARCHAR(24),(START_DATETIME),3)
, DATEADD(HH,DATEDIFF(HH,0,START_DATETIME),0)
,WELL_HEAD_PRES
FROM TEST t
WHERE ITEM_NAME ='HF-01'
AND VALID_TEST IN ('True','TRUE')
AND START_DATETIME <= '20111101'
AND 1 = (SELECT COUNT(*)
FROM TEST WHERE ITEM_NAME ='HF-01'
AND VALID_TEST IN ('True','TRUE')
AND START_DATETIME > t.START_DATETIME
)
[/code]

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

hameedf
Starting Member

27 Posts

Posted - 2012-06-16 : 06:40:43

visakh16,

Exactly the query gorgeous wow ! You have done a lot for me so nice of you many many thanks . This was my first post love it this forum guru's

Thanks
hameedf

Hameed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-16 : 08:54:28

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources