Post reply

Calculating Turnover

  • January 23, 2009 at 5:49 am

    #72414

    Hi! guys

    I'm trying to workout turnover percentage but I just cant seem to get the query right. Please help.

    SELECT Consolidate,

    HeadcountStart = SUM(CASE WHEN Headcount ='Headcount' AND Period = 200801 AND StaffNo IS NOT NULL THEN 1 ELSE 0 END)

    , NewHires = SUM(CASE WHEN Transfertype = 'Newhire' THEN 1 ELSE 0 END)

    , TransfersIn = SUM(CASE WHEN Headcount ='TransferCluster' AND Transfertype = 'TransferIn' THEN 1 ELSE 0 END)

    , [NetTransferOut/Conversions] = SUM(CASE WHEN Headcount ='Headcount' AND Period = 200812 AND StaffNo IS NOT NULL THEN 1 ELSE 0 END)

    -((SUM(CASE WHEN Headcount ='Headcount' AND Period = 200801 AND StaffNo IS NOT NULL THEN 1 ELSE 0 END))

    +(SUM(CASE WHEN Headcount ='TransferCluster' AND Transfertype = 'TransferIn' THEN 1 ELSE 0 END))

    +(SUM(CASE WHEN Transfertype = 'Newhire' THEN 1 ELSE 0 END))

    +(-SUM(CASE WHEN Transfertype = 'Termination' THEN 1 ELSE 0 END)))

    , Terminations = -SUM(CASE WHEN Transfertype = 'Termination' THEN 1 ELSE 0 END)

    , HeadcountEnd = SUM(CASE WHEN Headcount ='Headcount' AND Period = 200812 AND StaffNo IS NOT NULL THEN 1 ELSE 0 END)

    , NettGrowthDecline = (SUM(CASE WHEN Headcount ='Headcount' AND Period = 200812 AND StaffNo IS NOT NULL THEN 1 ELSE 0 END))-(SUM(CASE WHEN Headcount ='Headcount' AND Period = 200801 AND StaffNo IS NOT NULL THEN 1 ELSE 0 END))

    , TurnOverYTD = CASE WHEN Sum (CASE WHEN Transfertype = 'Termination' THEN 1 ELSE 0 END) = 0 Then 0

    Else Sum (CASE WHEN Transfertype = 'Termination' THEN 1 ELSE 0 END)

    +(SUM(CASE WHEN Headcount ='TransferCluster' AND Transfertype = 'TransferOut' THEN 1 ELSE 0 END))

    / Convert (Float(SUM(CASE WHEN Transfertype = 'Termination' THEN 1 ELSE 0 END)))

    +Convert (Float(SUM(CASE WHEN Headcount ='TransferCluster' AND Transfertype = 'TransferOut' THEN 1 ELSE 0 END)))

    +Convert(Float(SUM(CASE WHEN Headcount ='Headcount' AND Period = 200801 AND StaffNo IS NOT NULL THEN 1 ELSE 0 END))) End

    , TransferOut = SUM(CASE WHEN Headcount ='TransferCluster' AND Transfertype = 'TransferOut' THEN 1 ELSE 0 END)

    FROM DTIHeadcount

    GROUP BY Consolidate

    HAVING Consolidate IS NOT NULL

    ORDER BY Consolidate

    when I run this code I get this message: Msg 102, Level 15, State 1, Line 16

    Incorrect syntax near '('.

    Please help.

  • January 23, 2009 at 8:54 am

    #931835

    The error is because you have Convert(Float(data)) which is missing a "," after float. Convert(Float, (Data)). It is in

    this section of code:

    Else Sum (CASE WHEN Transfertype = 'Termination' THEN 1 ELSE 0 END)

    +(SUM(CASE WHEN Headcount ='TransferCluster' AND Transfertype = 'TransferOut' THEN 1 ELSE 0 END))

    / Convert (Float(SUM(CASE WHEN Transfertype = 'Termination' THEN 1 ELSE 0 END)))

    +Convert (Float(SUM(CASE WHEN Headcount ='TransferCluster' AND Transfertype = 'TransferOut' THEN 1 ELSE 0 END)))

    +Convert(Float(SUM(CASE WHEN Headcount ='Headcount' AND Period = 200801 AND StaffNo IS NOT NULL THEN 1 ELSE 0 END))) End

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • January 26, 2009 at 12:52 am

    #932479

    Thanks Jack

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

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