none
Replace all but one value

    Question

  • I am designing a report in SSRS and ran into a snag with some bad data that I want to "mask" in SQL or SSRS. My end goal is trying to calculate the correct sum for ESTTIME AND HOURS, so whether "masking" the number or re-creating my query to pull the correct data, I am all ears.  My query is below for your reference. 


    The problem I am having is that A.ESTTIME; at times has the same amount for multiple records, but they are technical not duplicates because the L.hours column is different. What I want to do is keep one of the ESTTIME values and zero out the other two.  example of the results are below. The sum should be SUM(ESTTIME) = 1.5 AND SUM(HOURS) = 5.8

    NAME         COMPCODE ORDERNUM SECTIONNUM ESTTIME HOURS FIRSTNAME

    customer 001 1234555 1 1.5 2.0 Joe Doe

    customer 001 1234555 1 1.5 2.5 Joe Doe

    customer 001 1234555 1 1.5 1.3 Joe  Doe

    SELECT

      DISTINCT C.NAME, L.COMPCODE, O.ORDERNUM, SEC.SECTIONNUM, A.ESTTIME, L.HOURS, E.FIRSTNAME, E.LASTNAME

    FROM

      ORDERLN L

      JOIN ORDERS O ON O.ORDERID = L.ORDERID

      JOIN ORDERSEC SEC ON SEC.ORDERID = L.ORDERID AND SEC.SECTION = L.SECTION

      JOIN ORDERASSMT A ON A.ORDERID = O.ORDERID AND A.SECTION = SEC.SECTION AND A.ESTTIME IS NOT NULL  AND A.EMPID = L.MECHANIC

      JOIN JOBCODES J ON J.JOBCODEID = A.JOBCODEID

      JOIN JOBSFLTER F ON F.JOBCODEID = A.JOBCODEID AND F.JOBSFLTID = A.JOBSFLTID

      JOIN EMPLOYEE E ON L.MECHANIC = E.EMPID

      JOIN SHOP S ON O.SHOPID = S.SHOPID

      JOIN UNITS U ON U.UNITID = O.UNITID

      JOIN CUSTOMERS C ON C.CUSTID = U.CUSTID

      JOIN CUSTOMERPAYGRADE P ON P.CUSTID = C.CUSTID

    WHERE

      L.LINETYPE = 'LABOR'

      AND L.VENDORLINE = 'N'

      AND L.HOURS > 0

      AND L.COMPCODE = @compCode

      AND L.SHOPID = @Shop

      AND CHGDATE >= @Start AND CHGDATE < @End

    ORDER BY

    C.NAME, L.COMPCODE, O.ORDERNUM, SEC.SECTIONNUM, A.ESTTIME,  L.HOURS, E.FIRSTNAME, E.LASTNAME ASC

    Thursday, March 14, 2013 6:50 PM

Answers

  • If you group by NAME, COMPCODE, ORDERNUM, SECTIONNUM, ESTTIME in your display table then there is no need to Sum the ESTTIME, just Sum the Hours.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    • Marked as answer by wolfeste Monday, March 18, 2013 9:10 PM
    Thursday, March 14, 2013 7:07 PM
  • Get rid of the DISTINCT keyword. SUM will sum all of the records in a group. DISTINCT will only print one row for multiple identical rows. If you actually have 2 identical rows, the sum will show twice the expected because sum will add both rows but the DISTINCT will hide the duplicate row.

    Rerun both queries without DISTINCT and see if the numbers add up.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    • Marked as answer by wolfeste Monday, March 18, 2013 9:11 PM
    Friday, March 15, 2013 9:22 PM
  • I did that and my results stayed the same. I ended up trying Sum(Distinct L.HOURS) and that ended up working. I am going to do some more testing but I think this wrapped it up. Thank you for your help Tim. 
    • Marked as answer by wolfeste Monday, March 18, 2013 9:10 PM
    Monday, March 18, 2013 9:10 PM

