Duplicate Values 1

[Khanson82]

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?

 

[markros]

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

 

[Khanson82]

2005 Management Studio Express

 

[markros]

It actually doesn't change my answer, I was just thinking that perhaps we can move some logic to CTEs.

PluralSight Learning Library