I have the following columns in Form A. They record user fingerprint “transactions” every time they register or check out from the building.
CREATE TABLE user_transactions(
id serial PRIMARY KEY,
staff_id INT4,
transaction_time TIMESTAMP,
transaction_type INT4
);
A user can have many transactions in one day. How to create a view with the following structure?
staff_id INT4
transaction_date DATE
first_transaction TIMESTAMP --first finger scan of the day
last_transaction TIMESTAMP --last finger scan of the day
number_of_transaction INT4 --how many times did the user scan for the day
this should Work done:
create or replace view xxx as
select
staff_id,
date_trunc('day', transaction_time) transaction_date,
min(transaction_time) first_transaction,
max(transaction_time) last_transaction,
count(*)
from user_transactions
group by staff_id, date_trunc('day', transaction_time);
I have the following columns in Form A, which record the user’s fingerprint “transaction” every time they register or check out from the building.
CREATE TABLE user_transactions(
id serial PRIMARY KEY,
staff_id INT4,
transaction_time TIMESTAMP,
transaction_type INT4
) ;
A user can have many transactions in one day. How to create a structure with the following View?
staff_id INT4
transaction_date DATE
first_transaction TIMESTAMP --first finger scan of the day
last_transaction TIMESTAMP --last finger scan of the day
number_of_transaction INT4 --how many times did the user scan for the day
This should be done:
< p>
create or replace view xxx as
select
staff_id,
date_trunc('day', transaction_time) transaction_date,
min(transaction_time) first_transaction,
max(transaction_time) last_transaction,
count(*)
from user_transactions
group by staff_id, date_trunc('day', transaction_time);
< p>