asked on

SQL Get Previous Day Data excluding weekends

Hi Experts,

I have written a sql script and trying to implement the weekend schedule in the script. I already have it calculated to previous day but then it becomes an issue when I run the report on Monday. Since according to the logic, previous day is Sunday but there is no data on Sunday.

So I want to code to retrieve Friday's data (last activity) on Monday

* I am running SQL on DB2

SELECT 
CRI.CRI_CID_CUST_ID AS Customer_RID,
TRIM(CUS.CUS_NME_SHORT_NAME) AS Customer_Name,
TRIM(CUS.CUS_CDE_CUST_DESC) As Customer_Desc,
CUS.CUS_CDE_PRIM_SIC AS Customer_SIC,
CRI.CRI_CDE_REM_METHOD AS Trans_Type,
IRI.IRI_CDE_MSG_TYPE AS Msg_Type,
CRI.CRI_CDE_CURRENCY As CCY,
TRIM(CRI.CRI_DSC_REM_INSTR) AS Remit_Instr,
CRI.CRI_IND_APPROVED AS Approval_Status,
CRI.CRI_RID_REM_INSTR AS Remit_Instr_RID,
CRI.CRI_TSP_REC_CREATE AS Create_Date,
CRI.CRI_UID_REC_CREATE AS Created_By

FROM ls2user.VLS_CUST_REM_INSTR CRI 

LEFT JOIN ls2user.vls_customer CUS on CUS.CUS_CID_CUST_ID = CRI.CRI_CID_CUST_ID 

LEFT JOIN ls2user.VLS_REM_INSTR_MSG IRI on IRI.IRI_RID_REM_INSTR = CRI.CRI_RID_REM_INSTR 

WHERE CAST(CRI_TSP_REC_CREATE AS DATE) = Date(Current DATE - 1 day)-- Yesterday's Date


AND CRI.CRI_CDE_REM_METHOD = 'IMT'
AND IRI.IRI_CDE_MSG_TYPE = 'MT202'
AND (CUS.CUS_CDE_CUST_DESC NOT IN ('BCSG','BDE','CGCR','DBA','FBA','INS','MBA','S&L') 
OR CUS.CUS_CDE_PRIM_SIC NOT IN ('6000','6010','6013','6100','6160','6200','6210','6220','6310'))
ORDER BY CUS.CUS_NME_SHORT_NAME

Open in new window

ASKER CERTIFIED SOLUTION

momi_sabag

membership

This solution is only available to members.

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

Start Free Trial

gmarino

So - what do you plan to do to handle Holidays?

The best solution is to create a table that contains the business/processing calendar for your app. This will allow you the flexibility needed to handle days that will have no data to process.

- Greg

bose3

ASKER

Greg,
I am not building a holiday schedule because in some entities there is activity due to their global locations
Fiaz

momi_sabag

did you try my query?

gmarino

I would still suggest the idea of a table to hold this information - and consider expanding the table definition to include Location Specific calendars.

It's just a suggestion/idea - meant to spur discussion and other ideas. Momi's code should do exactly what you are asking for.

Good Luck!

- Greg

devarajamurthy

create a function to return previous day date depending on weekday of the date.
You can use DAYOFWEEK function available to check the day. it is 1 for sunday through 7 for satuday.
if DAYOFWEEK returns 1 Previous day DATE is current DATE - 2 day.
if DAYOFWEEK returns 7 Previous day DATE is current DATE - 1 day.

so function will return Previous day DATE considering weekends.

bose3

ASKER

Thanks momi.
It works. Just curious how does "Current Date = 2" works

gmarino

This is wrong:

when day(current date) = 2

It should actually be:

when DAYOFWEEK(current date) = 2

DAYOFWEEK returns a number between 1-7 which represents the Day of the Week 1 for Sunday through 7 for Saturday. (Devarajamurthy's post had it right).

DAY returns a number which represents todays date (8)

- Greg