| Author |
 Topic  |
|
|
hameedf
Starting Member
Pakistan
27 Posts |
 Posted - 06/15/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3383 Posts |
Posted - 06/15/2012 : 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. |
Edited by - nigelrivett on 06/15/2012 07:41:02 |
 |
|
|
hameedf
Starting Member
Pakistan
27 Posts |
Posted - 06/15/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3383 Posts |
Posted - 06/15/2012 : 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
Pakistan
27 Posts |
Posted - 06/15/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3383 Posts |
Posted - 06/15/2012 : 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
Pakistan
27 Posts |
Posted - 06/15/2012 : 11:22:57
|
Can you please elobrate in my above query please ?
Hameed |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 06/15/2012 : 16:07:38
|
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/
|
|
|
|
hameedf
Starting Member
Pakistan
27 Posts |
Posted - 06/16/2012 : 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
India
52326 Posts |
Posted - 06/16/2012 : 08:54:28
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Sql Query to get data of previous of data exist