Hi
Im having a hardtime making my query work the way it should be.i'm getting wrong output. basically what im trying to do is a simple user statistic that will show the user inputed data and user processed data base on the date range the user selected.
heres the screenshots for better visualization
and the query for that is.
first Get all the user from the user table
Select Name from user GROUP BY Name ORDER BY Name ASC
then i will loop inside the reservations table for the matching username base on the date range selected
select
SDate,
FoName,
COUNT( FoName ) AS 'opentrans'
From reservations
where SDate BETWEEN '" . $from . "' AND '" . $to. "'
AND FoName='$ngaran'
GROUP BY FoName ORDER BY FoName ASC
after getting all the username available on that date range. i will loop inside table trans_logs to get all the proceesed/served transactions on that date range
Select username,status,
SUM( IF( status = 'Served', 1, 0 ) ) Served
from trans_logs
where datetime
BETWEEN '" . $from . " 00:00' AND '" . $to. " 23:59'
and username='$ngaran' group by username order by username
I know that is not a great approach. but its the only closest thing in getting what i want. but i also tried using diffrent method the problem is that while im getting the correct result that i want i cant find a way to make the presentation of the data same as the original
what im getting using this approach is
and the query is
SELECT DISTINCT FoName, COUNT( FoName ) AS
opentrans
FROM reservations
WHERE SDate between
'" . $from . "' AND '" . $to. "'
GROUP BY FoName
UNION
SELECT DISTINCT username , COUNT(DISTINCT CASE WHEN status = 'Served' THEN 1 END) 'Served'
FROM trans_logs
WHERE datetime
BETWEEN '" . $from . " 00:00' AND '" . $to. " 23:59'
GROUP BY username
Any help?
