How to correctly set up WHERE Clause

[rrmenon]

In my where clause, i'm trying to limit the datetimein selected (after AND), to only those dates in the compdate range i specify right after WHERE (WHERE datepart(t2.compDate) BETWEEN '01Jan2024'd AND '31Mar2024'd).  

Is my query written correctly.

This is my goal- pull average daily theoretical (pulled from slots table) for the comp date plus one month ****Only for Q1 (01/01-03/31).

PROC SQL;

SELECT sum(t1.TheoWin)/30  FROM Work.Slots t1 
JOIN TDCX.vCXComps t2
ON t1.PtnId=t2.ptnid 
WHERE datepart(t2.compDate) BETWEEN '01Jan2024'd AND '31Mar2024'd
AND datepart(t1.DateTimeIn) BETWEEN datepart(t2.compDate) AND INTNX('month', datepart(t2.compDate), 1,'b');   
QUIT;

[SASKiwi]

It looks like you require an inner join as you need valid date ranges from your T2 table. I'd also suggest you look at the output data first to confirm you are getting the right data like so:

PROC SQL;
create table test as
SELECT * 
FROM Work.Slots t1 
INNER JOIN TDCX.vCXComps t2
ON t1.PtnId=t2.ptnid 
WHERE datepart(t2.compDate) BETWEEN '01Jan2024'd AND '31Mar2024'd
AND datepart(t1.DateTimeIn) BETWEEN datepart(t2.compDate) AND INTNX('month', datepart(t2.compDate), 1,'b');   
QUIT;

[rrmenon]

Is a regular JOIN, not an inner join by default in SAS---was not aware, thanks

[Patrick]

Your SQL looks syntactically correct but if it also will return your desired result depends on your source tables. We can't tell if you don't share representative sample data and show us the desired result. 

My first question would be: In your table tdcx.vCXComps how many rows (dates) do you have per PtnId?

As for keyword JOIN used on its own: Yes, this is the same like INNER JOIN. I'd consider it a good practice to always use the INNER for clarity.

If you use Proc SQL option FEEDBACK then the SAS log will show you to what the SQL compiler transforms your code for execution.

In your case that's an implicit join using the WHERE clause only and with only AND conditions I assume it will return what you're after - but again: We can't really know without sample data and desired result. 

28         proc sql feedback;
29           select sum(t1.theowin)/30  from work.slots t1
30             join tdcx.vcxcomps t2
31               on t1.ptnid=t2.ptnid
32             where datepart(t2.compdate) between '01jan2024'd and '31mar2024'd
33               and datepart(t1.datetimein) between datepart(t2.compdate) and intnx('month', datepart(t2.compdate), 1,'b');
NOTE: Statement transforms to:

        select SUM(T1.theowin) / 30
          from WORK.SLOTS T1, TDCX.VCXCOMPS T2
         where (T1.ptnid = T2.ptnid) and (DATEPART(T2.compdate) between '01JAN2024'D and '31MAR2024'D) and DATEPART(T1.datetimein) 
between DATEPART(T2.compdate) and INTNX('month', DATEPART(T2.compdate), 1, 'b');

34         quit;

And just for completeness here the "proof" that SAS will convert JOIN to INNER JOIN

28         proc sql feedback;
29           select l.name
30           from sashelp.class l join sashelp.class r
31             on l.name=r.name
32           ;
NOTE: Statement transforms to:

        select L.Name
          from SASHELP.CLASS L inner join SASHELP.CLASS R on L.Name = R.Name;

33         quit;