Sub-Query not running , INTNX- is there an issue in how i am using this?

[rrmenon]

Spoiler

PROC SQL;
SELECT sum(t1.TheoWin) FROM TDCX.vVW_DWH_PTNSlots t1 
JOIN TDCX.vCXComps t2
ON t1.PtnId=t2.ptnid 
WHERE (t1.DateTimeIn BETWEEN t2.compDate AND INTNX('month', t2.compDate, 1))

[Patrick · Accepted solution]

Please be always specific what's not working - like returning an Error (please share log) or just not returning the expected result.

Does DateTimeIn store a SAS Date or DateTime value? Same goes for variable compDate.

Assuming DateTimeIn stores a datetime and compDate a Date value the expression needs to look like:

WHERE (datepart(t1.DateTimeIn) BETWEEN t2.compDate AND INTNX('month', t2.compDate, 1,'b'))

Or if these are both SAS DateTime values then you would need to use a datetime directive for intnx()

WHERE (t1.DateTimeIn BETWEEN t2.compDate AND INTNX('DTmonth', t2.compDate, 1,'b'))

INTNX({interval<multiple><.shift-index>}, start-from, increment<, 'alignment'>)

I would also always add the alignment explicitly. (The default is Beginning which I assume is what you want). 

Also be aware that that the BETWEEN operator is inclusive meaning the upper boundary (date start of next month) will be included into the selection.

[SASKiwi]

If TDCX points to an external database then it's quite possible INTNX won't get translated correctly into that database's SQL dialect. Id suggest trying SQL Passthru if you are reading an external database.