Loop Through Flat Files Based On A Date Range

Hello,

I currently have a For Each File container that loops through all files from a specific directory. The files have a naming convention that looks like this;

CDNSC.CDNSC.SC00015.01012007

The last segment of the file name is the date of the data in the file (mmddyyyy). The create date for these files is always a day later than indicated in the file name.

What I would like to do is to have more control over the 'range' of files that are looped through by using the date portion of the file name to define what group of files should be looped through. Ideally, I would like to have a 'StartDate' variable and an 'EndDate' variable that I could define at run time for the package, and the package would loop through all of the files where the date portion of the file name fell between 'StartDate' and 'EndDate'.

Any ideas on this?

Thank you for your help!

cdun2

One way is to move the files to an archive directory when you're done with them. That way you only pick up the files you need to process. Just throwing that idea out there.|||Thanks for your response. One thing that I have to be prepared for is that I may have to 'reload' data. The date range for any reloading could be anything.|||Well, you could (and perhaps should?) stage the data from the flat files in a staging table. Then, once in the staging table you can keep n number of loads around, all marked by either the load date, or a unique load number. After n number of loads, you can delete the old data. Each row in the staging table would also contain the source file name.

Or, you could simply load a table with the load date and a filename. That way, you've got a table that tells you which files to grab for a given load.
Load File
1 xyz.txt
1 fhs.txt
2 jfb.txt

The above still assumes that you "archive" the files into a subdirectory or somewhere.|||The data from the flat files will wind up in one ore more sql server tables. One idea I was considering was to stored the file names in a table, and maybe parse the date portion of the file name into a column. Then somehow use that as a lookup table to determine which files would be processed.|||

cdun2 wrote:

The data from the flat files will wind up in one ore more sql server tables. One idea I was considering was to stored the file names in a table, and maybe parse the date portion of the file name into a column. Then somehow use that as a lookup table to determine which files would be processed.

Right, but the point is that your first data flow simply loads the data from the files AS IS into a table. Then in the data flow that you use today, you point them to the staging table instead of the flat file(s).|||Oh, I see. The data that I need to process is a subset, and will be aggregated for reporting. The concern that I have is that somewhere down the road, the reporting requirements may change, and the users may need another column from the data. I'm thinking that if that happens, I'll need to add the additional column to the subset table, and reload everything.|||

cdun2 wrote:

Oh, I see. The data that I need to process is a subset, and will be aggregated for reporting. The concern that I have is that somewhere down the road, the reporting requirements may change, and the users may need another column from the data. I'm thinking that if that happens, I'll need to add the additional column to the subset table, and reload everything.

You're going to have to add the column in the flat file source anyway, would be one argument.

Regardless, this is just one way to tackle this problem. There are other ways I'm sure.|||

Actually, the destination table will have just a subset of the columns that are found in the flat files. If a need is found for an addional column of data, it will already be in the flat files. I'll just need to add it to the destination table.

What kind of component could I use in a DataFlow Task to read filenames?

Thanks again.

|||

cdun2 wrote:

Actually, the destination table will have just a subset of the columns that are found in the flat files. If a need is found for an addional column of data, it will already be in the flat files. I'll just need to add it to the destination table.

What kind of component could I use in a DataFlow Task to read filenames?

Thanks again.

If this is the case, then the staging table example still works. In your CONTROL flow, you'd use a foreach loop to spin through the files. Each filename can be put in a variable, which can be used in a data flow inside the foreach loop. Then, using a derived column in the data flow, you'd have access to the variable that contains the current filename.|||

cdun2 wrote:

Hello,

I currently have a For Each File container that loops through all files from a specific directory. The files have a naming convention that looks like this;

CDNSC.CDNSC.SC00015.01012007

The last segment of the file name is the date of the data in the file (mmddyyyy). The create date for these files is always a day later than indicated in the file name.

