hi,

        I am getting a data in a dbf file and they have a StartDate and end Date for where the Statments are valid for.. How can i incorporate them into the database..

right now We are doing with PeriodId.. like while the user imports the data they select which period they want to import the data into.. But now if i have to get those details from the Database how do i store it in the Database..and i need to store them in multiple tables..

Thanks

Karen

I don't understand your request... could you include a row or two of your data? Are you saying that each record(row) has a field for StartDate and a field for EndDate, OR is it one date field and you want to filter the records by a date range?

SMc

Hi Karenros,

I'm not very sure what you mean, but generally speaking, we handle sql SatrDate and EndDate like this (at least I handle it in the following way): Store Start Date and End Date as Varchar and if you want to filter the desired date which meets your need , you can use  select XXX,XX, from tbl_name where Date_Column>StartDate and Date_Column<EndDate. Note here that both StartDate and EndDate are varchar variables.

Hope my suggestion helps

Thanks for the answers..

Here is my scenario.

I am getting 6 dbf files from the customer and i am importing them using sql bulk copy to a staging database. And in the one of the Dbf files called PlanDbf. i have 2 fields called Start date and enddate which i want to capture and insert into to different tables in the production database. ( but not in the table that it is from). and i am stuck as to what to do.. The import process works where the user selects a PeriodId and then we pass that periodId to all the needed sproces and import the data in to the database..

But now i need to eliminate the PeriodId take the start and end date from the plandbf table.

This is my table structure

PlanDbf.

Plan_NUM  Name1  Name2  Beg_Date     End Date etc

  01212       Abc       Plannme  20080101    2008313

  01211       bca       jajal          20080101    2008313

 so on and so forth..

Then in the PartDbf

Plan_NUM  PartID   OP_BAL  etc

01212         XXXX       12.56

01212        XXXX        45.45

01211       XXXX        25.42

so on and so forth depending on how many Plans are there in the Plandbf file.. in plan they can be N no.. of unique PartId  for each plan

SO when i am transfereing the data from the Staging databse to the production

I want to add the BEG_Date and the End Date in the PartDbf table in the production database..

SO suppose if my query is like this

Select

     pd.PlanNum,

     pd.PartId

     pl.Beg_Date,

    pl.End_Date

From

  Partdbf pd

   Inner join other tables to get the required actually. that is actally planid from a particular table

Inner Join Plandbf pl on pd.PlanNum = pl.PlanNum.

So i ran a test data which has around 203 records in the Plandbf and 12603 in partdbf and  if  i dont inner join pl in the above query the it takes 0.01 secs to exceute the whole query.. if i add  the pl inner join it take around 5.17 secs..

so i think theere should be a better way that i get that beg_date and end date in the query,,

any help will be appreicated..

Thanks

Karen

Correct me If I'm wrong. You are looking for parts between some specific Start and End dates in PlanDbf, Correct? So the query is driven by the plan_num in PlanDbf.

Select Plan.Plan_num,Part.PartID,Beg_Date,End_date

From PlanDbf Plan Inner Join PartDbf Part On Plan.Plan_Num=Part.Plan_num

Where Begin_Date >= somevalue And End_Date <= somevalue

Is there an index on PlanNum of PartDbf table?  If not,Adding one would give a performance boost.