All replies

  • If you group by NAME, COMPCODE, ORDERNUM, SECTIONNUM, ESTTIME in your display table then there is no need to Sum the ESTTIME, just Sum the Hours.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    • Marked as answer by wolfeste Monday, March 18, 2013 9:10 PM
    Thursday, March 14, 2013 7:07 PM
  • When I do the Sum for hours it gives me a number that doesn't seem to match up.  for example it should be 5.8 but it will give me 18.3. Is there a way to find out what data is being summed?
    Thursday, March 14, 2013 8:57 PM
  • Hello,

    Please refer to the following query:

    SELECT
     
      DISTINCT C.NAME, L.COMPCODE, O.ORDERNUM, SEC.SECTIONNUM, A.ESTTIME, E.FIRSTNAME, E.LASTNAME,SUM(L.HOURS)
     
    FROM
     
      ORDERLN L 
    
      JOIN ORDERS O ON O.ORDERID = L.ORDERID
     
      JOIN ORDERSEC SEC ON SEC.ORDERID = L.ORDERID AND SEC.SECTION = L.SECTION
     
      JOIN ORDERASSMT A ON A.ORDERID = O.ORDERID AND A.SECTION = SEC.SECTION AND A.ESTTIME IS NOT NULL  AND A.EMPID = L.MECHANIC
     
      JOIN JOBCODES J ON J.JOBCODEID = A.JOBCODEID
     
      JOIN JOBSFLTER F ON F.JOBCODEID = A.JOBCODEID AND F.JOBSFLTID = A.JOBSFLTID
     
      JOIN EMPLOYEE E ON L.MECHANIC = E.EMPID
     
      JOIN SHOP S ON O.SHOPID = S.SHOPID
     
      JOIN UNITS U ON U.UNITID = O.UNITID
     
      JOIN CUSTOMERS C ON C.CUSTID = U.CUSTID
     
      JOIN CUSTOMERPAYGRADE P ON P.CUSTID = C.CUSTID
     
    WHERE
     
      L.LINETYPE = 'LABOR'
     
      AND L.VENDORLINE = 'N'
     
      AND L.HOURS > 0
     
      AND L.COMPCODE = @compCode
     
      AND L.SHOPID = @Shop
     
      AND CHGDATE >= @Start AND CHGDATE < @End
    GROUP BY
    C.NAME, L.COMPCODE, O.ORDERNUM, SEC.SECTIONNUM, A.ESTTIME, E.FIRSTNAME, E.LASTNAME
     ORDER BY
    C.NAME, L.COMPCODE, O.ORDERNUM, SEC.SECTIONNUM, A.ESTTIME, E.FIRSTNAME, E.LASTNAME ,SUM(L.HOURS) ASC
    
    Regards,
    Fanny Liu

    Fanny Liu
    TechNet Community Support

    Friday, March 15, 2013 8:00 AM
    Moderator
  • That query seems to work for some but not for all. I am running into a situation where the Sum is *2 for the hours column. For example I have only one record that is supposed to be sum. The amount for Hours for this one record = 1.87 and the sum is displaying 3.74.  any way to find out why it is *2?
    Friday, March 15, 2013 1:28 PM
  • Can you post a screenshot of your table? Blank out any internal info using Paint first but I need to see how you have your groups set up.

    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Friday, March 15, 2013 2:14 PM
  • Sum(hours) SS:

    All records:

    

    Friday, March 15, 2013 2:54 PM
  • That is your query results. I want to see the report table so I can see where the 2x effect is occurring and perhaps I will be able to see why.

    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Friday, March 15, 2013 3:57 PM
  • Not sure what you mean by report table.... Do you want to see the structure of each table that I am joining?
    Friday, March 15, 2013 4:52 PM
  • How do you display your data in the report?  Where are you seeing a record value of 1.87 and the sum is displaying 3.74?

    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Friday, March 15, 2013 5:22 PM
  • I have it setup in SSRS to view the report. its identical to what you see in my previous post of the query results.
    Friday, March 15, 2013 5:46 PM
  • sorry the example i posted the image of is different than the example I talked about earlier. You can use that image and see that line 1 and line 2 are doubled. Then if you combine line 3&4 * 2, you will get the sum.
    Friday, March 15, 2013 5:49 PM
  • These are from 2 separate queries. What do the 2 queries look like?

    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Friday, March 15, 2013 7:31 PM
  • SELECT
      DISTINCT C.NAME, L.COMPCODE, O.ORDERNUM, SEC.SECTIONNUM, A.ESTTIME, E.FIRSTNAME, E.LASTNAME, L.HOURS, E.EMPID
    FROM
      ORDERLN L
      JOIN ORDERS O ON O.ORDERID = L.ORDERID
      JOIN ORDERSEC SEC ON SEC.ORDERID = L.ORDERID AND SEC.SECTION = L.SECTION
      JOIN ORDERASSMT A ON A.ORDERID = O.ORDERID AND A.SECTION = SEC.SECTION AND A.ESTTIME IS NOT NULL  AND A.EMPID = L.MECHANIC
      JOIN JOBCODES J ON J.JOBCODEID = A.JOBCODEID
      JOIN JOBSFLTER F ON F.JOBCODEID = A.JOBCODEID AND F.JOBSFLTID = A.JOBSFLTID
      JOIN EMPLOYEE E ON L.MECHANIC = E.EMPID
      JOIN SHOP S ON O.SHOPID = S.SHOPID
      JOIN UNITS U ON U.UNITID = O.UNITID
      JOIN CUSTOMERS C ON C.CUSTID = U.CUSTID
      JOIN CUSTOMERPAYGRADE P ON P.CUSTID = C.CUSTID
    WHERE
      L.LINETYPE = 'LABOR'
      AND L.VENDORLINE = 'N'
      AND L.HOURS > 0
      AND L.SHOPID = '11'
      AND CHGDATE >= '2/1/2013' AND CHGDATE < '3/15/2013'
      AND O.ORDERNUM = '11-000124316'
    GROUP BY
    C.NAME, L.COMPCODE, O.ORDERNUM, SEC.SECTIONNUM, A.ESTTIME, E.FIRSTNAME, E.LASTNAME, E.EMPID, L.HOURS
     ORDER BY
    C.NAME, L.COMPCODE, O.ORDERNUM, SEC.SECTIONNUM, A.ESTTIME, E.FIRSTNAME, E.LASTNAME , L.HOURS ASC

    -----QUERY WITH SUM-------

    SELECT
      DISTINCT C.NAME, L.COMPCODE, O.ORDERNUM, SEC.SECTIONNUM, A.ESTTIME, E.FIRSTNAME, E.LASTNAME, Sum(L.HOURS), E.EMPID
    FROM
      ORDERLN L
      JOIN ORDERS O ON O.ORDERID = L.ORDERID
      JOIN ORDERSEC SEC ON SEC.ORDERID = L.ORDERID AND SEC.SECTION = L.SECTION
      JOIN ORDERASSMT A ON A.ORDERID = O.ORDERID AND A.SECTION = SEC.SECTION AND A.ESTTIME IS NOT NULL  AND A.EMPID = L.MECHANIC
      JOIN JOBCODES J ON J.JOBCODEID = A.JOBCODEID
      JOIN JOBSFLTER F ON F.JOBCODEID = A.JOBCODEID AND F.JOBSFLTID = A.JOBSFLTID
      JOIN EMPLOYEE E ON L.MECHANIC = E.EMPID
      JOIN SHOP S ON O.SHOPID = S.SHOPID
      JOIN UNITS U ON U.UNITID = O.UNITID
      JOIN CUSTOMERS C ON C.CUSTID = U.CUSTID
      JOIN CUSTOMERPAYGRADE P ON P.CUSTID = C.CUSTID
    WHERE
      L.LINETYPE = 'LABOR'
      AND L.VENDORLINE = 'N'
      AND L.HOURS > 0
      AND L.SHOPID = '11'
      AND CHGDATE >= '2/1/2013' AND CHGDATE < '3/15/2013'
      AND O.ORDERNUM = '11-000124316'
    GROUP BY
    C.NAME, L.COMPCODE, O.ORDERNUM, SEC.SECTIONNUM, A.ESTTIME, E.FIRSTNAME, E.LASTNAME, E.EMPID
     ORDER BY
    C.NAME, L.COMPCODE, O.ORDERNUM, SEC.SECTIONNUM, A.ESTTIME, E.FIRSTNAME, E.LASTNAME , Sum(L.HOURS) ASC

    Friday, March 15, 2013 8:05 PM
  • Get rid of the DISTINCT keyword. SUM will sum all of the records in a group. DISTINCT will only print one row for multiple identical rows. If you actually have 2 identical rows, the sum will show twice the expected because sum will add both rows but the DISTINCT will hide the duplicate row.

    Rerun both queries without DISTINCT and see if the numbers add up.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    • Marked as answer by wolfeste Monday, March 18, 2013 9:11 PM
    Friday, March 15, 2013 9:22 PM
  • I did that and my results stayed the same. I ended up trying Sum(Distinct L.HOURS) and that ended up working. I am going to do some more testing but I think this wrapped it up. Thank you for your help Tim. 
    • Marked as answer by wolfeste Monday, March 18, 2013 9:10 PM
    Monday, March 18, 2013 9:10 PM