What I would like to do is to have more control over the 'range' of files that are looped through by using the date portion of the file name to define what group of files should be looped through. Ideally, I would like to have a 'StartDate' variable and an 'EndDate' variable that I could define at run time for the package, and the package would loop through all of the files where the date portion of the file name fell between 'StartDate' and 'EndDate'.

Any ideas on this?

Thank you for your help!

cdun2

Cdun2,

This is an alternative approach to the issue of processing only the files that are within a start/end date range. It uses Expression and precedence constraints:

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

BTW,

Just to save extra work down the road; i would recomment to import all the columns of the files from the begining.

|||Thanks again for your input. I'll dig a little deeper into this next week. As far as bringing in all of the columns of data, the attempt is to aggregate so that we can meet a reporting need, and reduce the number of rows of data that we need to store. Its a resource/performance issue for us. I can't bring in any more than I need.|||

cdun2 wrote:

I can't bring in any more than I need.

But you can. To Rafael's point, you can at least have ALL of the flat file columns mapped in the connection manager. You don't have to do anything with them in the data flow, but at least they are there if you need to pick them up later; you won't have to redefine the connection manager.|||

Phil Brammer wrote:

cdun2 wrote:

I can't bring in any more than I need.

But you can. To Rafael's point, you can at least have ALL of the flat file columns mapped in the connection manager. You don't have to do anything with them in the data flow, but at least they are there if you need to pick them up later; you won't have to redefine the connection manager.

Yes, that makes sense. Thanks again.

|||

I took a look at the example here; http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

Thank you for providing this example. I am having a difficult time making the bridge between the precendent constraint, and the Data Flow Task where the content of the qualifying flat files are inserted into the SQL Server table.

Lookup transformation using effective dates

Hi,

I need to perform a lookup based on a business key and a date in the source that must be between an effective from and effective to date in the reference table. I've been able to achieve this by updating the SQL caching command on the advanced tab but the performance is very slow. I have 6 lookups of this type in the data flow with a source SQL statement that returns approx 1 million rows and this package takes over 90 minutes to run.

The caching SQL command now looks like this

select * from
(select * from [ReferenceTable]) as refTable
where [refTable].[Key] = ? and ? BETWEEN [refTable].[StartDate] AND [refTable].[EndDate]

and I've set up the parameters so that the business key is the first parameter and the source date is the second.

