Convert Date and check number of days

[tmumaw]

Hi all,

I am trying to only select employees which have been with the company more than 90 days.  Does anyone see why this would not work?  When I try and write out the Temp table it's empty.

Thanks

TempX:
LOAD [Cost Center] as [Profit Center],
[Employee Name],
Empl.#,
1 as EmployeeCnt,
[Job Key],
[Hire Date],
age(Today(),[Hire Date]) as [Years Serv],
Num(Date(Floor([Hire Date]))) as Num_HireDte,
Date(Floor([Prior Exp.])) as [Prior Exp],
Date(Floor([Davey Exp.])) as DaveyExp,
Date(Floor(Saf.Viol.Dt)) as [Safety Viol Date],
Subty,
Date(Floor([Lic Dt])) as [LicDt],
Date(Floor([ID Expiry Dt])) as [ID ExpiryDt],
[Drv. Lic],
Years,
if(len(trim([CDP L-In])) > 0, 'C' & ' ' & Date(Floor([CDP L-In]))) as CDP_L_In,
if(len(trim([CDP Lndsc])) > 0, 'C' & ' ' & Date(Floor([CDP Lndsc]))) as CDP_Lndsc,
if(len(trim([CDP BrMgr])) > 0, 'C' & ' ' & Date(Floor([CDP BrMgr]))) as CDP_BrMgr,
if(len(trim([DCPR])) > 0, 'E' & ' ' & Date(Floor([DCPR]))) as [DCPR],
if(len(trim([DFAC])) > 0, 'E' & ' ' & Date(Floor([DFAC]))) as [DFAC],
if(len(trim([DFAI])) > 0, 'E' & ' ' & Date(Floor([DFAI]))) as [DFAI],
if(len(trim([DDDC])) > 0, 'E' & ' ' & Date(Floor([DDDC]))) as [DDDC],
if(len(trim([DDDI])) > 0, 'E' & ' ' & Date(Floor([DDDI]))) as [DDDI],
Num(Date(Floor([DCPR]))) as Num_DCPR,
Num(Date(Floor([DFAC]))) as Num_DFAC,
Num(Date(Floor([DFAI]))) as Num_DFAI,
Num(Date(Floor([DDDC]))) as Num_DDDC,
Num(Date(Floor([DDDI]))) as Num_DDDI,
Num(today()) as Num_Current,
Num([Hire Date]) as Num_HireDate
// Num(today()) - Num([Hire Date]) as WorkDays
FROM
[\\KENT\DATASTORE\DATA\R3\Safety\overallcommercial.xls]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

TempY:
Load *,
Num_Current - Num_HireDate as WorkDays
Resident TempX
;
Drop Table TempX;

Temp:
Load *
Resident TempY
Where WorkDays > 90
;
Drop Table TempY;

[sunny_talwar · Accepted solution]

May be you need NoConcatenate

Temp:

NoConcatenate
Load *
Resident TempY
Where WorkDays > 90;

[sunny_talwar]

Another way would be to do this using preceding load:

Temp:
LOAD *
Where WorkDays > 90;
LOAD *,
Num_Current - Num_HireDate as WorkDays;
LOAD [Cost Center] as [Profit Center], 
[Employee Name], 
Empl.#,
1 as EmployeeCnt, 
[Job Key], 
[Hire Date],
age(Today(),[Hire Date]) as [Years Serv],
Num(Date(Floor([Hire Date]))) as Num_HireDte, 
Date(Floor([Prior Exp.])) as [Prior Exp], 
Date(Floor([Davey Exp.])) as DaveyExp, 
Date(Floor(Saf.Viol.Dt)) as [Safety Viol Date], 
Subty, 
Date(Floor([Lic Dt])) as [LicDt], 
Date(Floor([ID Expiry Dt])) as [ID ExpiryDt], 
[Drv. Lic], 
Years,
if(len(trim([CDP L-In])) > 0, 'C' & ' ' & Date(Floor([CDP L-In]))) as CDP_L_In,
if(len(trim([CDP Lndsc])) > 0, 'C' & ' ' & Date(Floor([CDP Lndsc]))) as CDP_Lndsc,
if(len(trim([CDP BrMgr])) > 0, 'C' & ' ' & Date(Floor([CDP BrMgr]))) as CDP_BrMgr,
if(len(trim([DCPR])) > 0, 'E' & ' ' & Date(Floor([DCPR]))) as [DCPR],
if(len(trim([DFAC])) > 0, 'E' & ' ' & Date(Floor([DFAC]))) as [DFAC],
if(len(trim([DFAI])) > 0, 'E' & ' ' & Date(Floor([DFAI]))) as [DFAI],
if(len(trim([DDDC])) > 0, 'E' & ' ' & Date(Floor([DDDC]))) as [DDDC],
if(len(trim([DDDI])) > 0, 'E' & ' ' & Date(Floor([DDDI]))) as [DDDI],
Num(Date(Floor([DCPR]))) as Num_DCPR,
Num(Date(Floor([DFAC]))) as Num_DFAC,
Num(Date(Floor([DFAI]))) as Num_DFAI,
Num(Date(Floor([DDDC]))) as Num_DDDC,
Num(Date(Floor([DDDI]))) as Num_DDDI,
Num(today()) as Num_Current,
Num([Hire Date]) as Num_HireDate 
// Num(today()) - Num([Hire Date]) as WorkDays
FROM
[\\KENT\DATASTORE\DATA\R3\Safety\overallcommercial.xls]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

[tmumaw]

thanks Sunny T.....Don't you ever take a break.  I ask questions through the day and you are there, and now at night.

[sunny_talwar]

Its evening here still

[tmumaw]

Where are you at....

[sunny_talwar]

Northeast part of United States

[tmumaw]

Me too....NE Ohio

[sunny_talwar]

I am very near to Philadelphia

[tmumaw]

Are you headed to the conference in May