I'd like to be able to output the following fields from the query below:
AddedById,AddedByName,HoursWorked,CurrentYearlyFlexiAvailable
However where I have WHERE addedby=1, I'd like to replace this with the field name AddedById as I do not want to hard code this value as the overall query should and will return more than one person, I want to get this value from the rpt_timesheet_data view, which is there. The CurrentYearlyFlexiAvailable field should be telling me how much time they have left for the current year to date by doing the calculation between the SELECT SUM(ttl) and from the FROM (SELECT SUM(worked)-420 as ttl
SELECT AddedById,AddedByName,SUM(HoursWorked) AS HoursWorked
,(SELECT SUM(ttl) - (
SELECT SUM(worked)
FROM vwtimesheet
WHERE addedby=AddedById
AND entrydate BETWEEN '2017-01-01' AND '2017-04-13'
AND activityid=3192
GROUP BY addedby ) AS flexihours
FROM (
SELECT SUM(worked)-420 AS ttl
FROM vwtimesheet
WHERE addedby=1 <!--HERE IS THE ISSUE
AND entrydate BETWEEN '2017-01-01' AND '2017-04-13'
AND projectid<>113 AND activityid<>3192
GROUP BY entrydate
HAVING SUM(worked)>420
) AS s) AS CurrentYearlyFlexiAvailable
FROM rpt_timesheet_data
WHERE entrydate BETWEEN '2017-04-02' AND '2017-04-13 23:59:59'
AND ActivityId=3192
GROUP BY AddedById,AddedByName
ORDER BY AddedByName
but I keep getting:
Error Code: 1054. Unknown column 'AddedById' in 'where clause'
Just in that one location. I've tried various queries to sort this, but just cannot figure it out. Sorry not to good at explaining this, can see it in my head what I want to do...
Here is a query that does something very similar in that it returns the results for a single user, where as the one above is meant to loop through all users and give me the results:-
SELECT addedbyname, SUM(ttl) -
(SELECT SUM(worked)
FROM vwtimesheet
WHERE addedby=1
AND entrydate BETWEEN '2017-01-01' AND '2017-04-13'
AND activityid=3192
GROUP BY addedby ) AS CurrentYearlyFlexiAvailable
,(SELECT SUM(worked) FROM vwtimesheet
WHERE addedby=1
AND entrydate BETWEEN '2017-01-01' AND '2017-04-13'
AND activityid=3192
GROUP BY addedby ) AS flexiused
,(SELECT sum(worked) FROM vwtimesheet
WHERE addedby=1
AND entrydate BETWEEN DATE_FORMAT(NOW() ,'%Y-%m-01') AND curdate()
AND activityid=3192
GROUP BY addedby ) as fleximonthused
FROM ( SELECT entrydate,addedbyname,SUM(worked)-420 AS ttl FROM vwtimesheet
WHERE addedby=1
AND entrydate BETWEEN '2017-01-01' AND '2017-04-13'
AND projectid<>113
AND activityid<>3192
GROUP BY entrydate,addedbyname
HAVING SUM(worked)>420
) AS s
Please try the following...
SELECT AddedById,
AddedByName,
SUM( HoursWorked ) AS HoursWorked,
SUM( ttl ) - sumWorked AS CurrentYearlyFlexiAvailable
FROM ( SELECT AddedById AS AddedById,
AddedByName AS AddedByName
FROM rpt_timesheet_data
GROUP BY AddedById
) AS AddedByFinder
JOIN ( SELECT addedby AS addedby,
entrydate AS entrydate,
SUM( worked ) - 420 AS ttl
FROM vwtimesheet
WHERE entrydate BETWEEN '2017-01-01' AND '2017-04-13'
AND projectid <> 113
AND activityid <> 3192
GROUP BY addedby,
entrydate
HAVING SUM( worked ) > 420
) AS ttlFinder ON AddedByFinder.AddedById = ttlFinder.addedby
JOIN ( SELECT addedby AS addedby,
SUM( worked ) AS sumWorked
FROM vwtimesheet
WHERE entrydate BETWEEN '2017-01-01' AND '2017-04-13'
AND activityid = 3192
GROUP BY addedby
) sumWorkedFinder ON AddedByFinder.AddedById = sumWorkedFinder.addedby
WHERE entrydate BETWEEN '2017-04-02' AND '2017-04-13 23:59:59'
AND ActivityId = 3192
GROUP BY AddedById,
AddedByName
ORDER BY AddedByName;
(Explanation to follow...)
If you have any questions or comments, then please feel free to post a Comment accordingly.