...

View Full Version : Selecting Records Grater Than 7 Days



Kal
08-02-2011, 02:22 PM
Hi,

I have the following query which works great, however I would like to extend so that I can add extra column which shows me a SUM of records older than 7 days.




SELECT
JobWorkBranchId,
ContactBranchText,
SUM(IF(JobStatusCode='AA',1,0)),
SUM(IF(JobStatusCode='AB',1,0)),
SUM(IF((JobStatusCode IN ('BA', 'BB') AND CAST(JobAgreedDate AS DATE)<=DATE_SUB('2011/08/02',INTERVAL 0 DAY) AND CAST(JobAgreedDate AS DATE)>DATE_SUB('2011/08/02',INTERVAL 1 DAY)),1,0)),
SUM(IF((JobStatusCode IN ('BA', 'BB') AND CAST(JobAgreedDate AS DATE)<=DATE_SUB('2011/08/02',INTERVAL 1 DAY) AND CAST(JobAgreedDate AS DATE)>DATE_SUB('2011/08/02',INTERVAL 2 DAY)),1,0)),
SUM(IF((JobStatusCode IN ('BA', 'BB') AND CAST(JobAgreedDate AS DATE)<=DATE_SUB('2011/08/02',INTERVAL 2 DAY) AND CAST(JobAgreedDate AS DATE)>DATE_SUB('2011/08/02',INTERVAL 3 DAY)),1,0)),
SUM(IF((JobStatusCode IN ('BA', 'BB') AND CAST(JobAgreedDate AS DATE)<=DATE_SUB('2011/08/02',INTERVAL 3 DAY) AND CAST(JobAgreedDate AS DATE)>DATE_SUB('2011/08/02',INTERVAL 4 DAY)),1,0)),
SUM(IF((JobStatusCode IN ('BA', 'BB') AND CAST(JobAgreedDate AS DATE)<=DATE_SUB('2011/08/02',INTERVAL 4 DAY) AND CAST(JobAgreedDate AS DATE)>DATE_SUB('2011/08/02',INTERVAL 5 DAY)),1,0)),
SUM(IF((JobStatusCode IN ('BA', 'BB') AND CAST(JobAgreedDate AS DATE)<=DATE_SUB('2011/08/02',INTERVAL 5 DAY) AND CAST(JobAgreedDate AS DATE)>DATE_SUB('2011/08/02',INTERVAL 6 DAY)),1,0)),
SUM(IF((JobStatusCode IN ('BA', 'BB') AND CAST(JobAgreedDate AS DATE)<=DATE_SUB('2011/08/02',INTERVAL 6 DAY) AND CAST(JobAgreedDate AS DATE)>DATE_SUB('2011/08/02',INTERVAL 7 DAY)),1,0))
FROM
jobs
JOIN
contacts ON ContactId = JobWorkBranchId
WHERE
JobAssignedOwnerId = 22737
AND
JobStatusCode NOT IN ('DA', 'YA', 'ZA')
AND
((CAST(JobAgreedDate AS DATE)>=DATE_SUB('2011/08/02',INTERVAL 7 DAY) AND CAST(JobAgreedDate AS DATE)<='2011/08/02') OR CAST(JobAgreedDate AS DATE) IS NULL)
GROUP BY
JobWorkBranchId
ORDER BY
ContactBranchText



Any help would be great.

Thanks



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum