sql server - SQL - Selecting Records with an odd number of a given attribute -

sql server - SQL - Selecting Records with an odd number of a given attribute -

i'm brushing on sql - in other words, i'm really rusty - , bit stuck @ moment. it's trivial, we'll see.

i'd select people possess odd number of attribute isn't integer ( in example, transactiontype). so, example, take next test/not real info these people buying auto or big purchase.

name transactiontype date john purchase 5/1 john cancel 5/1 john purchase 5/2 joseph purchase 5/25 joseph cancel 5/25 tanya purchase 5/28

i homecoming people had odd number of transactions; in other words, ended purchasing item. so, in case, john , tanya selected , joseph not.

i know can utilize modulus operand here, i'm bit lost how utilize correctly. thought of using

count(transactiontype) % 2 != 0

in clause that's no-go. pointers in right direction helpful. allow me know if unclear, , thanks!

wouldn't improve off getting latest status transaction date , using rather relying on counting transactiontype determine latest status:

something this:

select b.name, b.transactiontype, b.[date] ( select name, max(t1.[date]) latestdate [transactions] t1 grouping t1.name ) inner bring together [transactions] b on b.name = a.name , a.latestdate = b.[date] b.transactiontype = 'buy'

assuming dates valid dates times included, should work.

sample sql fiddle

if store date portion max date same people buy , cancel on same date, hence homecoming more info , wrong records.

sql sql-server modulus