How to do a boolean check in SQL if user has purchased an item before?

Transact-SQL
1

Hey guys, I am struggling how to write a query to do this.

I have a table that looks like this:

user date_purchased item price
John ‘2011-02-04’ SK2341 34.54
John ‘2012-01-04’ LS2414 94.98

I want to create a new table that looks like this, with the bought before using the bit value:

user item bought_before(T/F).

I want to populate this table with the user, and items and a boolean T/F based on the if user has bought the same item before. Based on the data I have, it should return False for both in the table above but I'm struggling on the statement to do this.

Any ideas?

2

try this

create table #tbl 
( 
   Myuser varchar(50),
   date_purchased date,
   item varchar(50),
   price numeric(5,2)
);

insert into #tbl 
values

('John' ,'2011-02-04', 'SK2341', 34.54),
('John','2012-01-04', 'LS2414', 94.98),
('Carls','2012-02-05', 'CS2425', 90.98),
('Carls','2012-03-07', 'CS2425', 96.98),
('Mark','2012-02-05', 'LS2425', 94.98),
('Mark','2012-03-05', 'LS2425', 94.98),
('Ana','2012-02-05', 'AS2425', 64.98),
('Ana','2012-03-05', 'AS2430', 94.98),
('Lisa','2012-02-05', 'MS2421', 34.92),
('Lisa','2012-03-05', 'MS2421', 24.93),
('Lisa','2012-04-05', 'MS2421', 4.94);



SELECT 
	t.myuser AS [user],t.item, 
    'flag'=case when ( ROW_NUMBER() OVER(PARTITION BY Myuser, item ORDER BY myuser))= 1 THEN 'F' ELSE 'T' END 
	 INTO #flagUsers
FROM #tbl as t;

SELECT * FROM #flagUsers;

the last select shows the result of the table created using the 'select into statement '

1 Like