Solved

SQL Get Previous Day Data excluding weekends

Posted on 2010-09-08
8
1,834 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

737 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