SQL don't select date range

[#1]

How would I adapt the following script to exclude rows where the SessionStartedDate was in the range 2014-11-03 and 2014-11-09?

QUALIFY *;

UNQUALIFY SessionStartedDate;

DaySummary:

LOAD

    Date(DayStart(Date(Date,'DD/MM/YYYY')),'YYYY-MM-DD') As SessionStartedDate,

  Users,

  Disconnects,

  Reconnects,

  WorkingDayDisconnects,

  WorkingDayReconnects;

SQL Execute [dbo].[DaySummary];

UNQUALIFY *;

[ali_hijazi]

FYI

QUALIFY *;

UNQUALIFY SessionStartedDate;

temp_table:

Load

  Date(DayStart(Date(Date,'DD/MM/YYYY')),'YYYY-MM-DD') As SessionStartedDate,

    num(DayStart(Date)) as _SessionStartedDate

  Users,

  Disconnects,

  Reconnects,

  WorkingDayDisconnects,

  WorkingDayReconnects,

num(DayStart(Date)) as SessionStartedDate;

SQL Execute [dbo].[DaySummary];

DaySummary:

noconcatenate

load * resident temp_table

where _SessionStartedDate<num(daystart(makeDate(2014,11,3))) or  _SessionStartedDate> num(dayStart(makedate(2014,11,9)));

drop table temp_table;

UNQUALIFY *;

[#3]

This could be concatenate or case sensitive issue.

Normally Qlikview database connect syntaxes are very case sensitive. Check that execute statement case, if that's working fine.

Use aliases of columns from parent table in temp table.

[#4]

same error

OLEDB read failed

SQL Execute [dbo].[DaySummary]

[#5]

I get a different error now

Field not found - <_SessionStartedDate>

DaySummary:

noconcatenate

load * resident temp_table

where _SessionStartedDate <num(daystart(makeDate(2014,11,3))) or  _SessionStartedDate > num(dayStart(makedate(2014,11,9)))

This is the whole script

QUALIFY *;

UNQUALIFY SessionStartedDate;

temp_table:

Load

  Date(DayStart(Date(Date,'DD/MM/YYYY')),'YYYY-MM-DD') As SessionStartedDate,

  num(DayStart(Date)) as _SessionStartedDate,

  Users,

  Disconnects,

  Reconnects,

  WorkingDayDisconnects,

  WorkingDayReconnects;

SQL Execute [dbo].[DaySummary];

DaySummary:

noconcatenate

load * resident temp_table

where _SessionStartedDate <num(daystart(makeDate(2014,11,3))) or  _SessionStartedDate > num(dayStart(makedate(2014,11,9)));

drop table temp_table;

UNQUALIFY *;

[ali_hijazi]

you should un qualify this field too

[#7]

The script runs OK. So thanks for that

But all the other fields in the table have been renamed to include the temp table name.

For example DaySummary.Disconnects have been renamed to DaySummary.temp_table.Disconnects

How do i get rid of the temp_table bit?

[ali_hijazi · Accepted solution]

QUALIFY *;

UNQUALIFY SessionStartedDate;

temp_table:

Load

  Date(DayStart(Date(Date,'DD/MM/YYYY')),'YYYY-MM-DD') As SessionStartedDate,

  num(DayStart(Date)) as _SessionStartedDate,

  Users,

  Disconnects,

  Reconnects,

  WorkingDayDisconnects,

  WorkingDayReconnects;

SQL Execute [dbo].[DaySummary];

unqualify *;

DaySummary:

noconcatenate

load * resident temp_table

where _SessionStartedDate <num(daystart(makeDate(2014,11,3))) or  _SessionStartedDate > num(dayStart(makedate(2014,11,9)));

drop table temp_table;