Description

This SQL lists persons included in CSB for a set period.

Selection

declare @Start date = '2022-01-01';
declare @End date = '2022-02-28';

select
csb.crewmember as PIN,
p01.Name as Navn,
csb.Member_from as 'Date from',
csb.MEMBER_TO as 'Date To',
case when csb.member_from<'2022-01-01' then '2022-01-01' else csb.member_from end as 'Date From 1',
case when csb.member_to>'2022-02-28' then '2022-02-28' else csb.member_to end as 'Date To 2',

--(case when csb.member_to>'2022-02-28' then '2022-02-28' else csb.member_to end
--case when csb.member_from<'2022-01-01' then '2022-01-01' else csb.member_from end)+1 as 'Days',
datediff(DAY,Case when csb.MEMBER_FROM<@Start then @Start else csb.MEMBER_FROM end,case when csb.MEMBER_TO>@End then @End else csb.MEMBER_TO end)+1
as [Days],

id.SafetyID
from web_cp_csb_members csb
left join pw001p01 p01 on p01.pin=csb.CREWMEMBER
left join WEB_CP_CSB_SAFETYID ID on ID.seqno=csb.SAFETYID
where member_to>'2022-01-01' and member_from<'2022-02-28'
order by Safetyid