TCC – Date Certain Step/Status Achieved
I ran into an issue trying to get everyone within the last 24 hours that had achieved a certain step/status combination. The hitch was that they could have been moved out of the target step/status so we have to muck around in the history to get it. As it’s a date you need to make sure the projected value you’re comparing to is a date as well since the last 24 hours is always going to be date time. This did the trick. Remember to pay attention to the value types in sub queries like this.
<quer:filtering xmlns:quer="http://www.taleo.com/ws/integration/query">
<quer:greaterThan>
<quer:query projectedClass="Application" alias="sqLocation" preventDuplicates="true" pagingsize="1">
<quer:projections>
<quer:projection alias="AnotherDate">
<quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingHistoryItem.CreationDate"/>
</quer:projection>
</quer:projections>
<quer:filterings>
<quer:filtering>
<quer:equal>
<quer:field path="Number"/>
<quer:field ownerQuery="MainQuery" path="Number"/>
</quer:equal>
</quer:filtering>
<quer:filtering>
<quer:equal>
<quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingCSWItem.Step,Mnemonic"/>
<quer:string>RequiredStep</quer:string>
</quer:equal>
</quer:filtering>
<quer:filtering>
<quer:equal>
<quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingCSWItem.Status,Mnemonic"/>
<quer:string>RequiredStatus</quer:string>
</quer:equal>
</quer:filtering>
<quer:filtering>
<quer:equal>
<quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingHistoryItem.CreationDate"/>
<quer:query projectedClass="Application" alias="sqMotives" preventDuplicates="true" pagingsize="1">
<quer:subQueries/>
<quer:projections>
<quer:projection>
<quer:maximum>
<quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingHistoryItem.CreationDate"/>
</quer:maximum>
</quer:projection>
</quer:projections>
<quer:filterings>
<quer:filtering>
<quer:equal>
<quer:field path="Number"/>
<quer:field ownerQuery="MainQuery" path="Number"/>
</quer:equal>
</quer:filtering>
<quer:filtering>
<quer:equal>
<quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingCSWItem.Step,Mnemonic"/>
<quer:string>RequiredStep</quer:string>
</quer:equal>
</quer:filtering>
<quer:filtering>
<quer:equal>
<quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingCSWItem.Status,Mnemonic"/>
<quer:string>RequiredStatus</quer:string>
</quer:equal>
</quer:filtering>
</quer:filterings>
</quer:query>
</quer:equal>
</quer:filtering>
</quer:filterings>
</quer:query>
<quer:castAsDate>
<quer:addDays>
<quer:date type="now"/>
<quer:integer>-1</quer:integer>
</quer:addDays>
</quer:castAsDate>
</quer:greaterThan>
</quer:filtering>