Post reply

Needed Urgent help please with a TSQL code for report

  • April 11, 2013 at 3:36 pm

    #270960

    Dear friends,

    i have a dead line to do the following requirement for 2 fields-

    1) Age (Working Days)"a) Don't display this if the job posting is in Closed status

    b) If the job posting is in Halted status, only display this data if there is no corresponding work order.

    Calculate as the number of working days from the Job Posting Create Date until the day prior to the Report Run Date, inclusive.

    For example, if the Job Posting Create Date is March 18, 2013 and the Report Run Date is April 8, the age would be 15 days. Working days should exclude weekends."

    Age Group (Working Days)"a) Don't display this if the job posting is in Closed status

    b) If the job posting is in Halted status, only display this data if there is no corresponding work order.

    2) Based on the Age (Working Days), display one of these selections:

    0 - 29

    30 - 59

    60 - 89

    90 - 119

    120 +

    In the example display ""0 - 29""."

    for the 1st field I have the code -

    (datediff(dd,convert(int,[WorkForce_JobPosting].[Job Posting Create Date_JP]),convert(int,[WorkForce_JobPosting].[Report Run Date_BYR])+1))

    - (datediff(WK,convert(int,[WorkForce_JobPosting].[Job Posting Create Date_JP]),convert(int,[WorkForce_JobPosting].[Report Run Date_BYR]))*2)

    - (CASE when DATENAME(DW,convert(int,[WorkForce_JobPosting].[Report Run Date_BYR])) = 'Sunday' then 1 else 0 END)

    - (CASE when DATENAME(DW,convert(int,[WorkForce_JobPosting].[Job Posting Create Date_JP])) = 'Saturday' then 1 else 0 END)

    but not sure how to implement it conditionally and need help with the second field to display range based on the output of this field.

    thanks

    Dhananjay

  • April 11, 2013 at 3:51 pm

    #1605798

    Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply