For your various date fields, as in these entries from your load script, are the dates true dates of actually being held a date/time fields ?
Makedate(year(CreatedOnDate),month(CreatedOnDate),day(CreatedOnDate)) as CreateDate,
Year(CreatedOnDate) as WOYear,
Month(CreatedOnDate) as WOMonth,
ActualCompletionDate - CreatedOnDate as AgeofWO,
The reason I asked that as if they being held a date/time fields then they will have a lot more unique values than if they are pure dates.
To convert them to pure dates you need to knock off the decimals, to leave just the integer date, by doing something like:
date ( floor ( [ActualCompletionDate] ) ) as ActualCompletionDate ,
Best Regards, Bill
I think what you posted is not your actual script. The WorkOrderSummaryCounts loads a field AcctNum|PurposeID that doesn't seem to exist in WorkOrders. Also you have a group by clause, but no aggregation function.
If WorkOrders and WorkOrderSummaryCounts have a lot of field names in common you will get a synthetic key. That could be one reason for the increase in file size. You could consider concatenating the summary table to the WorkOrders table. Something like:
Load *, 'detail' as RecordType from ....
Load X,Y,Z, count(Q) as CountQ, 'summary' as RecordType
group by X,Y,Z;
I didn´t find any sum,count, into load below, I think you should have some agregation here, right?
Group by AcctNum|PurposeID, AcctNumUserName,PMScheduleAttached,AgeofWO, WOYear, WOMonth, PriorityID, CraftID, StatusID, AcctNum;