0

I have the following working query that gives me the "Used_LeaveTimeSum". enter image description here

SELECT     
SUM(lqHoursPL) AS Used_LeaveTimePL, 
SUM(lqHoursPB) AS Used_LeaveTimePB, 
SUM(lqHoursUL) AS Used_LeaveTimeUL, 
SUM(lqHoursFMLA) AS Used_LeaveTimeFMLA, 
COALESCE(SUM(lqHoursPL),0)+ COALESCE(SUM(lqHoursFMLA),0) AS Used_LeaveTimeSum

FROM      tblleaverequest
WHERE 
tblleaverequest.lqUser = 'jdoe' AND 
YEAR(tblleaverequest.lqStartDate) = YEAR(CURDATE())

Now I need to filter the sum to only "Approved records" so I changed the WHERE clause to:

WHERE 
lqApproved = "Y" AND
tblleaverequest.lqUser = 'jdoe' AND 
YEAR(tblleaverequest.lqStartDate) = YEAR(CURDATE())

Now all I get is all zero's? enter image description here

Here is the backend data: ("Used_LeaveTimeSum" should be 24) enter image description here

INSERT INTO tblleaverequest (lqID, lqUser, lqSupervisor, lqTotalHours, lqHoursPL, lqHoursPB, lqHoursUL, lqHoursFMLA, lqStartDate, lqEndDate, lqDescription, lqNotes, lqSubmitted, lqApproved, lqCreated, lqmodified, lqmodifiedBy) VALUES (392, 'jdoe', 'jsmith', 8, 8, 0, 0, 0, '2017-01-02', '2017-01-02', '* Testing - Please Disregard* Upd', NULL, 'Y', 'Y', '2017-01-05 16:13:08', '2017-01-12 09:00:29', 'jdoe'); INSERT INTO tblleaverequest (lqID, lqUser, lqSupervisor, lqTotalHours, lqHoursPL, lqHoursPB, lqHoursUL, lqHoursFMLA, lqStartDate, lqEndDate, lqDescription, lqNotes, lqSubmitted, lqApproved, lqCreated, lqmodified, lqmodifiedBy) VALUES (427, 'jdoe', 'jsmith', 8, 8, 0, 0, 0, '2017-01-12', '2017-01-12', '* Testing - Please Disregard* ', NULL, 'Y', 'Y', '2017-01-11 09:51:31', '2017-01-12 09:01:08', NULL); INSERT INTO tblleaverequest (lqID, lqUser, lqSupervisor, lqTotalHours, lqHoursPL, lqHoursPB, lqHoursUL, lqHoursFMLA, lqStartDate, lqEndDate, lqDescription, lqNotes, lqSubmitted, lqApproved, lqCreated, lqmodified, lqmodifiedBy) VALUES (428, 'jdoe', 'jsmith', -8, -8, 0, 0, 0, '2017-01-11', '2017-01-11', '* Testing - Please Disregard* ', NULL, 'Y', 'Y', '2017-01-11 09:56:02', '2017-01-12 09:00:33', NULL); INSERT INTO tblleaverequest (lqID, lqUser, lqSupervisor, lqTotalHours, lqHoursPL, lqHoursPB, lqHoursUL, lqHoursFMLA, lqStartDate, lqEndDate, lqDescription, lqNotes, lqSubmitted, lqApproved, lqCreated, lqmodified, lqmodifiedBy) VALUES (422, 'jdoe', 'jsmith', 8, 8, 0, 0, 0, '2017-01-11', '2017-01-11', '* Testing - Please Disregard* ', NULL, 'Y', 'Y', '2017-01-10 14:43:58', '2017-01-12 09:00:35', NULL); INSERT INTO tblleaverequest (lqID, lqUser, lqSupervisor, lqTotalHours, lqHoursPL, lqHoursPB, lqHoursUL, lqHoursFMLA, lqStartDate, lqEndDate, lqDescription, lqNotes, lqSubmitted, lqApproved, lqCreated, lqmodified, lqmodifiedBy) VALUES (423, 'jdoe', 'jsmith', 8, 8, 0, 0, 0, '2017-01-09', '2017-01-09', '* Testing - Please Disregard* ', NULL, 'Y', 'Y', '2017-01-10 14:51:25', '2017-01-12 09:04:56', NULL); INSERT INTO tblleaverequest (lqID, lqUser, lqSupervisor, lqTotalHours, lqHoursPL, lqHoursPB, lqHoursUL, lqHoursFMLA, lqStartDate, lqEndDate, lqDescription, lqNotes, lqSubmitted, lqApproved, lqCreated, lqmodified, lqmodifiedBy) VALUES (426, 'jdoe', 'jsmith', 8, 8, 0, 0, 0, '2017-01-11', '2017-01-11', '* Testing - Please Disregard* ', NULL, 'Y', 'N', '2017-01-11 09:42:04', '2017-01-11 09:42:26', NULL);

jlig
  • 241
  • 2
  • 12
  • 1
    Please provide data as formated text and not as a image.. – Raymond Nijland Jan 12 '17 at 15:18
  • Is it possible that in your data there is a space after the Y? – CptMisery Jan 12 '17 at 15:18
  • 1
    are you sure you have a column [lqUser] with values ['jdoe'] I can't see in the image – Mohammed Elshennawy Jan 12 '17 at 15:22
  • 1
    If that is really the actual data AND the only thing in the query that has changed is the addition of `lqApproved = "Y"`, then the value of `lqApproved` must be different than what you think it is .. Check the distinct values and sizes: `SELECT DISTINCT lqApproved, length(lqApproved) AS LenlqApproved FROM YourTable` – Leigh Jan 12 '17 at 15:33
  • 1
    Why is this question tagged as ColdFusion? – Brad Wood Jan 12 '17 at 15:36
  • Thanks for the responses: – jlig Jan 12 '17 at 16:18
  • 1
    There could be an issue with using the double quotes in the lqApproved = "Y" clause depending on the server settings. http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql – snackboy Jan 12 '17 at 16:20
  • Thanks again, - The "lqApproved" column is Datatype: VARCHAR, Length: 1 - I updated original post to include the raw data - I tried the query with single quotes, double quotes, no quotes.. no change? ps: This query is part of my CFQUERY dataset on my ColdFusion page. – jlig Jan 12 '17 at 16:40
  • Got it! I changed the = to LIKE and it started working? lqApproved LIKE "Y" Thanks for the help. – jlig Jan 12 '17 at 16:53

0 Answers0