May 30, 2013 at 1:16 pm
Hi friends,
I'm basically a Java guy into TSQL sometimes as and when needed- I need a help with a requirement on how to code the below req in SQL - even a Pseduo will help please
//
cenario 2:
You'll need to leverage the SLII database for scenario 2. Include requests in these statuses (for Job Postings, Work Orders and Work Order Revisions): Pending Approval (anything), Approval Paused, Rejected (bottom line: use the same exact method you use in the SLII audit).
- If “Delivery Staffing Justification Audit Flag” = SLII Request and “Miscellaneous Notes” = blank then compare the “ORC entry AC Tenure Exception #” field in the Req Audit (can’t do exact match – need to look at first 12 char) to the “SLII ID” from the SLII Database.
1. If no match then, mark
a. "Owner for Next Action" = WFP/Business
b. "Passed Audit" = No
c. "Audit Failure Summary" = See Miscellaneous Notes
d. “Miscellaneous Notes” = FLAG – Invalid SmartLaborII Job Posting/Work Order
e. "WFP Business Action Items" = Correct Flags
2. If created in SLII before 12:00 AM GMT on June 3, 2013 then, mark
a. “Miscellaneous Notes” = SLII pre June 3, 2013
3. If created in SLII on or after 12:00 AM GMT on June 3, 2013 then, mark
a. "Owner for Next Action" = WFP/Business
b. "Passed Audit" = No
c. "Audit Failure Summary" = See Miscellaneous Notes
d. “Miscellaneous Notes” = FLAG - This request was created in SmartLaborII on or after 12:00 AM GMT on June 3, 2013, the ORCR and Taleo request are not required, please cancel them.
e. "WFP Business Action Items" = Correct Flags
//
thanks
DJ
May 30, 2013 at 1:41 pm
Could you post DDL (tables structure) and sample data? And define what's a match and what else is involved.
I'm thinking on using UPDATE... WHERE [NOT] EXISTS( <query to find the match>)
But it's hard to know how will you define the matchs.
May 31, 2013 at 3:43 am
What I'm asking for is how to use CASE syntax for these validations?
May 31, 2013 at 8:04 am
dhananjay.nagarkar (5/31/2013)
What I'm asking for is how to use CASE syntax for these validations?
You didn't provide a whole lot to go on here. The best we can do is take a shot in the dark. From the scattered requirements this MIGHT be something similar to what you might use for Miscellaneous Notes.
case when [When No Match Whatever That Means] then 'FLAG – Invalid SmartLaborII Job Posting/Work Order '
when SLII.SomeDate < '2013-06-03' then 'SLII pre June 3, 2013'
when SLII.SomeDate >= '2013-06-03' then 'FLAG - This request was created in SmartLaborII on or after 12:00 AM GMT on June 3, 2013, the ORCR and Taleo request are not required, please cancel them.'
end as [Miscellaneous Notes]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply