SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sql Query to get data of previous of data exist
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hameedf
Starting Member

Pakistan
27 Posts

Posted - 06/15/2012 :  07:30:54  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 06/15/2012 :  07:40:49  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.

Edited by - nigelrivett on 06/15/2012 07:41:02
Go to Top of Page

hameedf
Starting Member

Pakistan
27 Posts

Posted - 06/15/2012 :  08:47:43  Show Profile  Reply with Quote
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
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 06/15/2012 :  09:32:59  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

hameedf
Starting Member

Pakistan
27 Posts

Posted - 06/15/2012 :  10:24:19  Show Profile  Reply with Quote
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
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 06/15/2012 :  10:55:00  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
;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.
Go to Top of Page

hameedf
Starting Member

Pakistan
27 Posts

Posted - 06/15/2012 :  11:22:57  Show Profile  Reply with Quote
Can you please elobrate in my above query please ?

Hameed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 06/15/2012 :  16:07:38  Show Profile  Reply with Quote

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 
         )


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

Go to Top of Page

hameedf
Starting Member

Pakistan
27 Posts

Posted - 06/16/2012 :  06:40:43  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 06/16/2012 :  08:54:28  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000