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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Find the right records with a parameter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Marteijn
Starting Member

Netherlands
25 Posts

Posted - 12/14/2012 :  04:56:32  Show Profile  Reply with Quote
Hi,

Who want to help me with the following?
I have a primary key FGRB_ID and 2 columns with dates (converted to numerics, first column startdate, second column enddate).

From the list under here, I like to see what is 'recent' on
a certain date (parameter).

E.g. what is recent on 20121214100655000?

How can i write the SQL to do this?

FGRB_ID HIS_Datum_Ingang_Num HIS_Datum_Einde_Num
436494 20121213102757170 20121214100652930
436494 20121214100652930 20121214100939633
436494 20121214100939633 NULL
436495 20121213102757170 20121214100939633
436496 20121213102757170 20121214100652930
436496 20121214100652930 NULL
436497 20121213102757170 20121214101344837
436497 20121214101344837 NULL
436498 20121213102757170 NULL

I thank you already for your answers!

Greets, Marteijn

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/14/2012 :  05:01:48  Show Profile  Reply with Quote
can you explain what you mean by recent? DO you mean latest record? like

SELECT TOP 1 *
FROM Table
WHERE @date >= HIS_Datum_Ingang_Num
AND @date <= HIS_Datum_Einde_Num
ORDER BY FGRB_ID DESC


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

Go to Top of Page

Marteijn
Starting Member

Netherlands
25 Posts

Posted - 12/14/2012 :  05:41:32  Show Profile  Reply with Quote
When I use the 'date' 20121214100655000, I should get
the following FGRB_ID s:

436494 (only the 2nd row, not 1st or third)
436495
436496 (2nd row, not the 1st)
436497 (2nd row, not the 1st)
436498

When I use the 'date' 20121214120000000, I should get
the following FGRB_ID s:

436494 (3rd row, not the first 2 rows)
436496 (2nd row, not the 1st one)
436497 (2nd row, not the 1st one)
436498

436495 will not appear in this list...

Thanks again!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/14/2012 :  05:44:41  Show Profile  Reply with Quote
sounds like this then!

SELECT *
FROM Table
WHERE @date >= HIS_Datum_Ingang_Num
AND @date <= HIS_Datum_Einde_Num


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

Go to Top of Page

Marteijn
Starting Member

Netherlands
25 Posts

Posted - 12/19/2012 :  05:04:48  Show Profile  Reply with Quote
Thanks!

I looked further for other solutions and found this one...
Added to the where clause...

and tlbn.HIS_Datum_ingang_num= (select MAX(tlbn1.HIS_Datum_ingang_num)
from MIB_HISTORIE.dbo.HIS_DEC_FLAT_GRBS tlbn1
where tlbn1.FGRB_ID = tlbn.FGRB_ID
)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/19/2012 :  08:08:36  Show Profile  Reply with Quote
This is not what you asked for. Or probably your explanation didnt gave us full idea

------------------------------------------------------------------------------------------------------
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.08 seconds. Powered By: Snitz Forums 2000