Hi I have a table with 2350k records I have the following query
SELECT Name ,
count(CASE WHEN P_A = ‘Y’ and CC = ‘Y’ THEN Name END),
count(CASE WHEN P_A = ‘Y’ and CC = ‘Y’ and flag = ‘1’ THEN Name END),
count(CASE WHEN P_A = ‘Y’ and CC = ‘Y’ and flag = ‘0’ and Score >= 76 and Score < 300 THEN Name END),
count(CASE WHEN P_A = ‘Y’ and CC = ‘Y’ and flag = ‘0’ and Score >= 300 and Score <= 330 THEN Name END)
FROM Table1
WHERE IF($P{StartDate} = $P{EndDate} ,Date_from_Table like CONCAT(DATE_FORMAT(IF(length($P{SelectDate}) > 4,$P{SelectDate},curdate()), ‘%m%d%Y’),“%”), date_format(str_to_date(CONCAT(SUBSTRING(Date_from_Table,1,2 ), “-”,LOWER(SUBSTRING(Date_from_Table,4,3)),“-”,‘20’,SUBSTRING(Date_from_Table,8,2)), “%d-%b-%Y”), “%Y-%m-%d”) BETWEEN IF(length($P{StartDate}) > 4,$P{StartDate},curdate()) AND IF(length($P{EndDate}) > 4,$P{EndDate},curdate()))
GROUP BY ClientName;
Now The first part of the IF condition works quit fast on its own like .15 sec but when I extend it to consider a range of Date It takes about a minute or so.
Date_from_Table unfortunately is in string format and I cannot at the moment alter the Table.
IF(length($P{SelectDate}) > 4,$P{SelectDate},curdate()) part is for if no date is specified
Also I would like to get the total of the columns . Can I do that??