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.
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_Num436494 20121213102757170 20121214100652930436494 20121214100652930 20121214100939633436494 20121214100939633 NULL436495 20121213102757170 20121214100939633436496 20121213102757170 20121214100652930436496 20121214100652930 NULL436497 20121213102757170 20121214101344837436497 20121214101344837 NULL436498 20121213102757170 NULLI 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? likeSELECT TOP 1 *FROM TableWHERE @date >= HIS_Datum_Ingang_NumAND @date <= HIS_Datum_Einde_NumORDER BY FGRB_ID DESC ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Marteijn
Starting Member
28 Posts |
Posted - 2012-12-14 : 05:41:32
|
When I use the 'date' 20121214100655000, I should getthe following FGRB_ID s:436494 (only the 2nd row, not 1st or third)436495436496 (2nd row, not the 1st)436497 (2nd row, not the 1st)436498When I use the 'date' 20121214120000000, I should getthe 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 TableWHERE @date >= HIS_Datum_Ingang_NumAND @date <= HIS_Datum_Einde_Num ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|