SQL Server - get data of previous exist date

Hello Experts,

I have some help i have a senario suppose i have a column name "WELL_HEAD_PRES"  its data comes on event i able to write a query which get the data of  current date or previous where the data is present. But problem i have this data is on event right i need change in below query that get data of  <= '2011.11.01'    pevious then row exist suppose data is exist at  2010.2.01'   on event i need go back to this date and fetch that row data  ?   

SELECT

ITEM_NAME,CONVERT(VARCHAR(24),(START_DATETIME),3)

 ,CONVERT(Varchar, DATEPART(HH,START_DATETIME))+':00' ,

 WELL_HEAD_PRES

 FROM VT_TEST_en_ WHERE ITEM_NAME ='-01'

 AND VALID_TEST IN ('True','TRUE')

 AND CONVERT(Varchar(10),START_DATETIME,102)<= '2011.11.01'

 AND DATEPART(HH,START_DATETIME)>=0

 


Thanks and Regards

hameed

Hi Frndz,

 

Functionality:  Get Previous one Data if Exist in SQL server

 

Set order by Date in DESC

 

order by CONVERT(Varchar(10),START_DATETIME,102) desc

 

After then get top 1 (Top 1)

 

 

 

Query :

 

 

SELECT

TOP 1

 

 ITEM_NAME,CONVERT(VARCHAR(24),(START_DATETIME),3)

 ,CONVERT(Varchar, DATEPART(HH,START_DATETIME))+':00' ,

 WELL_HEAD_PRES

 FROM VT_TEST_en_ WHERE ITEM_NAME ='-01'

 AND VALID_TEST IN ('True','TRUE')

 AND CONVERT(Varchar(10),START_DATETIME,102)<= '2011.11.01'

 AND DATEPART(HH,START_DATETIME)>=0

 

 order by CONVERT(Varchar(10),START_DATETIME,102) desc

 

Hope this helpful!

Thanks

 

 

 

Dear RB,

Having the same result ?

(No column name)	(No column name)	WELL_HEAD_PRES
1/11/2011	3:00	44

Thanks
hammeed

did you send me your data??

and which data you want in your send data?

Give me example with data

did you send me your data??

and which data you want in your send data? 

Hi Frndz,

 

Functionality:  Get Second Highest Date Records

 

To achieve this task you need to make your logic as following way

 

 

Logic   :

 

SELECT TOP 1

    WELL_HEAD_PRES

FROM

    VT_TEST_en_

WHERE

   

    CONVERT(Varchar(10),START_DATETIME,102) NOT IN

    (

        SELECT DISTINCT TOP 1

          CONVERT(Varchar(10),START_DATETIME,102)

        FROM

          VT_TEST_en_

         

        order by

          CONVERT(Varchar(10),START_DATETIME,102) desc

    )

    order by CONVERT(Varchar(10),START_DATETIME ,102) desc

 

 

Full Query    :

 

 

 

SELECT TOP 1

    ITEM_NAME,

    CONVERT(VARCHAR(24),(START_DATETIME),3),

    CONVERT(Varchar, DATEPART(HH,START_DATETIME))+':00' ,

    WELL_HEAD_PRES

FROM

    VT_TEST_en_

WHERE

    ITEM_NAME ='-01'

    AND VALID_TEST IN ('True','TRUE')

    AND CONVERT(Varchar(10),START_DATETIME,102)<= '2011.11.01'

    AND DATEPART(HH,START_DATETIME)>=0

    AND CONVERT(Varchar(10),START_DATETIME,102) NOT IN

    (

        SELECT DISTINCT TOP 1

          CONVERT(Varchar(10),START_DATETIME,102)

        FROM

          VT_TEST_en_

        WHERE ITEM_NAME ='-01'

          AND VALID_TEST IN ('True','TRUE')

          AND CONVERT(Varchar(10),START_DATETIME,102)<= '2011.11.01'

          AND DATEPART(HH,START_DATETIME)>=0

        order by

          CONVERT(Varchar(10),START_DATETIME,102) desc

    )

    order by CONVERT(Varchar(10),START_DATETIME,102) desc

 

Hope this helpful!

Thanks

 

 

 

RB thanks for you hard work   this is fine but when i change the date suppose )<= '2011.11.01' to <= '2012.11.11'  i have two rows exists in sample data fine when i change the date  to <= '2012.11.11'  it shows the data of '2011.11.01'  but i need the previous row which is at 1/1/2005 exist?


Thanks and Regards,
hameed

More clearence if i cannot understand well

Example 1:
 If there is no row for that date  the row for the second latest date before the input date
2010.01.01
2010.01.02
2010.01.03
2010.01.20
2010.01.21  pass in the date 2010.01.19 then you want the row for 2010.01.02

Example:2

 if i run the there is no data on 2010.01.21 so query exclude the row of data 2010.01.20
and get the record of 2010.01.03 but if the record is exist at 2010.01.21 then query get the data of 2010.01.20

Its working  fine.

 

Can you send me your query  and db table  with data?

 

Logic   :

 

 

 

SELECT

    TOP 1 CONVERT(Varchar(10),DateTimeField,102)

FROM

    TABLENAME

WHERE

    CONVERT(Varchar(10),DateTimeField,102)<= '2012.05.17'

    AND CONVERT(Varchar(10),DateTimeField,102)

    NOT IN

    (

        SELECT

          DISTINCT TOP 1 CONVERT(Varchar(10),DateTimeField,102)

      FROM

          TABLENAME

        WHERE

          CONVERT(Varchar(10),DateTimeField,102)<= '2012.05.17'

      ORDER BY

          CONVERT(Varchar(10),DateTimeField,102) desc

    )

ORDER BY

    CONVERT(Varchar(10),DateTimeField,102) desc

Dear R B,

I a m sorry thats was my mistake the Query is Working Outstandingly. Thank you very much for you precious time for me.

Wishes you very best.


Bundle of thanks,

Hameed

you most welcome :)

I am really sorry RB ,

I have problem now having times problem suppose in date the sample is attached

2012-06-17 00:00:00.000
2012-06-17 15:00:00.000

Can you please look at that too? if this scenario comes how to handle ?Timing.zip
Now in this case please filter in the query this too if timing is less then it is previous?
When date comes with two time duration query pick the less time as a pervious or if only one row exist then do like previous query

Hi Frndz,

 

Functionality:  Get top 2 records with Date and Time

 

To achieve your task need to concat date and time field

(CONVERT(Varchar(10),DateField,102) +' '+ CONVERT(Varchar(20),TimeField,108))

 

 

After then same proecess as previous one.

 

Order by as Date + Time

Check In records by Date + Time

 

Other logic same as previous one

 

Logic   :

 

 

 

SELECT TOP 1

  CONVERT(Varchar(10),DateField,102)+' '+ CONVERT(Varchar(20),TimeField,108))

FROM

    TableName

WHERE

    CONVERT(Varchar(10),DateField,102)<= '2012.05.18'

    AND (CONVERT(Varchar(10),DateField,102) +' '+ CONVERT(Varchar(20),TimeField,108))

    NOT IN

    (

      SELECT

       TOP 1 (CONVERT(Varchar(10),DateField,102)+' '+CONVERT(Varchar(20),TimeField,108))

    FROM

      TableName

      WHERE

      CONVERT(Varchar(10),DateField,102)<= '2012.05.18'

    ORDER BY

       (CONVERT(Varchar(10),DateField,102)+' '+CONVERT(Varchar(20),TimeField,108)) DESC

    

    )

ORDER BY

    (CONVERT(Varchar(10),DateField,102) +' '+ CONVERT(Varchar(20),TimeField,108)) desc

 

Hope this helpful!

Thanks

 

 

 

Dear RB,
 

I have no words to say you thanks a lot for your kindness to me and make my works very easy .

