Need to modify a variable data to use as a “LIKE” in a COUNT query

I’m trying to use a numeric variable on a LIKE statement, but I do not know how to change the data to a numeric value.

Before we start, I apologize because I can’t copy the table structure as I’m working with a “SQL environment” that does not allow me to see it, so I will do my best to explain it.

I have two tables:

SAMPLE table

Code on the table :

SELECT sample_number,project,status,template,parent_aliquot,C_DUE_DATE_2
FROM SAMPLE
ORDER BY SAMPLE_NUMBER DESC

enter image description here

KPS_SMP_DUE_DATE_WEEK_PIVOT_VW

Code on the table:

select *
from KPS_SMP_DUE_DATE_WEEK_PIVOT_VW
where project = 'S/180308/01'

The important fields on this table are “PROJECT”, “PRODUCT” and “YEAR”. The fields seen on the right side of the table are not important.

enter image description here

I have this code :

SELECT vw.project,vw.year,(SELECT COUNT(s.sample_number)
                                      FROM SAMPLE s
                                      WHERE s.PROJECT = vw.PROJECT AND s.STATUS IN ('I', 'U', 'P') and s.TEMPLATE = 'KPS_DEFAULT' AND s.PARENT_ALIQUOT > 0 
                                      and s.C_DUE_DATE_2 < {ts '2021-01-20 00:00:00'} and s.C_DUE_DATE_2 LIKE '%'vw.year'%')
FROM KPS_SMP_DUE_DATE_WEEK_PIVOT_VW vw
where vw.project = 'S/180308/01' and vw.department = 'QC'

As seen before, “KPS_SMP_DUE_DATE_WEEK_PIVOT_VW” has entries categorized by the field “Year”, so I’m trying to fetch the total amount of samples that have expired each year, by using this code:

SELECT COUNT(s.sample_number)
                                      FROM SAMPLE s
                                      WHERE s.PROJECT = vw.PROJECT AND s.STATUS IN ('I', 'U', 'P') and s.TEMPLATE = 'KPS_DEFAULT' AND s.PARENT_ALIQUOT > 0 
                                      and s.C_DUE_DATE_2 < {ts '2021-01-20 00:00:00'} and s.C_DUE_DATE_2 LIKE '%'vw.year'%'

I would like that the ‘%’vw.year’%’ would work, and the expected result would be something like this :

Thanks

enter image description here

Answer

You can use the YEAR or DATEPART function as follows:

YEAR(s.C_DUE_DATE_2) = vw.year

DATEPART(YEAR,s.C_DUE_DATE_2) = vw.year

Advertisement

Answer