Formula to capture last 7 days of data

Nicole Hicks

Summary

Formula to capture last 7 days of data

Content

Hello,

 

We are looking to capture requisition in specific statuses (Approved, To Be Approved, Draft, Sourcing and Filled) for the last seven calendar days.  Look for assistance in creating a formula to capture that timeframe so I can set up a weekly agent to run the report.

rajArun

You may use the below variable: 

"date variable" > TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE) 

Thanks to Manoj who had posted the above formula in another post. Here is the link to the original post: 

 

Thanks,

Arun

 

Joy Hanneman-21499

Hello!

Would you by chance know how to structure this formula to bring back candidates who have been rejected in the last seven days?

 

Thanks!

Joy

Ramesh Nakka

Hi Joy,

You can use the below for formula for  Rejected Candidates for the last 7 days.

 

"Application Current CSW"."Application Current CSW Status" IS IN Candidate Reject - Not Applicant;Candidate Reject — No Business Need;Candidate Rejected;Candidate Withdrew
AND
"Application Current CSW Start Date"."Application Current CSW Start Date"  is Greater Than TIMESTAMPADD(SQL_TSI_DAY, -6,"Special Objects"."Current Date")

 

Regards,

Ramesh

Joy Hanneman-21499

Hi Ramesh, thank you so much! This ended up working for my needs.

Joy

Ramesh Nakka

Thanks Joy. Great to know that.

 

 

Regards,

Ramesh

 

 

Rhea Evans

I am looking at incomplete submissions and desire to see those that are in the last seven days.

I am using the field Submission Created Date in the Criteria as a filter.  I can enter a value of 3/8/2019 and all works fine.  Using your notes above, I'm using 'Add More Options" > SQL Expressions and entered:   "Submission Dates"."Submission Created Date" >= TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE)

After running the report, an error is presented:

Error getting drill information: SELECT "Submission Dates"."Submission Created Date" saw_0, "Submission General Info"."Submission Is Completed" saw_1, "Submission General Info"."Career Site Last Page Reached Name (BL)" saw_2, "Submission General Info"."Career Site Last Page Reached Sequence" saw_3, "Candidate Identification"."Name" saw_4, "Candidate Identification"."Email" saw_5, "Candidate Identification"."Candidate Identifier" saw_6, "Requisition Identification"."Req. Identifier" saw_7, "Submission Source"."Submission Source (BL)" saw_8, "Requisition Identification"."Title (BL)" saw_9 FROM "Recruiting" WHERE ("Submission General Info"."Submission Originator" = 'Candidate') AND ("Submission General Info"."Career Site Name (BL)" IN ('Corporate', 'Corporate_mobile')) AND (("Submission Dates"."Submission Created Date" IN ("Submission Dates"."Submission Created Date" >= TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE)))) AND ("Requisition Organization"."Organization Level1 Name" = 'Aerospace') AND ("Requisition Primary Location"."Location Level1 Name" = 'United States') AND ("Submission General Info"."Submission Is Completed" = 'No')

 

Error Codes: YQCO4T56:OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P

Odbc driver returned an error (SQLExecDirectW).

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)

State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)

State: HY000. Code: 27002. [nQSError: 27002] Near <>=>: Syntax error (HY000)

State: HY000. Code: 26012. [nQSError: 26012] . (HY000)

 

Any help you could provide?  Thanks, Rhea

rajArun

Hi Rhea,

You have to select "Is Greater Than" condition and then add SQL Expression as TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE)

See the attached image.

Hope this fixes the issue.

Regards,
Arun