Lots of wishes Thanks you


Hameed 

you most welcome :)

Dear R B I really feel guilty to end then restart the tread i am extremely apologize for this...    I need this badly (:   The Query is working outstandingly while checking more if i remove then ITEM_NAME OR  Do like ITEM_NAME IN ('HF-01',HF-02') I find the previous value of one of them if i remove filter in where clause still find the last record one of Item names . Can you please slight more that i don't want to filter (WHERE ITEM_NAME='HF-01') in the query i need that what ever item names is before date '2012.06.16' the query pick the previous exist date for all the items?  The query show the data of all the item names ?

Hi Frndz,

 

Functionality:  Remove Item Name filter from Query

 

To achieve this task,

 

Remove Item Name in your query (ITEM_NAME='HF-01' AND)

 

Also Remove this Item Name from In Query ITEM_NAME='HF-01' AND

 

 

 

Query :

 

 

SELECT TOP 1

  CONVERT(Varchar(10),START_DATETIME,102)+' '+ CONVERT(Varchar(20),START_DATETIME,108),WELL_HEAD_PRES

 FROM

  VT_WELL_TEST_en_US

 WHERE  VALID_TEST IN ('True','TRUE')

   AND CONVERT(Varchar(10),START_DATETIME,102)<= '2012.06.16'

   AND (CONVERT(Varchar(10),START_DATETIME,102) +' '+ CONVERT(Varchar(20),START_DATETIME,108))

   NOT IN

   (

    SELECT

    TOP 1 (CONVERT(Varchar(10),START_DATETIME,102)+' '+CONVERT(Varchar(20),START_DATETIME,108))

   FROM

   VT_WELL_TEST_en_US

    WHERE VALID_TEST IN ('True','TRUE')

   AND CONVERT(Varchar(10),START_DATETIME,102)<= '2012.06.16'

   ORDER BY

    (CONVERT(Varchar(10),START_DATETIME,102)+''+CONVERT(Varchar(20),START_DATETIME,108)) DESC

  )

 

ORDER BY

 (CONVERT(Varchar(10),START_DATETIME,102) +' '+ CONVERT(Varchar(20),START_DATETIME,108)) desc

 

 

Hope this helpful!

Thanks

 

 

 

Thanks for your reply  RB,

I think Top1 handle only one row ? Iam attaching the data file still iam getting only one item name data ?

Thank you

hameed    datafile.zip

Hi Frndz,

 

Functionality:  Get Second Highest Date for Each Item Name

 

To achieve this task,

 

Remove Top Query (TOP Return only one ROW)

 

Use Max function with Group by Item Name

 

Added Group by ItemName on Both Query (Main and Sub Query)

 

Change your query as following way

 

 

 

Query :

 

 

SELECT

    MAX(CONVERT(Varchar(10),START_DATETIME,102)+' '+ CONVERT(Varchar(20),START_DATETIME,108)) as SecondMaximumDate

 

 FROM

    VT_WELL_TEST_en_US

 WHERE  VALID_TEST IN ('True','TRUE')

   AND CONVERT(Varchar(10),START_DATETIME,102)<= '2012.06.16'

   AND (CONVERT(Varchar(10),START_DATETIME,102) +' '+ CONVERT(Varchar(20),START_DATETIME,108))

   NOT IN

   (

    SELECT

    MAX(CONVERT(Varchar(10),START_DATETIME,102)+' '+CONVERT(Varchar(20),START_DATETIME,108))

   FROM

   VT_WELL_TEST_en_US

    WHERE VALID_TEST IN ('True','TRUE')

   AND CONVERT(Varchar(10),START_DATETIME,102)<= '2012.06.16'

    GROUP BY  ITEM_NAME

  )

 

    GROUP BY ITEM_NAME

 

 

Hope this helpful!

Thanks

 

 

 

Dear RB,

I have tested a lot and its perfectly really thank you RB such a wonderful work :)


Thank You


Hameed