Select Distinct results from just one Table/column

Question

Post reply

Select Distinct results from just one Table/column

cmw 66135

Ten Centuries

Points: 1272
More actions
May 16, 2019 at 11:40 am

#3644156

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

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

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

David Burrows SSC Guru Points: 65142 More actions · Answer 1

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: 1272 More actions · Answer 2

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: 65142 More actions · Answer 3

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(
SELECTe.MemberId AS [Earners MemberId],SUM(e.feeBudFee) 
FROMEarners e
GROUPBY 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 5 years, 6 months 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: 1272 More actions · Answer 4

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 '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 '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: 65142 More actions · Answer 5

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: 1272 More actions · Answer 6

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