I know this is very simple but for the life of me i cannot fix it.
I'm trying to find values with same trans_lgnnumber and different barcode.
select a.transt_lgnnumber,
b.transt_lgnnumber
from tbltrans_ticket a
join tbltrans_ticket b on b.transt_lgnnumber = a.transt_lgnnumber
where a.transt_lgnnumber <> b.transt_lgnnumber
and a.TransT_strBarcodeRedemp = b.TransT_strBarcodeRedemp
and a.TransT_dtmDateTime > '20170101'
and (a.TransT_strBarcodeRedemp is not null or a.TransT_strBarcodeRedemp <> '')
select a.transt_lgnnumber,
b.transt_lgnnumber
from tbltrans_ticket a
join tbltrans_ticket b on b.transt_lgnnumber = a.transt_lgnnumber
where a.transt_lgnnumber <> b.transt_lgnnumber
and (a.TransT_strBarcodeRedemp = b.TransT_strBarcodeRedemp
OR (a.TransT_strBarcodeRedemp IS NULL AND b.TransT_strBarcodeRedemp IS NOT NULL)
OR (a.TransT_strBarcodeRedemp IS NOT NULL AND b.TransT_strBarcodeRedemp IS NULL))
and a.TransT_dtmDateTime > '20170101';
The issue here is that I am getting transaction numbers that may have 2 same transaction numbers so they are duplicated, sorry I missed that.
So i can get this that is duplicate:
120091, 9XX40000000000000007
120091, 9XX40000000000000007
Can i remove those transactions?
select a.transt_lgnnumber,
b.transt_lgnnumber
from tbltrans_ticket a
join tbltrans_ticket b on b.transt_lgnnumber = a.transt_lgnnumber
where a.transt_lgnnumber = b.transt_lgnnumber
and (a.TransT_strBarcodeRedemp <> b.TransT_strBarcodeRedemp )
OR (a.TransT_strBarcodeRedemp IS NULL AND b.TransT_strBarcodeRedemp IS NOT NULL)
OR (a.TransT_strBarcodeRedemp IS NOT NULL AND b.TransT_strBarcodeRedemp IS NULL)
and a.TransT_dtmDateTime > '20170101'
group by a.transt_lgnnumber,
b.transt_lgnnumber
order by a.transt_lgnnumber
;
thanks
Have in mind that transt_lgnnumber can appear more than once in the same number as it is multiple transactions with the same number
Your code is missing a set of parenthesis around the or section.
where a.transt_lgnnumber = b.transt_lgnnumber
and ( (a.TransT_strBarcodeRedemp <> b.TransT_strBarcodeRedemp )
OR (a.TransT_strBarcodeRedemp IS NULL AND b.TransT_strBarcodeRedemp IS NOT NULL)
OR (a.TransT_strBarcodeRedemp IS NOT NULL AND b.TransT_strBarcodeRedemp IS NULL) )
and a.TransT_dtmDateTime > '20170101'
select a.transt_lgnnumber,
b.transt_lgnnumber,
a.TransT_strBarcodeRedemp
from tbltrans_ticket a
join tbltrans_ticket b on b.transt_lgnnumber = a.transt_lgnnumber
where a.transt_lgnnumber = b.transt_lgnnumber
and ( (a.TransT_strBarcodeRedemp <> b.TransT_strBarcodeRedemp )
OR (a.TransT_strBarcodeRedemp IS NULL AND b.TransT_strBarcodeRedemp IS NOT NULL)
OR (a.TransT_strBarcodeRedemp IS NOT NULL AND b.TransT_strBarcodeRedemp IS NULL) )
and a.TransT_dtmDateTime > '20170101';
When i then do select * from tbltrans_ticket where TransT_strBarcodeRedemp = 'THEBARCODES' i get barcodes that have been used in a transaction that have 2 same transaction number. As described above.
OK.
So As I couldn't find a solution I went medieval on this one
select transt_lgnnumber,TransT_strBarcodeRedemp
into #gt
from tbltrans_ticket where TransT_strBarcodeRedemp is not null
and TransT_strBarcodeRedemp <> ''
-- and TransT_dtmDateTime > '20170101'
group by transt_lgnnumber,TransT_strBarcodeRedemp
SELECT G.TransT_strBarcodeRedemp
into #gtN
FROM #gt G
GROUP BY G.TransT_strBarcodeRedemp
HAVING ( COUNT(G.transt_lgnnumber) > 1 )
select T.transt_lgnnumber,G.TransT_strBarcodeRedemp from #gtn G
inner join tbltrans_ticket T on G.TransT_strBarcodeRedemp = T.TransT_strBarcodeRedemp
order by T.transt_lgnnumber
drop table #gt
drop table #gtn