I have below data table [CRA_feasibility_test]
x
atmid CRA monday tuesday wednesday thursday friday saturday
DA0068C1 ABC Y N Y N N Y
I want to select the data based on the weekday of my date.If it is Y then want to get closest ‘Y’ i.e the day when it will be Y from the @date.
And also i want to get next
I am doing the following
declare @date datetime set @date ='2018-06-15'
Select @dateT as indentdate, DATENAME(dw,@dateT) as weekdayname,
case when DATENAME(dw,@dateT) ='monday'
then monday
when DATENAME(dw,@dateT) ='tuesday' then
tuesday
when DATENAME(dw,@dateT) ='wednesday' then
wednesday
when DATENAME(dw,@dateT) ='Thursday' then
thursday
when DATENAME(dw,@dateT) ='Friday' then
friday
when DATENAME(dw,@dateT) ='Saturday' then
saturday
end feasible
from [CRA_feasibility_test]
Advertisement
Answer
If I understand correctly, you can try to create a calendar table for next 7 day by cte recursive
, then use CROSS APPLY
with values
make unpivot to get Y
date which is the closest day.
declare @dateT datetime
set @dateT ='2018-06-15'
;with calendar as (
select @dateT startDt,dateadd(day,7,@dateT) endDt, 1 rn
union all
select dateadd(day,1,startDt) , endDt,rn +1
from calendar
WHERE dateadd(day,1,startDt)<endDt
), cte as (
select
atmid,v.*
from CRA_feasibility_test cross apply (
values ('monday',monday), ('tuesday',tuesday), ('wednesday',wednesday), ('thursday',thursday), ('friday',friday), ('saturday',saturday)
) v(name,flag)
)
select atmid,
@dateT today,
min(case when flag = 'Y' then startDt end) nextYDay,
datename(dw,min(case when flag = 'Y' then startDt end) ) nextYname
from calendar t1
join cte t2 on datename(dw,startDt) = t2.name
group by atmid
Result
atmid today nextYDay nextYname
DA0068C1 15/06/2018 00:00:00 16/06/2018 00:00:00 Saturday