Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Get Previous Day Data excluding weekends

Posted on 2010-09-08
8
Medium Priority
?
1,919 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 500 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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…

721 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