Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Duplicate Values 1

Status
Not open for further replies.

Khanson82

MIS
Joined
Mar 5, 2010
Messages
85
Location
US
I have this below and it works fine except the calculations on the last step due to duplication in the views..I will describe below.

SELECT
I3C.dbo.vwrptPI.customerdesc as Client,
I3S.dbo.PA.App_Date as Date,
I3C.dbo.vwrptPI.masExamNum as ID,
I3C.dbo.vwrptPI.FName,
I3C.dbo.vwrptPI.LName,
I3C.dbo.vwrptPI.county as County,
I3C.dbo.vwrptPI.State,
I3S.dbo.PA.Available + I3S.dbo.PA.Scheduled AS Available,
I3S.dbo.PA.Available AS Unused,
sum(Q.dbo.vwAMZC.zip) as Members

FROM I3S.dbo.PA
INNER JOIN I3C.dbo.vwrptPI with(nolock)
ON (I3C.dbo.vwrptPI.masexamnum = I3S.dbo.PA.masexamnum)
LEFT OUTER JOIN I3C.dbo.vwrptPC with(nolock)
ON (I3C.dbo.vwrptPI.masexamnum = I3C.dbo.vwrptPC.id)
and (I3C.dbo.vwrptPI.customerdesc = I3C.dbo.vwrptPC.clientdetail)
LEFT OUTER JOIN Q.dbo.vwAMZC with(nolock)
ON (I3C.dbo.vwrptPC.clientdetail = Q.dbo.vwAMZC.client)
and (Q.dbo.vwAMZC.zipcode = I3C.dbo.vwrptPC.zip)

WHERE I3S.dbo.PA.App_Date >= getdate()

Group BY
I3C.dbo.vwrptPI.customerdesc,
I3S.dbo.PA.App_Date,
I3C.dbo.vwrptPI.masExamNum,
I3C.dbo.vwrptPI.FName,
I3C.dbo.vwrptPI.LName,
I3C.dbo.vwrptPI.county,
I3C.dbo.vwrptPI.State,
I3S.dbo.PA.Available + I3S.dbo.PA.Scheduled,
I3S.dbo.PA.Available
-----------------------------------------------------------
In (I3S.dbo.PA, there is no duplication)
In (I3C.dbo.vwrptPC, it lists the people by zip code they cover and if they service more than one client it lists all this information as many times as there are clients)
In (Q.dbo.vwAMZC,there is no Duplication)
In (I3C.dbo.vwrptPI, there is duplication of the
information for every client they service)

So somewhere when using (I3C.dbo.vwrptPI & I3C.dbo.vwrptPC I get the final result multiplied by upwards of 8 times for some clients.)Any ideas?

 
What is your SQL Server version?

To avoid such problem, do it this way:

select MainTable.Fields inner join (select GroupFld, sum(SomeValue) as TotalValue from Child1 group by GroupFld) C1 on MainTable.GroupFld = C1.GroupFld
LEFT JOIN (select GrpFld, sum(something) as AnotherTotal from SecondChild group by GrpFld) S on MainTable.GrpFld = S.GrpFld

--------
This way you'll avoid the problem and also would not need to include GROUP BY...

PluralSight Learning Library
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top