Return Specific Value from a table

Question

Hello DBA's. I have a query that selects all the insured policies from the previous year, as shown below:

select	MotorCINo, 
		IssuanceType, 
		VehicleType, 
		VehiclePower, 
		VehicleChasisNo, 
		InsuranceType, 
		InsuranceOption, 
		PeriodFrom, 
		PeriodTo 
from tblMotorInsurance_eCI 
where YEAR(PeriodFrom) = YEAR(DATEADD(year,-1,GETDATE())) and PeriodTo < CAST(getdate() as date) and IssuanceType <> 'Short Term'
order by PeriodFrom desc

The output is also shown below:

How do I get the list of motor vehicles that did not renew the policy this year? Thank you in advance.

Jignesh Kumar · Answer

Hello Hold, Please check for PeriodFrom, it should be in current year and not null. select MotorCINo, IssuanceType, VehicleType, VehiclePower, VehicleChasisNo, InsuranceType, InsuranceOption, PeriodFrom, PeriodTo from tblMotorInsurance_eCI where (YEAR(PeriodFrom) = YEAR(DATEADD(year,-1,GETDATE())) or PeriodFrom IS NULL) and IssuanceType 'Short Term' order by PeriodFrom desc

Jes Sie · Answer

If querying the whole year, it takes so long for the data to display. So, I made a between-period. Below is my query and so far it serves the purpose: select MotorCINo, IssuanceType, VehicleType, VehiclePower, VehicleChasisNo, InsuranceType, InsuranceOption, PeriodFrom, PeriodTo from tblMotorInsurance_eCI where (PeriodFrom BETWEEN '2022-06-01' AND '2023-11-03') and PeriodTo 'Short Term' order by PeriodFrom desc

Jes Sie · Answer

Thank you so much, Jignesh. I tested your recommended query; however, I found that the motor vehicle that was renewed this year exists in the result set.