If minus figure pull back 0
-
Hi,
Have the following in my SELECT Stataement
CASE WHEN com.completion_date IS NOT NULL AND dim.DayName <> 'Saturday'THEN DATEDIFF(d, com.current_task_target_date,com.completion_date) - non1.NoWorkDays
WHEN com.completion_date IS NOT NULL AND dim.DayName = 'Saturday'
THEN DATEDIFF(d, com.current_task_target_date,com.completion_date)
ELSE NULL
END AS 'DaysOverTarget'
Some of the figures coming back are minus figures. How could I get the minus figures reported to be 0.00?
Below is the full TSQL
SELECT DISTINCT com.comm_reference AS 'Referance'
,com.crt_date AS 'CreatedDate'
,com.current_task_target_date AS 'TargetDate'
,com.completion_date AS 'CompletionDate'
,CASE WHEN com.completion_date IS NOT NULL AND dim.DayName <> 'Saturday'
THEN DATEDIFF(d, com.crt_date,com.completion_date) - non.NoWorkDays
WHEN com.completion_date IS NOT NULL AND dim.Dayname = 'Saturday'
THEN DATEDIFF(d, com.crt_date,com.completion_date)
ELSE NULL
END AS 'DaysToCompletion'
,CASE WHEN com.completion_date IS NOT NULL AND dim.DayName <> 'Saturday'
THEN DATEDIFF(d, com.current_task_target_date,com.completion_date) - non1.NoWorkDays
WHEN com.completion_date IS NOT NULL AND dim.DayName = 'Saturday'
THEN DATEDIFF(d, com.current_task_target_date,com.completion_date)
ELSE NULL
END AS 'DaysOverTarget'
,CASE WHEN com.completion_date IS NOT NULL THEN 'Completed'
ELSE 'Outstanding'
END AS 'CallbackStatus'
,comt.comm_type_description AS 'CommunicationType'
,main.comm_description AS 'MainCode'
,com.decision_code 'DecisionCode'
,CONCAT(offi.ofcr_title,' ',offi.forename,' ',offi.surname) AS 'OfficerName'
,work.description AS 'WorkGroup'
,CONCAT(per.person_title,' ',per.surname) AS 'Originator'
,pla.address##1 AS 'AddressLine1'
,pla.post_code AS 'PostCode'
,dim.DayName
-- ,diary.[Callback Details] AS 'Callback Details'
--,ROW_NUMBER() OVER (PARTITION BY com.comm_reference ORDER BY diary.entry_date) 'LoggedNote'
FROM [dbo].[em_communication] as com
/* To work out the number of non working days between Logged Date and Completed Date*/
CROSS APPLY
(
SELECT COUNT(*) AS NoWorkDays
FROM [dbo].[ih_non_work_days] D
WHERE D.date_off BETWEEN com.crt_date AND com.completion_date
) AS non
CROSS APPLY
(
SELECT COUNT(*) AS NoWorkDays
FROM [dbo].[ih_non_work_days] D
WHERE D.date_off BETWEEN com.current_task_target_date AND com.completion_date
) AS non1
INNER JOIN
[dbo].[ih_officer] AS offi
ON com.current_officer_code = offi.officer_code
INNER JOIN
[dbo].[em_comm_type] AS comt
ON com.comm_type = comt.comm_type
INNER JOIN
[dbo].[em_comm_type_main] as main
ON com.main_code = main.main_code
AND com.comm_type = main.comm_type
INNER JOIN
[dbo].[em_work_group] as work
ON com.work_group = work.work_group
INNER JOIN
[dbo].[em_originator] AS orig
ON com.comm_reference = orig.comm_reference
AND orig.person_type = 'O'
LEFT OUTER JOIN
[dbo].[co_place] AS pla
ON orig.place_ref = pla.place_ref
LEFT OUTER JOIN
[dbo].[co_person] AS per
ON orig.person_ref = per.person_ref
INNER JOIN
[InSightOpenHousingDataWarehouse].[dbo].[DimWhen] AS dim
on com.crt_date = dim.Date
/*
INNER JOIN
(SELECT DISTINCT DIA.reference
,CONCAT(DIA.entry_text,DIAE.entry_text) AS 'Callback Details'
,dia.entry_date
FROM [dbo].[em_diary] dia
INNER JOIN
[dbo].[em_diary_ext] diae
ON DIA.reference = DIAE.reference
and dia.entry_date = diae.entry_date
and dia.entry_time = diae.entry_time
) AS DIARY
ON com.comm_reference = DIARY.reference
*/
WHERE com.main_code = 'CALLB'
-
Use a CASE expression
CASE WHEN MyColumn < 0 THEN 0 ELSE MyColumn END
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
But how do I include that in the expression below?
,CASE WHEN com.completion_date IS NOT NULL AND dim.DayName <> 'Saturday'THEN DATEDIFF(d, com.current_task_target_date,com.completion_date) - non1.NoWorkDays
WHEN com.completion_date IS NOT NULL AND dim.DayName = 'Saturday'
THEN DATEDIFF(d, com.current_task_target_date,com.completion_date)
ELSE NULL
END AS 'DaysOverTarget'
I don't really want an extra column
Thanks
-
You can nest CASE expressions
,CASE WHEN com.completion_date IS NOT NULL AND dim.DayName <> 'Saturday'
THEN CASE WHEN DATEDIFF(d, com.current_task_target_date,com.completion_date) < non1.NoWorkDays THEN 0 ELSE DATEDIFF(d, com.current_task_target_date,com.completion_date) - non1.NoWorkDays END
WHEN com.completion_date IS NOT NULL AND dim.DayName = 'Saturday'
THEN CASE WHEN DATEDIFF(d, com.current_task_target_date,com.completion_date) < 0 THEN 0 ELSE DATEDIFF(d, com.current_task_target_date,com.completion_date) END
ELSE NULL
END AS 'DaysOverTarget'
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Perfect. Worked a treat
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply