| Author |
 Topic  |
|
|
Marteijn
Starting Member
Netherlands
16 Posts |
 Posted - 12/14/2012 : 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
India
47173 Posts |
Posted - 12/14/2012 : 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
Netherlands
16 Posts |
Posted - 12/14/2012 : 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
India
47173 Posts |
Posted - 12/14/2012 : 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
Netherlands
16 Posts |
Posted - 12/19/2012 : 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
India
47173 Posts |
Posted - 12/19/2012 : 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/
|
|
|
| |
Topic |
|
Find the right records with a parameter
