Solved

SQL Get Previous Day Data excluding weekends

Posted on 2010-09-08
8
1,718 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
  • 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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 33627754
did you try my query?
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

861 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

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now