SQL Query Syntax - Certain Rows Being Returned Multiple Times

I have an SQL store procedure that is being crazy for lack of better words. I doesn't seem to matter how I structure the joins, certain rows are being repeated a couple hundred times. Originally the stored procedure utilized all LEFT JOIN, however, I've been attempting to get this working by using a mixture of INNER and LEFT JOIN. Entries that are listed as HOURS will and an IndID, Expenses will have an IndID of -1.  The repeated issue you will observe the 425508 TimeCardID repeating.Please find attached the SP and screenshot.spr_payroll.txtScreen Capture
Trent AdamsApplication EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
With no tables available to do any testing we can only offer advice on how to proceed.

I suggest you "start small" and add one extra join after each run, until you locate the join that explodes the number of rows.

e.g. start with this:
SELECT
       COUNT(*)
     , COUNT(distinct tbl_Claims.ClaimID)                                                                                                                                          AS 'TimeCardID'
FROM tbl_Claims
-- Perform filtering
WHERE (@StartDate IS NULLc OR @EndDate IS NULL)
OR (tbl_Claims.Created >= @StartDate AND tbl_Claims.Created <= @EndDate)

Open in new window

Then this:
SELECT
       COUNT(*)
     , COUNT(distinct tbl_Claims.ClaimID)                                                                                                                                          AS 'TimeCardID'
FROM tbl_Claims
LEFT JOIN tbl_Users ON tbl_Users.UserID = tbl_Claims.ProvidedBy
-- Perform filtering
WHERE (@StartDate IS NULLc OR @EndDate IS NULL)
OR (tbl_Claims.Created >= @StartDate AND tbl_Claims.Created <= @EndDate)

Open in new window

Then this:
SELECT
       COUNT(*)
     , COUNT(distinct tbl_Claims.ClaimID)                                                                                                                                          AS 'TimeCardID'
FROM tbl_Claims
LEFT JOIN tbl_Users ON tbl_Users.UserID = tbl_Claims.ProvidedBy
--***12-15-2017***
INNER JOIN tbl_ClaimDetails ON tbl_ClaimDetails.ClaimDetailID = tbl_Claims.ClaimDetailID
-- Perform filtering
WHERE (@StartDate IS NULLc OR @EndDate IS NULL)
OR (tbl_Claims.Created >= @StartDate AND tbl_Claims.Created <= @EndDate)

Open in new window


keep adding the remaining, one at a time:
LEFT JOIN tbl_Individuals ON tbl_ClaimDetails.IndID = tbl_Individuals.IndID
--***12-15-2017***
LEFT JOIN tbl_Users approvedbyjoin ON tbl_ClaimDetails.ApprovedBy = approvedbyjoin.UserID
LEFT JOIN tbl_Events ON tbl_Events.EventID = tbl_Claims.InternalEventID
LEFT JOIN tbl_Events ted ON tbl_Events.EventID = tbl_Events.EventID
LEFT JOIN tbl_ExpenseType ON tbl_Claims.ExpenseTypeID = tbl_ExpenseType.ExpenseTypeID
LEFT JOIN tbl_Services ON tbl_Services.ServiceID = tbl_Claims.ServiceID

LEFT JOIN tbl_EmpPayRates ON tbl_EmpPayRates.RateUserID = tbl_Users.EmployeeID
      AND tbl_Claims.Created BETWEEN tbl_EmpPayRates.RateEffectiveDate AND tbl_EmpPayRates.RateEndDate

--Employee plus service override
LEFT JOIN tbl_EmpPlusEventOverride ON tbl_EmpPlusEventOverride.EPEOEmployeeID = tbl_Users.EmployeeID
      AND tbl_EmpPlusEventOverride.EPEOActive = 1
      AND tbl_EmpPlusEventOverride.EPEOEventID = tbl_Claims.InternalEventID
      AND tbl_Claims.Created BETWEEN tbl_EmpPlusEventOverride.EPEOEffectiveDate AND tbl_EmpPlusEventOverride.EPEOEndDate

--Employee plus service override
LEFT JOIN tbl_EmpPlusServiceOverride ON tbl_EmpPlusServiceOverride.SOEmpID = tbl_Users.EmployeeID
      AND tbl_EmpPlusServiceOverride.Active = 1
      AND tbl_EmpPlusServiceOverride.SOServiceID = tbl_Claims.ServiceID
      AND tbl_Claims.Created BETWEEN tbl_EmpPlusServiceOverride.SOEffectiveDate AND tbl_EmpPlusServiceOverride.SOEndDate

--Event overrides
LEFT JOIN tbl_EventOverrideRates ON tbl_EventOverrideRates.EOEventID = tbl_Claims.InternalEventID
      AND tbl_EventOverrideRates.EOActive = 1
-- Service overrides
LEFT JOIN tbl_ServiceOverrideRates ON tbl_ServiceOverrideRates.SOServiceID = tbl_Claims.ServiceID
      AND tbl_ServiceOverrideRates.Active = 1
--Get rate type descriptions
--Override rate types for employee plus events
LEFT JOIN tbl_OverrideRateTypes ON tbl_EmpPlusEventOverride.EPEORateType = tbl_OverrideRateTypes.RateTypeID
--Override rate types for employee plus service
LEFT JOIN tbl_OverrideRateTypes svcratetypes ON tbl_EmpPlusServiceOverride.SORateType = svcratetypes.RateTypeID
--Override  rate types for events only
LEFT JOIN tbl_OverrideRateTypes eventratetypes ON tbl_EventOverrideRates.EOType = eventratetypes.RateTypeID
--Override rate types for services only
LEFT JOIN tbl_OverrideRateTypes serviceratetypes ON tbl_ServiceOverrideRates.SORateType = serviceratetypes.RateTypeID

Open in new window


Once you see the counts increase (or decrease) unexpectedly, investigate what that extra table has done to the query so far. You may need to change to joining a subquery instead of the table (e.g. a subquery containing a group by).

I don't have any silver bullet for this type of discovery. If you are not really familiar with all of those tables it can be painstaking work.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
mmm, could it be this? seems strange

      Line 162:       LEFT JOIN tbl_Events ON tbl_Events.EventID = tbl_Claims.InternalEventID
      Line 163:       LEFT JOIN tbl_Events ted ON tbl_Events.EventID = tbl_Events.EventID
1
Trent AdamsApplication EngineerAuthor Commented:
You were absolutely correct. A co-worker of mine also suggested to pick a claim and add joins 1-by-1 until I find the error. Which when I took another look at these joins when performing the 1-by-1 I realized that someone other than myself had joined a table alias on itself which was the cause of the issue. Thank you for your assistance.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.