searching for time specific records-CodePudding

I have a data table that lists weekly timesheet records for applicants.

I need to create a report of applicants who:

Have had a timesheet last week Who have NOT had a timesheet in the preceding 6 weeks but HAVE had a timesheet at any time prior to that

Any ideas? not got very far (im new at this)

SELECT
    [TimesheetID],
    [PeriodStarting],
    [Sector],
    [ApplicantId]
FROM
    [WebServices].[TimesheetEntity]
where
    [periodstarting] > getdate() - 8 (
        select
            [periodstarting]
        FROM
            [WebServices].[TimesheetEntity]
        where
            [periodstarting] not between (GETDATE() -8)
            AND (GETDATE() -50)

CodePudding user response：

if you only need app names , here is one way :

select [ApplicantId]    
from [WebServices].[TimesheetEntity]
group by [ApplicantId]
having count(case when periodstarting > dateadd(day, -8, getdate())) > 1  -- check if anyebtry during last 8 days
   and count(case when periodstarting between dateadd(day, -50, getdate()) and dateadd(DAY, -8, getdate())) = 0 -- check no case between last week and last 50 days
   and count(case when periodstarting < dateadd(DAY, -50, getdate())) > 1 -- check if any case up to 50 days ago

CodePudding user response：

Something like that?

SELECT
    [TimesheetID],
    [PeriodStarting],
    [Sector],
    [ApplicantId]
FROM
    [WebServices].[TimesheetEntity]
where
    [periodstarting] between dateadd(week, -1, getdate()) and getdate() # have an timesheet last week 
and (ApplicantId not  in 
      (select ApplicantId 
         from [WebServices].[TimesheetEntity] 
         where periodstarting >= dateadd(week, -7, getdate())
               and periodstarting < dateadd(week, -1, getdate())
    ) # does not have timesheet for last 6 weeks priori to the current one
and 
(ApplicantId in 
      (select ApplicantId 
         from [WebServices].[TimesheetEntity] 
         where  periodstarting < dateadd(week, -7, getdate())
    ) #had timesheet in the past

CodePudding user response：

EXISTS is very efficient b/c it stops processing once it knows it is true or not.

SELECT
    [TimesheetID],
    [PeriodStarting],
    [Sector],
    [ApplicantId]
FROM
    [WebServices].[TimesheetEntity]
where
    exists (select 1    --Have had a timesheet last week
from [WebServices].[TimesheetEntity]
where [periodstarting] > getdate() - 8)

and not exists (    --Who have NOT had a timesheet in the preceding 6 weeks 
        select 1  
        FROM
            [WebServices].[TimesheetEntity]
        where
            [periodstarting] between (GETDATE() -50)
            AND (GETDATE() -8)

and exist ( -- but HAVE had a timesheet at any time prior to 6 weeks
  select 1  
        FROM
            [WebServices].[TimesheetEntity]
        where
            [periodstarting] < (GETDATE() -50)
)