Return Specific Value from a table

Jes Sie

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.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jes Sie2y ago

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 < CAST(getdate() as date) and (VehicleChasisNo NOT IN (SELECT VehicleChasisNo FROM tblMotorInsurance_eCI WHERE YEAR(PeriodFrom) = YEAR(GETDATE()))) and IssuanceType <> 'Short Term' 
order by PeriodFrom desc

Jes Sie2y ago
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.

Jignesh Kumar2y ago

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