Query (sproc) duping cash & credits txn''s...anyone know why?
-
Anyone know why this query is duping cash & credit txn's? I can't figure it out. Thanks for your help!
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Abanco_RemittanceReportData
(
@TicketAgentID INT,
@StartDate DATETIME,
@EndDate DATETIME
)
AS
SELECT
sr.Number AS ScheduledRunNumber,
SUM( t.CashAmt ) AS Cash,
SUM( t.CreditAmt ) AS Credit
FROM
transactions t
INNER JOIN transactionitems ti ON t.TransactionID = ti.TransactionID
INNER JOIN tickets ON ti.TransactionItemID = tickets.TransactionItemID
LEFT JOIN ScheduledRuns sr ON tickets.ScheduledRunID = sr.ScheduledRunID
WHERE
t.TicketAgentID = @TicketAgentID AND tickets.SaleLocationID IN( 7, 8 )
AND
( ( t.CashAmt <> 0 AND t.CreditAmt = 0 ) OR ( t.CashAmt = 0 AND t.CreditAmt <> 0 ) )
AND
(FLOOR(CAST(t.TransactionDate AS DECIMAL(38, 19))) BETWEEN FLOOR(CAST(@StartDate AS DECIMAL(38, 19))) AND FLOOR(CAST(@EndDate AS DECIMAL(38, 19))))
GROUP BY sr.Number
ORDER BY sr.Number ASC
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
It would have to be because somewhere in the joins, your causing the rows to be duplicated. Without knowing the data, and structure, I couldn't say for sure, but that is a symptom that's common to that scenario.
The quickest way to tell, is to take your query, remove the groups and sums, and examine the rows going into it, once the joins are performed. You would see it immediately
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply