Query single column data into three columns

  • stephen.aa

    SSChasing Mays

    Points: 601

    Hello:

    I have a table that has the JobNo and Time1 Columns.

    EmpNo    |    JobNo             |    Time1

    8000        |    Office               |     4.5

    8000        |    Sales                |     3.5

    8000        |    200003-01     |     63

    8001        |    Office                |     7.5

    8002       |    Sales                 |     19.25

    I would like to add these up and create four columns

    EmpNo    |    Sales    |    Office    |    Billable

    8000        |    3.5        |    4.5         |    63

    8001         |    0           |    7.5         |    0

    8002        |    19.25    |    0            |    0

    Here's my attempt at a query:

    SELECT JobNo, Time1

    FROM (SELECT SUM(Time1) AS Office

    WHERE (JobNo = 'Office')

    UNION ALL

    SELECT SUM(Time1) AS Sales

    WHERE (JobNo = 'Sales')

    UNION ALL

    SELECT SUM(Time1) AS Billable

    WHERE (JobNo <> 'Office') OR

    (JobNo <> 'Sales')) AS derivedtbl_1

    GROUP BY derivedtbl_1

    Steve Anderson

  • pietlinden

    SSC Guru

    Points: 62816

    Like this?

    Setup:

    --Setup:
    CREATE TABLE WorkData (
     EmpNo INT,
     JobNo VARCHAR(10),
     Hrs DECIMAL(4,2));

    -- add some data
    INSERT INTO WorkData VALUES
    (8000 , 'Office' , 4.5)
    ,(8000 , 'Sales' , 3.5)
    ,(8000 , '200003-01' , 63)
    ,(8001 , 'Office' , 7.5)
    ,(8002 , 'Sales' , 19.25);

    Setup:
    CREATE TABLE WorkData (
     EmpNo INT,
     JobNo VARCHAR(10),
     Hrs DECIMAL(4,2));
    -- solution
    SELECT EmpNo
    , JobNo
    , [Sales] = SUM(CASE WHEN JobNo = 'Sales' THEN Hrs ELSE 0 END)
    , [Office] = SUM(CASE WHEN JobNo = 'Office' THEN Hrs ELSE 0 END)
    , [Billable] = SUM(CASE WHEN JobNo = '200003-01' THEN Hrs ELSE 0 END)
    FROM Workdata
    GROUP BY EmpNo, JobNo;
  • Jeffrey Williams

    SSC Guru

    Points: 88560

    You are looking at a cross-tab or pivot of the data:

     Select EmpNo
    , Sales = sum(Case When JobNo = 'Sales' Then Time1 End)
    , Office = sum(Case When JobNo = 'Office' Then Time1 End)
    , Billable = sum(Case When JobNo Not In ('Sales', 'Office') Then Time1 End)
    From yourTable
    Where {some criteria}
    Group By
    EmpNo;

    This is a cross-tab version and generally the easiest and most efficient way.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720070

    Are there always 3 (or less) rows for an employee? Or are there random non-sales/non-office items?

    You're really trying to pivot here. You might look at Pivot, though this isn't necessarily great for performance.

    Getting this for items that aren't there is tough, as you'll be trying to fill in the space for employees to try and match things up.

     

  • stephen.aa

    SSChasing Mays

    Points: 601

    Simply amazing!!

    My next question is, is there a good book you would recommend to teach me all these tricks?

     

    Thank you!!

    Steve Anderson

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720070

  • Jeff Moden

    SSC Guru

    Points: 996676

    stephen.aa wrote:

    Simply amazing!!

    My next question is, is there a good book you would recommend to teach me all these tricks?

    Thank you!!

    CROSSTABs are a bit of an ancient "Black Arts" method that not many people cover anymore but, as you've just seen, are still incredibly useful and are quite easy to do.  There are a couple of articles that I wrote on the subject that make CROSSTABs pretty easy to understand and then first one also contains a performance comparison between CROSSTABs and PIVOT (which I don't recommend using).  The second article explains how to do dynamic CROSSTABs fairly easily.  Here are the links...

    https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%e2%80%93-converting-rows-to-columns-1

    https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • stephen.aa

    SSChasing Mays

    Points: 601

    I played around with the query a little and added percentage columns.

    The math works, but it seems to default to six decimal places.  100% - 100.000000. Is there a way to round and keep as an integer?

    Thanks!

    SELECT TimeData.EmpNo, EmpData.EmpNameFirst, EmpData.EmpNameLast, SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) AS Total, SUM(CASE WHEN JobNo = 'Parts' THEN Time1 ELSE 0 END) AS Parts, 
    SUM(CASE WHEN JobNo = 'Parts' THEN Time1 ELSE 0 END) / SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) * 100 AS PParts, SUM(CASE WHEN JobNo = 'Sales' THEN Time1 ELSE 0 END) AS Sales,
    SUM(CASE WHEN JobNo = 'Sales' THEN Time1 ELSE 0 END) / SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) * 100 AS PSales, SUM(CASE WHEN JobNo = 'Office' THEN Time1 ELSE 0 END) AS Office,
    SUM(CASE WHEN JobNo = 'Office' THEN Time1 ELSE 0 END) / SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) * 100 AS POffice, SUM(CASE WHEN JobNo NOT IN ('Parts', 'Sales', 'Office', 'Holiday', 'PTO')
    THEN Time1 ELSE 0 END) AS Billable, SUM(CASE WHEN JobNo NOT IN ('Parts', 'Sales', 'Office', 'Holiday', 'PTO') THEN Time1 ELSE 0 END) / SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) * 100 AS PBillable
    FROM TimeData INNER JOIN
    EmpData ON TimeData.EmpNo = EmpData.EmpNo
    WHERE (TimeData.Date1 BETWEEN @SDate AND @EDate)
    GROUP BY TimeData.EmpNo, EmpData.EmpNameLast, EmpData.EmpNameFirst
    ORDER BY EmpData.EmpNameLast

    Steve Anderson

  • Jeffrey Williams

    SSC Guru

    Points: 88560

    You can wrap the calculation in a CAST or CONVERT:

    CAST(SUM(CASE WHEN JobNo = 'Parts' THEN Time1 ELSE 0 END) / SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) * 100 AS numeric(5,2)) AS PParts,

    I used NUMERIC(5,2) so you get 2 decimal places for the percentages.  If you really want an integer - then change from numeric to int or change the 2 to a 0.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeff Moden

    SSC Guru

    Points: 996676

    stephen.aa wrote:

    I played around with the query a little and added percentage columns.

    The math works, but it seems to default to six decimal places.  100% - 100.000000. Is there a way to round and keep as an integer?

    Thanks!

    SELECT TimeData.EmpNo, EmpData.EmpNameFirst, EmpData.EmpNameLast, SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) AS Total, SUM(CASE WHEN JobNo = 'Parts' THEN Time1 ELSE 0 END) AS Parts, 
    SUM(CASE WHEN JobNo = 'Parts' THEN Time1 ELSE 0 END) / SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) * 100 AS PParts, SUM(CASE WHEN JobNo = 'Sales' THEN Time1 ELSE 0 END) AS Sales,
    SUM(CASE WHEN JobNo = 'Sales' THEN Time1 ELSE 0 END) / SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) * 100 AS PSales, SUM(CASE WHEN JobNo = 'Office' THEN Time1 ELSE 0 END) AS Office,
    SUM(CASE WHEN JobNo = 'Office' THEN Time1 ELSE 0 END) / SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) * 100 AS POffice, SUM(CASE WHEN JobNo NOT IN ('Parts', 'Sales', 'Office', 'Holiday', 'PTO')
    THEN Time1 ELSE 0 END) AS Billable, SUM(CASE WHEN JobNo NOT IN ('Parts', 'Sales', 'Office', 'Holiday', 'PTO') THEN Time1 ELSE 0 END) / SUM(CASE WHEN JobNo NOT IN ('Holiday', 'PTO') THEN Time1 ELSE 0 END) * 100 AS PBillable
    FROM TimeData INNER JOIN
    EmpData ON TimeData.EmpNo = EmpData.EmpNo
    WHERE (TimeData.Date1 BETWEEN @SDate AND @EDate)
    GROUP BY TimeData.EmpNo, EmpData.EmpNameLast, EmpData.EmpNameFirst
    ORDER BY EmpData.EmpNameLast

    Steve,

    Consider the old programming trick of using "Divide'n'Conquer" to "DRY" (Don't Repeat Yourself) your code out for both readability, modifiability, and performance.  I don't have your tables to check my work with but the following should produce the same result as your code above as well as keeping your percentages as rounded integers.

       WITH
    cteDry AS
    (--===== This DRYs out the code for readability. It also does most of the work for the CROSSTAB
    SELECT EmpNo
    ,Parts = SUM(IIF(JobNo = 'Parts' ,Time1,0))
    ,Sales = SUM(IIF(JobNo = 'Sales' ,Time1,0))
    ,Office = SUM(IIF(JobNo = 'Office',Time1,0))
    ,Billiable = SUM(IIF(JobNo NOT IN ('Parts','Sales','Office') ,Time1,0))
    ,Total = SUM(Time1)
    FROM dbo.TimeData
    WHERE JobNo NOT IN ('Holiday','PTO') --This keeps these out of all calculations in one "swell-foop". 😀
    AND Date1 >= @SDate AND Date1 < DATEADD(dd,1,@EDate) --Get out of the habit of using BETWEEN. It will burn you someday. 😀
    GROUP BY EmpNo
    )--==== Now the calculations are easy and so is the readability.
    SELECT emp.EmpNo, emp.EmpNameFirst, emp.EmpNameLast
    ,tim.Parts ,PartsPct = CONVERT(INT,ROUND(tim.Parts *100.0/tim.Total,0))
    ,tim.Sales ,SalesPct = CONVERT(INT,ROUND(tim.Sales *100.0/tim.Total,0))
    ,tim.Office ,OfficePct = CONVERT(INT,ROUND(tim.Office *100.0/tim.Total,0))
    ,tim.Billiable ,BilliablePct = CONVERT(INT,ROUND(tim.Billiable*100.0/tim.Total,0))
    ,tim.Total --Keep in mind that none of this includes 'Holiday' or 'PTO' time.
    FROM cteDry tim
    JOIN dbo.EmpData emp ON emp.EmpNo = tim.EmpNo
    ORDER BY EmpNameLast, EmpNameFirst
    ;

    Also remember that "Set Based" doesn't necessarily mean you have to do everything in a single SELECT (nor even in a single query but we don't need to go that far in this code because  of the "blocking operator" of "GROUP BY" in the CTE).

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • jcelko212 32090

    SSCrazy Eights

    Points: 9013

    The real answer, which you don't want to hear, is it any time you do any kind of rounding, you are going to get some error. You just have to decide what kind of error you want; should each individual measurement be rounded according to some rule? Should the total be adjusted to always work out to 100%? This is a design decision and it's always technically wrong.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • Jeff Moden

    SSC Guru

    Points: 996676

    Nothing like stating the obvious, Joe.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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