Load only today's date information

[#1]

Please help me on what seems to be a really simple task.

So I only want to load todays date. Date = todays date

DaysFilledToday:

Qualify *;

ODBC CONNECT TO [MS Access Database;DBQ=G:\locumspm\BRT Team\Data Shack\CHLT DataShack DaysFilled.accdb];

LOAD ID,

    Date,

    month(Date) as MonthDate,

    year(Date) as YearDate,

     if((today()-Date)<62,1,0) as EightWeekDaysFilledHistory,

    Division,

    Team,

    Specialty,

    `Job Specialty`,

    `Assignment Status`,

    `Provider JDE #`,

    `Cost Center #`,

    `Provider Name`,

    `Provider Specialty`,

    `Job #`,

    `Asgn From`,

    `Job Type`,

    `Provider Status`,

    `Asgn To`,

    Recruiter,

    `Client Dev`,

    `Asgn Prv Rep`,

    `Asgn Clt Rep`,

    `Client Name`,

    `Client JDE#`,

    `Buyer Status`,

    `New Provider`,

    `WorkSite Name`,

    `WorkSite State`,

    Month,

    monthend(Month) as MonthEnd,

    Days;

SQL SELECT *

FROM `Days Filled`;

store DaysFilled into QVDs\DaysFilledToday.qvd;

drop table DaysFilledToday;

(qvd);

Thank you in advance.

[jonathandienst]

Hi

One really simple way would be:

     `WorkSite State`,
     Month,
     monthend(Month) as MonthEnd,
     Days
     Where Floor(Date) = Today();
SQL SELECT *
FROM `Days Filled`;

The downside is that the date filter is on the client, so the SQL query will return everything first. If the dataset is small, that is no problem

Regards

Jonathan

[jonathandienst]

The other way, for larger data sets:

Let zToday = Date(Today(), 'YYYY-MM-DD');
...
...
SQL SELECT *
FROM `Days Filled`
WHERE Date = '$(zToday)';

The date format (YYYY-MM-DD) must match the date format of your database, so adjust if necessary.

Regards

Jonathan

[#4]

is there any way to get the maximum date without doing a group by?

[jonathandienst]

I don't understand the question. Please provide more information.

Regards

Jonathan

[Nicole-Smith]

You can use a where statement to do the job.  Consider the following load script (I have also attached a .qvw file containing an example of this):

Table:

LOAD * INLINE [

Date, Number

4/1/12, 1

4/3/12, 3

4/15/12, 15

4/23/12, 23

]

WHERE Date = today();

[trey_bayne]

I don't understand the connection between your first question, loading only Today's date, and second, finding the last date in a data set.

If you are trying to find the last date loaded in a data set you can load

max_date_table:

load Date ORDER BY Date ASC;

SQL SELECT *

FROM `Days Filled`;

Set vMaxDate = Peek('Date',-1,'max_date_table';

The value stored in the variable $(vMaxDate) would be the last date loaded in from the table max_date_table. Since you specified the Load order to be by the field "Date" in ASC (Ascending) order the latest date would be the last one.