Duplicate results in query

Question

I’m having an issue with a specific SQL Server 2016 query reporting duplicate lines and I’m unsure how to best eliminate it without messing up the data. Please excuse my amateur query writing skills Below is the query. The field that I am having issues with is the [Tender Type] field. The transaction can have multiple tenders. For example: if you buy something at the store and pay half in cash and half with credit card, it will write two rows in the DT_Payment table. Ideally, if this occurred, the [Tender Type] would display something like “Multiple”, but I would be happy even if it only displayed the first [Tender Type]. I would appreciate any advice you could provide.

--Return Detail
declare @StoreName varchar(50);
declare @StartDate varchar(50);
declare @EndDate varchar(50);

set @StoreName = '8582 Brevard'
set @StartDate = '03/13/2020'
set @EndDate = '03/13/2020'

select 
       s.name as [Store],
       concat(sf.staff_code, ' - ',sf.Firstname,' ',sf.lastname) as [Associate],
       dtp.cais as [Terminal],
       dtp.transnum as [Trans #],
       P.Product_code as [SKU],
       PN.Short_name as [Description],
       dtp.qty as [Qty],
       dtp.price_sold as [Amt (Ea.)],
       concat(v.VIPCode, ' - ',v.VIPGName,' ',v.VIPName) as [Customer],
       ISNULL(DTPy.tender_code, 'Exchange') as [Tender Type],
       cast(dtp.TS_ID as date) as [Date]
from 
       DT_product DTP 
       LEFT JOIN store s on s.store_code_id = DTP.STORE_CODE_ID
       LEFT JOIN RETAIL_TRANSACTION rt on 
             rt.cais = dtp.Cais 
             and rt.STORE_CODE_ID = dtp.STORE_CODE_ID 
             and rt.TRANSNUM = dtp.Transnum
             and rt.TRANSTYPE = dtp.TRANSTYPE
       LEFT JOIN Staff sf on sf.staff_id = rt.OPERATOR_ID
       LEFT JOIN PRODUCT_NAME PN on PN.Product_ID = DTP.PRODUCT_ID
       LEFT JOIN Product P on P.Product_ID = DTP.Product_ID
       LEFT JOIN VIP v on v.VIPCode_id = rt.VIPCODE_ID
       LEFT JOIN DT_Payment DTPy on 
             DTPy.cais = dtp.Cais 
             and DTPy.STORE_CODE_ID = dtp.RETURN_STORE_ID 
             and DTPy.TRANSNUM = dtp.Transnum
             and DTPy.TRANSTYPE = dtp.TRANSTYPE
where 
       DTP.qty < 0
       and DTP.TRANSTYPE = 'SALE'
       and s.name = @StoreName
       AND format(cast(rt.transdate as date), 'MM/dd/yyyy') >= @StartDate
       and format(cast(rt.transdate as date), 'MM/dd/yyyy') <= @EndDate
Order by
       cast(dtp.ts_id as date),
       [Trans #]

Note - referencing any column from an outer joined table in the where clause logically turns that outer (i.e., left) join into an inner join. You refer to "transactions" and your column list appears to be focused on that table, yet that table is outer joined. A better approach is to start with your primary table of interest and then add the joins you need to generate the desired resultset. — SMor, Aug 05 '20 at 16:20
Does this answer your question? [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — SMor, Aug 05 '20 at 16:20
`AND format(cast(rt.transdate as date), 'MM/dd/yyyy')` is goign to ruin any chance of performance here. Why are you converting your datetimes to a `date` and then a `varchar`? Why is `@StartDate` and `@EndDate` a `varchar(50)` when they are clearly date and time values. Compare date and time columns to a date and time variable. — Thom A, Aug 05 '20 at 16:23
And don't use format if you don't need to. Formatting of data should be the responsibility of the presentation layer; format is also an expensive operation. To go along with that, don't pass dates (or numbers or ...) as strings. Use the appropriate datatypes and you don't need to do this type of kludge (which won't work correctly once you have multiple years of data to be considered). — SMor, Aug 05 '20 at 16:23

Duplicate results in query

0 Answers0