asked on

SQL Syntax Question

Hello I have the below code that works great. However, i want the 2016 to not be a hard coded and would prefer it to be the previous year i can do this in access with DATESERIAL(YEAR(DATE())-1,1,1) and DATESERIAL(YEAR(DATE())-1,12,31) bu ti can figure out the syntax to do the same thing in the below.

[1].FILE_NBR=[2].FILE_NBR(+)
AND [1].PAYGROUP=[2].PAYGROUP(+)
AND [1].AL_PROCESS_EFFDT = (SELECT MAX(B.AL_PROCESS_EFFDT) FROM PS_AL_YTD_ACCUM B
WHERE B.FILE_NBR = [1].FILE_NBR
AND B.PAYGROUP = [1].PAYGROUP
AND AL_PROCESS_EFFDT <= TO_DATE('2016-12-31', 'YYYY-MM-DD')
AND AL_PROCESS_EFFDT >= TO_DATE('2016-01-01', 'YYYY-MM-DD')
AND WEEK_NBR IN ('46','48','50','52','53'))

Shaun Kline

You could try:
AND YEAR(AL_PROCESS_EFFDT) = YEAR(GETDATE()) - 1

Another option using your SQL is:
AND AL_PROCESS_EFFDT <= DATEADD(YEAR, -1, '2016-12-31')
AND AL_PROCESS_EFFDT >= DATEADD(YEAR, -1, '2016-01-01')

Peter Chan

If you're running against Access, you should not use To_date, that is ffor Oracle.

PortletPaul

If using Oracle

ADD_MONTHS() can be used to deduct 12 months (use negative 12)

johnsone

Assuming Oracle, because that is where this is posted...

AND AL_PROCESS_EFFDT <= TRUNC(SYSDATE, 'YYYY') - (1 / 86400)
AND AL_PROCESS_EFFDT >= ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -12)

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Robbins

ASKER

Thank you, the first part of this worked perfectly.

johnsone

All 3 where clauses I posted will do the exact same thing. It is a matter of preference which one you want to use.

John Robbins

ASKER

I went with the below because it didnt have the 1/86400, what does 1/86400 accomplish?

AND AL_PROCESS_EFFDT < TRUNC(SYSDATE, 'YYYY')
AND AL_PROCESS_EFFDT >= ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -12)

johnsone

1/86400 in date math is 1 second. There are 86400 seconds in a day.

TRUNC(SYSDATE, 'YYYY') is January 1, 2017 00:00:00
TRUNC(SYSDATE, 'YYYY') - (1/86400) is December 31, 2016 23:59:59

That is also the reason for < or <=. You want < for the January 1 date, but you want <= for the December 31 date. As I said, all three accomplish the exact same thing.