get max date from qvd

[#1]

im been using peek -1 but its returnig the wrong value as im doing a concatenated load and the lastloaded value is not the max value.

[Code] select CreatedDate from table1 order by created date asc //Peek ('createddate', -1) would work here
Concatenate
select CreatedDate from table2 order by created date asc //Now same peek would fail as the max from table 2 may not be larger than the max of table 1

//Store to QVD
//Peek ('Createddate', -1) [\Code]

Can you peek the max created date from a QVD?

Colin R

[blaise]

depending of the structure/setup of the database you might gain speed by doing the max(CreatedDate) directly from SQL. I assume you doing something like this;

Full Load (maybee on a weekly basis or similiar)

<code>

Complete:

Select * from dbo.Table1;

store Complete into data\complete.qvd;

drop table Complete;

MaxDate:

Select max(CreatedDate) as MaxDate from dbo.Table1;

store MaxDate into data\maxDate.qvd;

</code>

Next day, you do a incremental load... But before that, fetch the maxDate out of the maxDate.qvd.

<code>

MaxDate:

Load MaxDate from data\MaxDate.qvd;

Let vMaxDate = peek(MaxDate,-1);

drop table MaxDate;

Inc:

Select * from dbo.Table1 where CreatedDate >'$(vMaxDate)';

store Inc into Data\inc.qvd;

</code>

Now, concatenate the Complete and Inc qvd.

NB! To be sure to include all the data, you should consider doing a ...where CreatedDate>='$(vMaxDate)' and then use the "where not exists". This will slow down the performance, but if the db is updated 24/7 this might be the only way to go.

[#3]

Unfortunatly the Source table has changed significantly between a Load completing and a seperate step query for maxdate from the DB. Im getting approx 500 rows per second for most of the day and its a 24/7 transactional table

Thanks for the idea of using seperate tables and concatenating using Where not exists, especially if i can do this with resident tables

[#4]

I have an idea, can someone code it for me please. (it came from a modified version of blaises suggestion)

declare initial Startdate and endDate variables //for first load only
Store Startdate,EndDate to NextLoad.QVD
Load * from mydata.qvd where CreatedDate between StartDate and EndDate
//Update NextLoad.qvd
Store Old EndDate as Startdate,EndDate as now + 5 minutes overwriting NextLoad.QVD

This means that I wont need to Query Existing Data.QVD for the max Date as I will always now in advance what it should be

ColinR

[pgriffiths]

This is super fast. Just reload the max of the field from the source QVD again.

A++

From the above example......

MaxDate:

Load date(max(Date)) as MaxDate

From Dates.ild (QVD);