Manipulating Dates with Case Statements

1

Hello Everybody,

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

SELECT
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],
CASE
WHEN CAST(ph.comp_date AS DATE) <= ph.deadline THEN 'TRUE'
ELSE 'FALSE'
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

GROUP BY
pdh.description,
cr.quoteid,
ph.start_date,
ph.comp_date,
ph.deadline

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.

Many Thanks

Vinnie

2

this should get you going
Read about Datefirst


select @@DATEFIRST		-- in my case it is 7 for Sunday
--set datefirst change to your locale if first day is not Sunday
--assuming first day of week is Sunday

create table #vinnie(productname varchar(50), comp_date datetime)

insert into #vinnie
select 'lasagna', dateadd(dd,1,getdate()) union
select 'chicken parma', dateadd(dd,2,getdate()) union
select 'Pizza', dateadd(dd,-1,getdate()) union
select 'Salad', dateadd(dd,0,getdate()) 


select *, 
       datename(dw,comp_date),
	   datepart(dw, comp_date),
	   case 
		  --Sat
	      when datepart(dw, comp_date) in (7) then dateadd(dd,-1,comp_date)
		  --Sun
		  when datepart(dw, comp_date) in (1) then dateadd(dd,-2,comp_date)
		  else comp_date
		  end		  
  from #vinnie

drop table #vinnie
3

or

select @@DATEFIRST		-- in my case it is 7 for Sunday
--set datefirst change to your locale if first day is not Sunday
--assuming first day of week is Sunday

create table #product_header(productid int identity(1,1), productname varchar(50), 
comp_date datetime, deadline datetime)

insert into #product_header
select 'lasagna', dateadd(dd,1,getdate()), dateadd(dd,-7,getdate()) deadline union
select 'chicken parma', dateadd(dd,2,getdate()), 
dateadd(dd,7,getdate()) deadline union
select 'Pizza', dateadd(dd,-1,getdate()), dateadd(dd,7,getdate()) deadline union
select 'Salad', dateadd(dd,0,getdate()), dateadd(dd,7,getdate()) deadline 

;
with ctePh
as
(
SELECT productid,
       productname,
       datename(dw,comp_date) as date_name,
	   datepart(dw, comp_date) as date_part,
       case 
		  --Sat
	      when datepart(dw, comp_date) in (7) then dateadd(dd,-1,comp_date)
		  --Sun
		  when datepart(dw, comp_date) in (1) then dateadd(dd,-2,comp_date)
		  else comp_date
		  end	as comp_date,
		  deadline
FROM #product_header ph
)

select *,
CASE
WHEN CAST(ph.comp_date AS DATE) <= ph.deadline THEN 'TRUE'
ELSE 'FALSE'
END AS 'In Service'
from ctePh ph

drop table #product_header
4

Hi yosiasz.

I can see that you created temp tables etc to test your workings and I can see what you have done with the case statement manipulating the dates which is great.

How would you re-write the script I provided using your CTE? I am not sure how to go about it as I had a few tables in my query with WHERE and GROUP BY clauses etc.

5

replace temp table in cte with your real table along with the where clause

WHERE CAST(ph.end_date AS Date) BETWEEN @startDate And @endDate

after the cte join the cte to the other tables as you have it in your original script joining by productid.

6

Hi yosiasz,

Thank you for taking the time to help me.

Much appreciated

Vinnie