none
Report Builder 3 - SQL subquery to get unique values.

    Question

  • Hi. I need some help with an sql query to get unique values.

    Using the following sample data:

    fee Pin fee Ref person Occupation fee Bud Rec Time Elapsed
    DUB    BM01 EMP 2610 600
    DUB    BM01 EMP 2610 600
    DUB    BM01 EMP 2610 480
    DUB    BM01 EMP 2610 180
    DUB    BM01 EMP 2610 510
    DUB    CD01 EMP 2610 600
    DUB    CD01 EMP 2610 150
    DUB    CD01 EMP 2610 90
    DUB    EE01 EMP 2218 510
    DUB    EE01 EMP 2218 510
    LEE    LR01 EMP 1177 450
    LEE    LR01 EMP 1177 12
    LEE    LR01 EMP 1177 150
    LEE    LR01 EMP 1177 168
    LEE    LR01 EMP 1177 48
    LEE    PS01 EMP 1957 246
    LEE    PS01 EMP 1957 222
    LEE    PS01 EMP 1957 12


    I need to be able to get one single value for feeBudRec returned for each feeRef. The value of each feeBudRec is always the same for each individual feeRef (eg for every data row feeRef LR01 will have a feeBudRec of 1177).

    The reason why I need to get a single feeBudRec value for each feeRef is that I need to be able to total the feeBudRec value for each feeRef in a feePin (eg for feePin LEE, I need to total the feeBudRec values for LR01 and PS01, which should be 1177 + 1957 to get a total of 3134; but if I don't have unique values for feeBudRec, it will add the values for each row, which would bring back a total of 11756 for the 8 LEE rows).

    My experience with writing SQL queries is limited, but from searching the internet, it looks like I'll need to put in a subquery into my SQL query in order to get a single unique feeBudRec figure for each feeRef, and that a subquery that gets a minimum feeBudRec value for each feeRef should work for me.

    Based on examples I've found, I think the following subquery should work:

    SELECT a.feeRef, a.feeBudRec
      FROM (
        SELECT uvw_EarnerInfo.feeRef, Min(uvw_EarnerInfo.feeBudRec) as AvailableTime
        FROM uvw_EarnerInfo
        GROUP BY
        uvw_EarnerInfo.feeRef
    ) as x INNER JOIN uvw_EarnerInfo as a ON a.feeRef = x.feeRef AND a.feeBudRec = x.AvailableTime;


    The problem is that I have no idea how to insert that subquery into the query I'm using to produce the report (as follows):

    SELECT
      uvw_EarnerInfo.feeRef
      ,uvw_EarnerInfo.PersonName
      ,uvw_EarnerInfo.PersonSurname
      ,uvw_EarnerInfo.feePin
      ,uvw_RB_TimeLedger.TimeDate
      ,uvw_RB_TimeLedger.matRef
      ,uvw_RB_TimeLedger.TimeTypeCode
      ,uvw_RB_TimeLedger.TimeCharge
      ,uvw_RB_TimeLedger.TimeElapsed
      ,uvw_WoffTimeByTime.WoffMins
      ,uvw_WoffTimeByTime.WoffCharge
      ,uvw_EarnerInfo.feeBudRec
      ,uvw_EarnerInfo.personOccupation
    FROM
      uvw_RB_TimeLedger
      LEFT OUTER JOIN uvw_WoffTimeByTime
        ON uvw_RB_TimeLedger.TimeId = uvw_WoffTimeByTime.TimeId
      RIGHT OUTER JOIN uvw_EarnerInfo
        ON uvw_EarnerInfo.feeRef = uvw_RB_TimeLedger.feeRef
    WHERE
      uvw_RB_TimeLedger.TimeDate >= @TimeDate
      AND uvw_RB_TimeLedger.TimeDate <= @TimeDate2


    If that subquery will get the correct results, can anyone please help me with inserting it into my report query. Otherwise, can anyone let me know what I will need to do to get a unique feeBudRec value for each feeRef?




    Thursday, July 19, 2012 10:20 AM

Answers

  • You can do something like this:

    select p.product_name, p.supplier_name,
    (select order_id from order_items where product_id = 101)
    as order_idfrom product p where p.product_id = 101

    This may work for you: (maybe you should modify something for it to work properly)

    SELECT
      uvw_EarnerInfo.feeRef
      ,uvw_EarnerInfo.PersonName
      ,uvw_EarnerInfo.PersonSurname
      ,uvw_EarnerInfo.feePin
      ,uvw_RB_TimeLedger.TimeDate
      ,uvw_RB_TimeLedger.matRef
      ,uvw_RB_TimeLedger.TimeTypeCode
      ,uvw_RB_TimeLedger.TimeCharge
      ,uvw_RB_TimeLedger.TimeElapsed
      ,uvw_WoffTimeByTime.WoffMins
      ,uvw_WoffTimeByTime.WoffCharge
      ,(select ei.feeBudRec from uvw_EarnerInfo ei where ei.feeRef=uvw_EarnerInfo.feeRef)
      ,uvw_EarnerInfo.personOccupation
    FROM
      uvw_RB_TimeLedger
      LEFT OUTER JOIN uvw_WoffTimeByTime
        ON uvw_RB_TimeLedger.TimeId = uvw_WoffTimeByTime.TimeId
      RIGHT OUTER JOIN uvw_EarnerInfo
        ON uvw_EarnerInfo.feeRef = uvw_RB_TimeLedger.feeRef
    WHERE
      uvw_RB_TimeLedger.TimeDate >= @TimeDate
      AND uvw_RB_TimeLedger.TimeDate <= @TimeDate2

    Friday, July 20, 2012 6:18 AM

All replies

  • To select one feeRef and feeBudRec in SQL you can use Distinct.

    In your case: 

    select distinct a.feeRef, a.feeBudRec
    from uvw_EarnerInfo

    Thursday, July 19, 2012 2:29 PM
  • If I use Select Distinct on the whole query, the results are exactly the same - each row of data includes a value for feeBudRec, so the totals for feePin are incorrect.

    If I need to use Select Distinct on a subquery to my query, that brings me back to my original problem of not knowing how to write a subquery into the query.

    Thursday, July 19, 2012 3:42 PM
  • You can do something like this:

    select p.product_name, p.supplier_name,
    (select order_id from order_items where product_id = 101)
    as order_idfrom product p where p.product_id = 101

    This may work for you: (maybe you should modify something for it to work properly)

    SELECT
      uvw_EarnerInfo.feeRef
      ,uvw_EarnerInfo.PersonName
      ,uvw_EarnerInfo.PersonSurname
      ,uvw_EarnerInfo.feePin
      ,uvw_RB_TimeLedger.TimeDate
      ,uvw_RB_TimeLedger.matRef
      ,uvw_RB_TimeLedger.TimeTypeCode
      ,uvw_RB_TimeLedger.TimeCharge
      ,uvw_RB_TimeLedger.TimeElapsed
      ,uvw_WoffTimeByTime.WoffMins
      ,uvw_WoffTimeByTime.WoffCharge
      ,(select ei.feeBudRec from uvw_EarnerInfo ei where ei.feeRef=uvw_EarnerInfo.feeRef)
      ,uvw_EarnerInfo.personOccupation
    FROM
      uvw_RB_TimeLedger
      LEFT OUTER JOIN uvw_WoffTimeByTime
        ON uvw_RB_TimeLedger.TimeId = uvw_WoffTimeByTime.TimeId
      RIGHT OUTER JOIN uvw_EarnerInfo
        ON uvw_EarnerInfo.feeRef = uvw_RB_TimeLedger.feeRef
    WHERE
      uvw_RB_TimeLedger.TimeDate >= @TimeDate
      AND uvw_RB_TimeLedger.TimeDate <= @TimeDate2

    Friday, July 20, 2012 6:18 AM