I have another lookup in the flow that does a straight equality comparison using 2 columns and the Progress tab shows that this lookup is cached (even though I haven't enabled it on the Advanced tab of the transformation editor) but none of the other lookups (using the date) appear to be cached, even though I have enabled them to be.

Can anyone suggest how I can improve the performance?

Thanks.

Hi,

When u use 'caching SQL command', caching can be either partial or none. In the 'none' mode each time it will execute the sql command for input. In the 'partial' mode, it will only cache the previously executed sql command results, so it won't cache any data at the outset.

Other alternative approach is, join ur input with the [reference table] using the key (don't use date). U will give multiple records. Use a conditional split to compare the date with start and end date. The output will be what u want.

|||

Thanks for the tip. Initially, the caching was partial so I would have expected the lookup speed to increase as the process went on ,as more and more of the target reference records were loaded, but this didn't seem to be the case.

I've now changed the package so that it joins directly onto the reference table and the speed has increased dramatically.

Thanks.

LookUP Transform...

Dear friends,

I have a ETL that have a Lookup transform to get a rate from a table SpotRates.

The problem is when the match od some date in SpotRates Table doens't exist...

And for that records I need to lookup for next date...

For example...

SpotRate Table

Date Currency Rate 05-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2262 06-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2312 07-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2179 10-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2099 11-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2105 12-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2125 13-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2094 18-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2252 19-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2346 20-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2346 21-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2315 24-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2365 25-04-2006 0:00 DOLAR ESTADOS UNIDOS 1,2425

When I first try to lookup the date 17-04-2006, doesnt give me any records... and I need to create a new lookup for the next date from 17-04-2006. And in this example the next date is 18-04-2006.. How can I do it?

I made a sql query date gives me the next date with 2 parameters ... but I'm having some errors...

SELECT TOP 1 Data
FROM Spot_Rates
WHERE (Currencies_Name = ?) AND (Data > CONVERT(DATETIME, ?, 102))
ORDER BY Data DESC

In this exampple, the parameters returned from lookup1 is:

Currencies_name= 'DOLAR ESTADOS UNIDOS'

DATE='17-04-2006'

I need to create a second lookup transform to return the next date/currency for each row that didnt match in the first lookup...

Regards,

Pedro

What are your errors?

I also think you'd want "Data >= CONVERT(DATETIME......)"|||

I have tried this way...

In lookUp transformation in 1oTab "Reference Table" I inserted SQL query:

SELECT Data, RevalRate, Currencies_Name
FROM Spot_Rates

In 2oTab "Columns" I'm inserted the relations between input/output and check the field that I want for output.

In 3oTab "Advanced" I'm inserted :

select TOP 1 * from
(SELECT Data, RevalRate, Currencies_Name
FROM Spot_Rates) as refTable
WHERE ([refTable].[Currencies_Name] = ?) AND ( [refTable].[Data] > CONVERT(DATETIME, ?, 102))
ORDER BY Data DESC

But at this moment the package are spending a lot of time to finalize... I still waiting... :-(

Regards,

pedro

|||

And it's not being return the correct revalrate for each row with Currencies_Name/Date...

It's returning the last date for this currency and not for input parameter date for each row... :-(

Regards

|||Do you have indexes at all on the Spot_Rates table? How large is the Spot_Rates table?|||

USE [dbRentabilidade]

GO

/****** Object: Table [dbo].[Spot_Rates] Script Date: 04/16/2007 15:36:35 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Spot_Rates](

[Currencies_ShortName] [nvarchar](3) NULL,

[Currencies_Name] [varchar](32) NULL,

[Data] [datetime] NULL,

[RevalRate] [float] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

About 55.000 rows...

|||

Pedro,

I would like to make some points:

The query needs to have an OR clause; so it retrives the exact match or the nearest greater one. If you are going to use TOP 1; the query must guarentee that resultset is ordered ASC If you use a Lookup transform the way you are using it (query w/parameters) it will work using partial cache; which menas it will run the query for each row passing through. That is way it is so slow. How many rows are passing through? If the Spot_dates table and the rows passing through the pipeline are in the same database; you are better of creating a database function to retrive the right RevalRate. That way the DB engine will carry the overhead.|||

In spite of looup transform, i'm trying to use OLE DB Command... executing the following SQL Stored Procedure:

USE [dbRentabilidade]

GO

/****** Object: StoredProcedure [dbo].[R_SP_GET_NextDayRate] Script Date: 04/16/2007 18:17:33 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[R_SP_GET_NextDayRate]

@.MyCurrency varchar(32),

@.MyDate datetime

AS

SELECT TOP 1 Data AS TESTE

FROM Spot_Rates

WHERE (Currencies_Name = @.MyCurrency) AND (Data > CONVERT(DATETIME, @.MyDate, 102))

ORDER BY Data ASC

RETURN

But the problem is that I dont know how to read the values returned from SP in order to continue the dataflow with this values... IT'S Possible?!

|||It looks like you cannot get columns back to the dataflow pipe line via oledb command. But here is the thing, even if you find the way; the performance will be about the same than the one you get using the Lookup component. That is why I was asking if you could include the join or function right on your source component.|||

And what you think about insert the non matched values into temp table, and use a lookup to find the rate in this temp table to continue the dataflow?

what you think?

|||

I have created a OLEDB Command to insert values that didnt match the lookup in a table, and have a loopup to match the values in thsi table...

The problem now, is that in the dataflow, the values inserted by OLE DB Command are not refreshed in the dataflow in the moment that I do a new match in the lookup transformation to this table...

...

7. LookUp

7.1. MutiCast

7.2. OLE DB Command

7.2.1 LookUp

And the values inserted in OLE DB Command are in the table database, but not in the dataflow in the moment that data is passing throut lookup transformation....

Could someone help me?

Thanks

|||I am getting lost with your approach; but in general you could break the process in 2 data flows; the first one to insert the no matches into the temp table; the second to do the lookup. does this make sense?|||

PedroCGD wrote:

I have created a OLEDB Command to insert values that didnt match the lookup in a table, and have a loopup to match the values in thsi table...

The problem now, is that in the dataflow, the values inserted by OLE DB Command are not refreshed in the dataflow in the moment that I do a new match in the lookup transformation to this table...

...

7. LookUp

7.1. MutiCast

7.2. OLE DB Command

7.2.1 LookUp

And the values inserted in OLE DB Command are in the table database, but not in the dataflow in the moment that data is passing throut lookup transformation....

Could someone help me?

Thanks

Enable memory restriction in your second lookup (on the advanced tab). This will slow down the data flow, but it's the only way you will see the new data in the lookup.

I've been watching your threads on this over the last day. Have you considered just populating your rate table with data for each day? Even if you use a seperate data flow to create a "temporary" working table with all the dates filled in, it would still make your process much simpler to implement and maintain.

|||

Make sense and I already tried it before... but when I do the lookup the data not exist yet... :-(

Regards,

Pedro

|||

jwelch,

But we are talking about millions of rows...

I'm trying to include one more lookup... but I dont know if it will work,... i give you feedback soon!

Thanks!

Lookup including looking up on null values possible?

In order to insert datekey values in I lookup datekey in the datedimension table. I join on the 'Date' column that contains dates. The datedimension contains one record for 'unknown date' for which the value of the 'Date' column is null.

The behavior that I desire from my lookup transformation is that for input records with a date the corresponding datekey from the datedimension is looked up and for records with date = null, the datekey for 'unknown date' is looked up.

The first part works well but the looking up on null fails, apparently because you can not say null == null. Does anyone know whether there is a setting in the lookup transformation to include null lookups?

Thnx,
HenkThe lookup transform can not do this. You would need to put a derived column in the flow and if the value is NULL then set it to the appropriate 'unknown date' value.

Thanks,|||Thanks Matt.|||In fact it can and it is quite easy! I found out in the documentation:

"A Lookup transformation that has been configured to use partial or no caching will fail if a lookup operation matches columns that contain null values, unless you manually update the SQL statement to include an OR ISNULL(ColumnName) condition. If full precaching is used, the lookup operation succeeds."

|||So by selecting the full precaching option for the lookup, you eliminate the need to modify the SQL with the ISNULL function?|||While this can work as described I would recommend against it and is, therefore, why I didn't mention it. You need to be careful if you do lookups in this way because unless you guarrantee that there is only one such value you will get the first one lookup happens to find with no warning.

Full precaching will not work because the cache is fully charged and doesn't issue the SQL statement again. The reason why partial or no cache works is because the SQL statement is issued if a match isn't found and will return success due to the ISNULL statement as long as there is a NULL in the table.

There are too many ifs and caveats to make this a good solution, IMHO.

Thanks,

looking to collect distinct date part out of datetime field

from this, circdate being a datetime field:
SQLQuery = "select distinct circdate from circdata order by circdate"

I need the distinct date portion excluding the time part.

this has come about when I discovered
I am inserting and updating some datetime values with the same value,
but for some reason, the values are always off by a few seconds. I set
a variable called SetNow assigned to NOW and then set the datetime
fields to this SetNow variable. Then when I collect the distinct date
time I am assuming they will have the same values recorded in
circdate, but no, they are off by several seconds. Makes no sense to me
at all. I tried renaming the variable several times but it makes no
difference at all.
any help appreciated, thanks.SQLQuery = "SELECT distinct CONVERT(char,circdate,1) from circdata"

I think I solved it
any one see a problem with this?
thanks
how does the '1' parameter affect the output as I know there are
several choices|||sdowney717@.msn.com (sdowney717@.msn.com) writes:
> SQLQuery = "SELECT distinct CONVERT(char,circdate,1) from circdata"
> I think I solved it
> any one see a problem with this?
> how does the '1' parameter affect the output as I know there are
> several choices

1 is a format parameter that controls how the datetime value is formatted.
You can read about these in the topic CAST and CONVERT in Books Online.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||http://www.aspfaq.com/show.asp?id=2464
shows all the outputs
Was wondering though if it wont use an index on a convert.|||sdowney717@.msn.com (sdowney717@.msn.com) writes:
> http://www.aspfaq.com/show.asp?id=2464
> shows all the outputs
> Was wondering though if it wont use an index on a convert.

For the query you gave,

SELECT distinct CONVERT(char,circdate,1) from circdata

this is not an issue. If there is an index on cricdate, SQL Server will
use that index in the most effective, that is to scan the index, because
that is what the query calls for, with or without the convert().

On the other hand

SELECT col1, col2, col3 FROM circdata
WHERE CONVERT(char, circdate, 1) = @.val

will probably not use the index, and in any case the query will not seek
the index, that is lookup the value through the B-tree. This is because
the index is sorted on the datetime value, not on a character value.

To list all rows for a given date you can do:

SELECT col1, col2, col3 FROM circdata
WHERE circdate >= @.val AND circdate < dateadd(DAY, @.val, 1)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Also refer this to know how to query on dates
http://www.karaszi.com/SQLServer/info_datetime.asp

Madhivanan

Looking for the equivalence (date issue)

Hi all of you,

1)Ok, this works properly:

ISNULL([Column 13]) ? NULL(DT_WSTR,1) : RIGHT([Column 13],2) + "/" + SUBSTRING([Column 13],5,2) + "/" + SUBSTRING([Column 13],1,4)

for (old dts2000 column transformation)

Fecha=right(DTSSource("Col014"),2) & "-" & Mid(DTSSource("Col014"),5,2) & "-" & left(DTSSource("Col014"),4)
If IsNull(Fecha) then
DTSDestination("FechaAp") = Fecha
Else
DTSDestination("FechaAp")=null
End If

2). But how must I do such thing for IsDate when you have not available that function?

?

Fecha=right(DTSSource("Col014"),2) & "-" & Mid(DTSSource("Col014"),5,2) & "-" & left(DTSSource("Col014"),4)
If IsDate(Fecha) then
DTSDestination("FechaAp") = Fecha
Else
DTSDestination("FechaAp")=null
End If

Thanks a lot,

I think you'll need to use a script component to do this check. You can use the DateTime.TryParse method.

|||Thanks for that. It'll be tested.

Looking for how to return the previous payment

I use the following sql in a view to return the last payment date and amount made by clients. What I need is a way to return the payment date and amount for the payment prior to the last one.

Any help is appreciated very much,

SELECT dbo.tblPaymentReceipts.Client_ID, dbo.tblPaymentReceipts.PaymentDateAS LastPayDate,SUM(dbo.tblPaymentReceipts.AmountPaid)AS LastPayAmt
FROM dbo.tblPaymentReceiptsINNERJOIN
(SELECT Client_ID,MAX(PaymentDate)AS LastPayDate
FROM dbo.tblPaymentReceiptsAS tblPaymentReceipts_1
GROUP BY Client_ID)AS AON dbo.tblPaymentReceipts.Client_ID = A.Client_IDAND dbo.tblPaymentReceipts.PaymentDate = A.LastPayDate
GROUP BY dbo.tblPaymentReceipts.Client_ID, dbo.tblPaymentReceipts.PaymentDate

You could try something like this...

SELECT
X.ClientID,
P.PaymentDate AS ThisPaymentDate,
P.Amount AS ThisPaymentAmount,
L.PaymentDate AS LastPaymentDate,
L.Amount AS LastPaymentAmount
FROM
(
SELECT
ClientID,
MAX(PaymentDate) AS PaymentDate
FROM tblPaymentReceipts
GROUP BY ClientID
) AS X
LEFT JOIN
(
SELECT
R.ClientID,
Max(R.PaymentDate) AS PaymentDate
FROM
(SELECT ClientID, MAX(PaymentDate) AS PaymentDate FROM tblPaymentReceipts GROUP BY ClientID) AS Z
LEFT JOIN tblPaymentReceipts R
ON Z.ClientID = R.ClientID
AND R.PaymentDate < Z.PaymentDate
GROUP BY R.ClientID
) AS Y
ON X.ClientID = Y.ClientID

LEFT JOIN tblPaymentReceipts P
ON X.ClientID = P.ClientID
AND X.PaymentDate = P.PaymentDate

LEFT JOIN tblPaymentReceipts L
ON Y.ClientID = L.ClientID
AND Y.PaymentDate = L.PaymentDate

This most likely isn't the most efficient query but its a starting point for you...

John

|||

--SQL Server 2005

SELECT Client_ID, PaymentDate, AmountPaidFROM(SELECT Client_ID, PaymentDate, AmountPaid, ROW_Number()OVER(PARTITIONBY Client_IDORDERBY PaymentDateDESC)as RowNum

FROM tblPaymentReceipts)AS t

WHERE RowNum=2--RowNum=1 for latest payment

-- If you are not using SQL Server 2005:

SELECT Client_ID, PaymentDate, AmountPaidFROM(SELECT Client_ID, PaymentDate, AmountPaid,

(SELECTCOUNT(*)FROM tblPaymentReceiptsAS a

WHERE(PaymentDate>= b.PaymentDate)AND(Client_ID= b.Client_ID))AS RowNum

FROM tblPaymentReceiptsAS b

)AS t

WHERE RowNum= 2--RowNum=1 for latest payment

|||

These options are much faster to run, however, they are not supoorted as views and I need this to work in a view. the OVER is not supported in SQL Server Express and/or Views.

Dave's option is not as fast but will work in a view.

Thank you both very much,

|||

--SQL Server 2005

CREATEVIEW [dbo].[View_2]

AS

SELECT Client_ID, PaymentDate, AmountPaid

FROM(SELECT Client_ID, PaymentDate, AmountPaid, ROW_Number()OVER(PARTITIONBY Client_ID

ORDERBY PaymentDateDESC)AS RowNum

FROM tblPaymentReceipts)AS t

WHERE RowNum= 2

It shows the error message of OVER not supported at the IDE window, but the view will run correctly.

|||

perhaps I misunderstood the problem, but I thought he was trying to return BOTH the current payment and the previous payment info.?

|||

You are right.

As long as he can work along these logics, it should be straight to get customized results.

Looking for help with an SQL query.

well, i want to select rows by date from a file. but I want
in particular one sum of values from the rows that fall WITHIN
a supplied date range, and a second sum of values from the
rows that have dates FOR ALL TIME UP TO the second date in the date range.

the former, by itself, might be:

SELECT id, value RangedValue
FROM myFile
WHERE date >= [lower date range value]
AND date <= [higher date range value]

and the latter, by itself, might be:

SELECT id, value AllTimeValue
FROM myFile
WHERE date <= [higher date range value]

but I need to grab the two separate sums (RangedValue and AllTimeValue)
using one SQL statement.

I'm thinking that the UNION might work, but my preliminary results are
taking a huge amount of time, and apparently smegging up the (rather
stupid, external) report generator to boot.
If you like the UNION idea, please give me an example.

I should mention that the report generator at very least can do the
(summing) part. I could do the summing at either the SQL level or the
report level. I should also mention that although I only talk about the
one file here (myFile), in fact I need to join to and pull values from
its "parent" file, although I don't think that that should change my
fundamental problem.

Any ideas?

Cheers in advance!

-GlennYou could try something like this:
Select Id
, Sum(Value) Alltimevalue
, Sum(Case
When Date >= [Lower Date Range Value] Then Value
Else 0 End) As Rangedvalue
From Myfile
Where Date <= [Higher Date Range Value];
;)