Select Distinct results from just one Table/column

  • cmw 66135

    Ten Centuries

    Points: 1205

    Beggin cap in hand again. Is there a way of altering the code below to just return single results for Earners.feeBudFee as this part of the query is returning multiple lines when it should just really be one entry

    Thanks

    Chris

    SELECT

    Bledger.BledgerId

    ,Bledger.PostingId AS [Bledger PostingId]

    ,Bledger.ProjectId

    ,Bledger.BledgerWorkingVatDisbTot

    ,Bledger.BledgerWorkingNVatDisbTot

    ,Bledger.BledgerWorkingProfit

    ,Bledger.BledgerWorkingVatAmount

    ,Bledger.BledgerWorkingBillAmount

    ,Bledger.VatRateId

    ,Bledger.BledgerPrinted

    ,Bledger.orgId

    ,Bledger.deptID

    ,Bledger.BledgerMasterVatDisbTot

    ,Bledger.BledgerMasterNVatDisbTot

    ,Bledger.BledgerMasterProfit

    ,Bledger.BledgerMasterVatAmount

    ,Bledger.BledgerMasterBillAmount

    ,Bledger.BledgerWorkingCurrencyId

    ,Bledger.BledgerMasterCurrencyId

    ,Bledger.BledgerExchangePerc

    ,Bledger.WorkTypeID

    ,Bledger.BledgerIsAbatement

    ,Bledger.BledgerMasterWipCostBilled

    ,Bledger.BledgerWorkingWipCostBilled

    ,Bledger.BledgerMasterWipChargeBilled

    ,Bledger.BledgerWorkingWipChargeBilled

    ,Bledger.MemberId AS [Bledger MemberId]

    ,Bledger.BledgerLegalAided

    ,Bledger.SystemType

    ,Bledger.AddressTypeId

    ,Postings.PostingId AS [Postings PostingId]

    ,Postings.PostingTypeId

    ,Postings.PostingCancelledId

    ,Postings.PostingGroupId

    ,Earners.MemberId AS [Earners MemberId]

    ,Earners.feeRef

    ,Earners.feeBudFee (Need to return distinct result)

    ,PostingDetails.PostingDetailsId

    ,PostingDetails.PostingId AS [PostingDetails PostingId]

    ,PostingDetails.PostingDetailsRef

    ,PostingDetails.PostingDetailsDescription

    ,PostingDetails.PostingDetailsDate

    ,PostingDetails.PeriodId AS [PostingDetails PeriodId]

    ,PostingDetails.PostingDetailsDaybookNumber

    ,Periods.PeriodId AS [Periods PeriodId]

    ,Periods.PeriodRef

    ,Periods.PeriodStartDate

    ,Periods.PeriodEndDate

    ,Periods.FinancialYearsId

    ,Periods.LastMonthEnd

    ,SplitBills.SplitBillsID

    ,SplitBills.PostingID AS [SplitBills PostingID]

    ,SplitBills.MemberId AS [SplitBills MemberId]

    ,SplitBills.SplitBillsWorkingBilledAmount

    ,SplitBills.SplitBillsWorkingSplitAmount

    ,SplitBills.SplitBillsWorkingWipCost

    ,SplitBills.SplitBillsWorkingWipCharge

    ,SplitBills.SplitBillsWorkingProfitAmount

    ,SplitBills.SplitBillsWorkingCurrencyID

    ,SplitBills.SplitBillsMasterBilledAmount

    ,SplitBills.SplitBillsMasterSplitAmount

    ,SplitBills.SplitBillsMasterWipCost

    ,SplitBills.SplitBillsMasterWipCharge

    ,SplitBills.SplitBillsMasterProfitAmount

    ,SplitBills.SplitBillsMasterCurrencyID

    ,SplitBills.SplitBillsExchangePerc

    ,SplitBills.SplitBillsPerc

    ,SplitBills.FeeSplitTypeId

    ,SplitBills.SplitBillsTimeElapsed

    FROM

    PostingDetails

    INNER JOIN Periods

    ON PostingDetails.PeriodId = Periods.PeriodId

    INNER JOIN Postings

    ON PostingDetails.PostingId = Postings.PostingId

    INNER JOIN Bledger

    ON Postings.PostingId = Bledger.PostingId

    INNER JOIN Earners

    ON Bledger.MemberId = Earners.MemberId

    INNER JOIN SplitBills

    ON Bledger.PostingId = SplitBills.PostingID AND Earners.MemberId = SplitBills.MemberId

  • David Burrows

    SSC Guru

    Points: 64392

    How would you identify which Earners.feeBudFee to select out of the many?

    Far away is close at hand in the images of elsewhere.
    Anon.

  • cmw 66135

    Ten Centuries

    Points: 1205

    I'm guessing on each change in value on Earners.feeBudFee or for each Earners.feeRef possibly?

    At the moment the above code is pulling back multiple values which I have filtered to only show one value but when I sum the column instead of getting a value of say £2 million I am getting £124 Million, which is obvious as when I look at the data the query returns I am getting multiple entries on the Earners.feeBudFee column. All the research I can see says clear the data up at sql level, what I cant find is how to do it when I have so many tables selected.

  • David Burrows

    SSC Guru

    Points: 64392

    I think your problem is not the multiple values of feeBufFee but the repeated values due to multiple rows from the other joins.

    If you change the Earners join to

    JOIN	(
    SELECT e.MemberId AS [Earners MemberId],SUM(e.feeBudFee)
    FROM Earners e
    GROUP BY e.MemberId
    ) Earners ([Earners MemberId],feeBudFee)
    ON Earners.MemberId = Bledger.MemberId

    You will get a single total per member

    If the other joins (excluding Earners) result in multiple rows per member then this will still give you incorrect grand total

    • This reply was modified 2 days, 16 hours ago by  David Burrows. Reason: Fix alias name

    Far away is close at hand in the images of elsewhere.
    Anon.

  • cmw 66135

    Ten Centuries

    Points: 1205

    That gave me the following error. The other grand totals seem to work out ok which is why I assumed it would just be this part of the query I needed to sort

    As usual thank you for your help David

    TITLE: Microsoft SQL Server Report Builder

    ------------------------------

    An error occurred while executing the query.

    The multi-part identifier "Earners.feeBudFee" could not be bound.

    Invalid column name 'MemberId'.

    Invalid column name 'MemberId'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'MemberId'.

    Invalid column name 'feeRef'.

    ------------------------------

    ADDITIONAL INFORMATION:

    The multi-part identifier "Earners.feeBudFee" could not be bound.

    Invalid column name 'MemberId'.

    Invalid column name 'MemberId'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'feeRef'.

    Invalid column name 'MemberId'.

    Invalid column name 'feeRef'. (Microsoft SQL Server, Error: 4104)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.6024&EvtSrc=MSSQLServer&EvtID=4104&LinkId=20476

     

  • David Burrows

    SSC Guru

    Points: 64392

    Sorry my bad, I have fixed my post.

    You will have to remove feeRef from the query to prevent the other errors.

    If you require feeRef then unless there is only one feeRef per member you will always get duplicates from the Earners table.

    If you remove Earners (table and columns from the report) do you get one row per MemberID?

    Far away is close at hand in the images of elsewhere.
    Anon.

  • cmw 66135

    Ten Centuries

    Points: 1205

    Tried again and still didn't work.

    Think I will abandon this for today and possibly take a different approach.

    Thank you for all your help with this

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

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