Script to load only data from last month

[#1]

Forgive me I am new to this and don't know any SQL.

Would someone help me if possible.

I have a load script that connects to an ODBC data source however I only want to retrieve data when (for example) the field opened_at is from last month. The script I'm currently using is shown below however it is taking 20 minute to execute because it's dragging in all records from the file:-

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET FirstWeekDay=0;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-GB';

ODBC CONNECT32 TO DataSource\ (UserId is yyyyyyyy, Password is "xxxxxxxxx");


LOAD
Year(opened_at) as OpenedYear,
Month(opened_at) as OpenedMonth,
"dv_assigned_to",
"dv_assignment_group",
"dv_caller_id",
"dv_category",
"dv_closed_by",
"dv_cmdb_ci",
"dv_contact_type",
"dv_escalation",
"dv_impact",
"dv_incident_state",
"dv_opened_by",
"dv_parent",
"dv_priority",
"dv_state",
"dv_u_business_service",
"dv_u_completed_by",
"dv_u_ooh_call",
"dv_u_secondary_business_services",
"dv_u_verify_the_business_service",
number,
"closed_at",
"opened_at",
"reassignment_count";

SQL SELECT active,
"dv_assigned_to",
"dv_assignment_group",
"dv_caller_id",
"dv_category",
"dv_closed_by",
"dv_cmdb_ci",
"dv_contact_type",
"dv_escalation",
"dv_impact",
"dv_incident_state",
"dv_opened_by",
"dv_parent",
"dv_priority",
"dv_state",
"dv_u_business_service",
"dv_u_completed_by",
"dv_u_ooh_call",
"dv_u_secondary_business_services",
"dv_u_verify_the_business_service",
number,
"closed_at",
"opened_at",
"reassignment_count"
FROM "SCHEMA".OAUSER.incident;

Thanks in advance.

[swuehl]

Maybe use a WHERE clause with your SQL statement?

The correct syntax to compare your field value against your requested date range might depend on your DBMS, so if in doubt, ask your friendly DBMS /SQL guru nearby.

[vishsaggi]

Try this in your where clause: Like

LOAD *;

SQL

SELECT field1,

              field2,.....

FROM scheme.oauser.incident

WHERE opened_at >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) AND

              opened_at < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);

OR

LOAD *

WHERE opened_at >= MonthStart(AddMonths(Today(), -1)) AND

              opened_at < MonthStart(Today());

SQL

SELECT field1,

              field2,.....

FROM scheme.oauser.incident;

[#4 · Accepted solution]

Thank you both Stefan and Vishwarath I have resolved this with a bit of help from Stefan's comment and a colleague. The revised SQL is below:-

FROM "SCHEMA".OAUSER.incident
WHERE year(opened_at)=year(now())
AND month(opened_at)=month(now())-1
;

All I need to do now is workout how to modify the SQL if the month is January (1)

Thank you both