September 3, 2013 at 3:14 am
Hi there,
I have two super queries below that go off and for a 'Job Ref' pulls back the 'Number of Appointments' and another one that pulls back the 'Number of times a job is Carded'
As it is a LEFT OUTER JOIN I still get results if the result is a NULL value. What I want to do is for a NULL value is return a '0' instead. The reason for doing this is later on in the script I want to do a calculation that takes away ''Number Times Carded' from the 'Number of Appointments' . If either the colums are Null it messes up my figures as the result will come back as Null.
So I'm using COALESCE in my sub queries but not sure I'm doing it right as I'm still getting Nulls in my results.
SELECT DISTINCTjob.[ref] AS 'Job Ref'
, APP.APPOINT AS 'Number of Appointments'
, CARDED.Carded AS 'Number Times Carded'
FROM dbo.Job AS JOB
LEFT OUTER JOIN
(SELECT JOB_ID, COUNT(COALESCE(JOB_ID,'0')) AS 'Carded'
FROM [dbo].[JobEvent]
WHERE Description LIKE '%Carded%'
AND EVENT = 'Additional Appointment Required'
GROUP BY job_id
) AS CARDED
ON JOB.ID = CARDED.job_id
LEFT OUTER JOIN
(SELECT job_id, COUNT(COALESCE(job_id,'0')) AS 'APPOINT'
FROM Appointment
WHERE status = 'Complete'
GROUP BY job_id
) AS APP
ON JOB.ID = APP.job_id
WHERE (JOB.status_id NOT IN ('0', '5', '6', '1', '2'))
AND (JOB.actual_enddate >= CONVERT(DATETIME, '2013-04-01 00:00:00', 102))
AND(JOB.actual_enddate <= CONVERT(DATETIME, '2014-03-31 00:00:00', 102))
September 3, 2013 at 3:43 am
I've cracked it guys.....
SELECT DISTINCTjob.[ref] AS 'Job Ref'
, COALESCE(APP.APPOINT,'0') AS 'Number of Appointments'
, COALESCE(CARDED.Carded,'0') AS 'Number Times Carded'
, COALESCE(APP.APPOINT,'0') - COALESCE(CARDED.Carded,'0') AS 'APP'
FROM dbo.Job AS JOB
LEFT OUTER JOIN
(SELECT JOB_ID, COUNT(JOB_ID) AS 'Carded'
FROM [dbo].[JobEvent]
WHERE Description LIKE '%Carded%' AND EVENT = 'Additional Appointment Required'
GROUP BY job_id) AS CARDED ON JOB.ID = CARDED.job_id
LEFT OUTER JOIN
(SELECT job_id, COUNT(job_id) AS APPOINT
FROM Appointment
WHERE status = 'Complete'
GROUP BY job_id) AS APP ON JOB.ID = APP.job_id
WHERE (JOB.status_id NOT IN ('0', '5', '6', '1', '2'))
AND (JOB.actual_enddate >= CONVERT(DATETIME, '2013-04-01 00:00:00', 102))
AND(JOB.actual_enddate <= CONVERT(DATETIME, '2014-03-31 00:00:00', 102))
September 3, 2013 at 3:45 am
Put the fileds in the outer query inside CASE statements:
SELECT ....
, CASE
WHEN APP.APPOINT IS NULL
THEN 0
ELSE APP.APPOINT
END AS 'Number of Appointments'
, CASE
WHEN CARDED.Carded IS NULL
THEN 0
ELSE CARDED.Carded
END AS 'Number Times Carded'
FROM (...
September 3, 2013 at 3:49 am
Ryan Keast (9/3/2013)
I've cracked it guys.....
Because you perform a calcultation with the values out of the COALESCE I suggest you set a numeric value as second parameter instead of a string value (use: "COALESCE (APP.APPOINT, 0)" instead of "COALESCE (APP.APPOINT, '0')" ). This will prevent some implicit cenversions.
September 3, 2013 at 4:34 am
Thanks
September 3, 2013 at 4:34 am
HanShi (9/3/2013)
Ryan Keast (9/3/2013)
I've cracked it guys.....Because you perform a calcultation with the values out of the COALESCE I suggest you set a numeric value as second parameter instead of a string value (use: "COALESCE (APP.APPOINT, 0)" instead of "COALESCE (APP.APPOINT, '0')" ). This will prevent some implicit cenversions.
Thanks I have made those changes
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply