Help with SQL Statement
Posted by:
Paul Hives
Date: September 22, 2017 05:58PM
I have the following procedure which works fine:
CREATE DEFINER=`root`@`192.168.1.%` PROCEDURE `GetWeeklySched`(IN _mon varchar(10), IN _tue varchar(10), IN _wed varchar(10), IN _thu varchar(10), IN _fri varchar(10))
BEGIN
SELECT
CONCAT(WEBFSS.MEMBERS.LAST_NAME, ", " ,WEBFSS.MEMBERS.FIRST_NAME) As Name,
SUM(CASE WHEN WEBFSS.SCHEDULE.DATE = _mon THEN WEBFSS.SCHEDULE.QTY END) AS MEALS1,
SUM(CASE WHEN WEBFSS.SCHEDULE.DATE = _mon THEN WEBFSS.SCHEDULE.ENSURE END) AS ENS1,
SUM(CASE WHEN WEBFSS.SCHEDULE.DATE = _tue THEN WEBFSS.SCHEDULE.QTY END) AS MEALS2,
SUM(CASE WHEN WEBFSS.SCHEDULE.DATE = _tue THEN WEBFSS.SCHEDULE.ENSURE END) AS ENS2,
SUM(CASE WHEN WEBFSS.SCHEDULE.DATE = _wed THEN WEBFSS.SCHEDULE.QTY END) AS MEALS3,
SUM(CASE WHEN WEBFSS.SCHEDULE.DATE = _wed THEN WEBFSS.SCHEDULE.ENSURE END) AS ENS3,
SUM(CASE WHEN WEBFSS.SCHEDULE.DATE = _thu THEN WEBFSS.SCHEDULE.QTY END) AS MEALS4,
SUM(CASE WHEN WEBFSS.SCHEDULE.DATE = _thu THEN WEBFSS.SCHEDULE.ENSURE END) AS ENS4,
SUM(CASE WHEN WEBFSS.SCHEDULE.DATE = _fri THEN WEBFSS.SCHEDULE.QTY END) AS MEALS5,
SUM(CASE WHEN WEBFSS.SCHEDULE.DATE = _fri THEN WEBFSS.SCHEDULE.ENSURE END) AS ENS5
FROM WEBFSS.SCHEDULE
INNER JOIN WEBFSS.MEMBERS
ON WEBFSS.SCHEDULE.MEMBER_ID = WEBFSS.MEMBERS.MEMBER_ID
WHERE
(WEBFSS.SCHEDULE.DATE BETWEEN _mon AND _fri)
GROUP BY
WEBFSS.MEMBERS.LAST_NAME, WEBFSS.MEMBERS.FIRST_NAME ;
END
I also need the MEMBER_ID which is the key column and is an INT. When adding MEMBERS.MEMBER_ID to the select the query will not run.
I tried cast and convert but no results. I have searched but have not see any thing related to this.
I'm stumped.