Solved

SQL Get Previous Day Data excluding weekends

Posted on 2010-09-08
8
1,866 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:bose3
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 125 total points
ID: 33627525
try

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) =
    case when day(current date) = 2 then date(current date - 3 days) else Date(Current DATE - 1 day) end -- 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
0
 
LVL 4

Expert Comment

by:gmarino
ID: 33627573
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
0
 

Author Comment

by:bose3
ID: 33627701
Greg,
I am not building a holiday schedule because in some entities there is activity due to their global locations
Fiaz
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 37

Expert Comment

by:momi_sabag
ID: 33627754
did you try my query?
0
 
LVL 4

Expert Comment

by:gmarino
ID: 33627822
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

0
 
LVL 1

Expert Comment

by:devarajamurthy
ID: 33628104
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.
0
 

Author Comment

by:bose3
ID: 33628106
Thanks momi.
It works. Just curious how does "Current Date = 2" works
0
 
LVL 4

Expert Comment

by:gmarino
ID: 33628348
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


0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question