I have a query that was working as intended but I now need to tweak it but I am not sure how to get the best outcome. This is the original query:
DECLARE @startDate datetime = dateadd(month, datediff(month, 0, getdate()), 0)--1st Day of the month
, @endDate datetime = cast(getdate()-2 as date); --2 days ago at midnight
pdh.description AS [Product Description],
cr.quoteid AS [Quote Number],
ph.start_date AS [Date Started],
ph.comp_date AS [Date Completed],
ph.deadline AS [Deadline Date],
WHEN CAST(ph.comp_date AS DATE) <= ph.deadline THEN 'TRUE'
END AS 'In Service'
FROM product_header ph
INNER JOIN product_detail_header pdh
ON ph.productid = pdh.productid
INNER JOIN product_tasks pt
ON ph.end_date = pt.end_date
INNER JOIN campaign_records cr
ON pt.quoteid = cr.quoteid
WHERE CAST(ph.end_date AS Date) BETWEEN @startDate And @endDate
The case statement was working fine but now the logic has changed. I now need to say: If the ph.comp_date falls on a weekend then it should be classed as a Friday and then compared to the target date to determine if in or out of service.. If it does not fall on a weekend then use the ph.comp_date provided against the target date.
For example, if the ph.comp date is Saturday 2nd or Sunday 3rd June and the target date is Friday 1st June the result should be True but at the moment it will say False.
If the target date was Thursday 31st May it would be False because the revised date of Friday 1st June is still after the target date.
I want to keep the ph.comp_date column in my results set with the dates as they are but change the True/False output in the case statement based on the logic I have described.
Is this possible?
I am quite inexperienced with SQL so any help would be much appreciated.