10-05-2018, 09:42 PM
my table look like
i have written query
need suggestion .
Code:
Id time_stamp
101 2018-05-23 10:42:03
102 2018-05-24 11:42:03
103 2018-05-24 15:42:03
104 2018-05-25 15:42:03
105 2018-05-26 10:42:03
106 2018-05-26 15:42:03
107 2018-05-27 15:42:03
Code:
output should be
Date session
FN AN count
23-05-2018 1 0 1
24-05-2018 1 1 2
25-05-2018 0 1 1
26-05-2018 1 1 2
27-05-2018 0 1 1
Code:
SELECT date(time_stamp) as time_stamp,
COUNT( CONCAT(DATE(time_stamp),(CASE WHEN TIME(time_stamp) < '12:00:00' THEN 1 END))) AS FN,
COUNT( CONCAT(DATE(time_stamp),(CASE WHEN TIME(time_stamp) >= '12:00:00' THEN 1 END))) AS AN,
count(date(time_stamp) ) as count
FROM master_table
need suggestion .