Find the right records with a parameter

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

SQL Server 2008 Forums

Transact-SQL (2008)

Find the right records with a parameter

Author

Topic

Marteijn
Starting Member

28 Posts

Posted - 2012-12-14 : 04:56:32

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

52326 Posts

Posted - 2012-12-14 : 05:01:48

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/

Marteijn
Starting Member

28 Posts

Posted - 2012-12-14 : 05:41:32

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!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-14 : 05:44:41

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/

Marteijn
Starting Member

28 Posts

Posted - 2012-12-19 : 05:04:48

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
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-19 : 08:08:36

This is not what you asked for. Or probably your explanation didnt gave us full idea

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources