asked on

Oracle query clean up

Hey there,

I'm looking for experts that may be able to simplify the query below. The whole basis on the where nonesense is related to:

https://www.experts-exchange.com/questions/26494839/Oracle-Dooozy-Cumulative-Sum-by-Month.html

I'm runing a report off of SSRS from as Oracle DB. Two parameters on the report (which also runs under a subscription). Basically, the report is scheduled to run every Sunday. When it runs both the :Start_Date and :End_Date parameters are NULL. The where clause then deciphers whether it's the customers month end or not. If it is data for the month is reported, if it's not data for the week is reported.

On the flip side the report is also set up to be ran manually with paramters filled in and then deciphering whether the entered parameter is a month end... etc...

I just want to know if there's a neater, cleaner, more efficient way to write this. I can really only work from the SSRS/MSVS side (no functions/stored procedures on the Oracle side).

Any ideas???

SELECT TO_CHAR(tblOrd.Ord_Date, 'IW') AS WEEK, TO_CHAR(tblOrd.Ord_Date, 'yyyy-mm-dd') AS CDate, tblOrd.Ord_Num, tblOrd_D.Pall_Code, tblOrd_D.Pall_Qty 
FROM tblOrd
	INNER JOIN tblOrd_D ON 
			tblOrd.Comp = tblOrd_D.Comp 
		AND tblOrd.Cust = tblOrd_D.Cust 
		AND tblOrd.Ord_Num = tblOrd_D.Ord_Num 
WHERE :Start_Date IS NULL AND :End_Date IS NULL AND TRUNC(SYSDATE) NOT IN (SELECT NEXT_DAY(NEXT_DAY(LAST_DAY(TRUNC(SYSDATE -1, 'mm')) -7, 'Saturday') -7, 'Monday') +6 FROM DUAL)
	AND tblOrd.Ord_Date BETWEEN (SELECT NEXT_DAY(TRUNC(SYSDATE), 'Sunday') - 13 FROM DUAL
									) AND (SELECT NEXT_DAY(TRUNC(SYSDATE), 'Sunday') - 6 - 1 / (24 * 60 * 60) FROM DUAL) 
	AND tblOrd.Comp = '01' AND tblOrd.Cust = 'ABC' AND tblOrd.Stat = 'A' 
	OR
		:Start_Date IS NULL AND :End_Date IS NULL AND TRUNC(SYSDATE) IN(SELECT NEXT_DAY(NEXT_DAY(LAST_DAY(TRUNC(SYSDATE -1, 'mm')) -7, 'Saturday') -7, 'Monday') +6 FROM DUAL) 
	AND tblOrd.Ord_Date BETWEEN (SELECT NEXT_DAY(NEXT_DAY(TRUNC(SYSDATE -1, 'mm'), 'Saturday') -7, 'Monday') FROM DUAL
									) AND (SELECT NEXT_DAY(NEXT_DAY(LAST_DAY(TRUNC(SYSDATE -1, 'mm')) -7, 'Saturday') -7, 'Monday') +7 - 1 / (24 * 60 * 60) FROM DUAL) 
	AND tblOrd.Comp = '01' AND tblOrd.Cust = 'ABC' AND tblOrd.Stat = 'A' 
	OR 
		:Start_Date IS NULL AND :End_Date IS NOT NULL AND TO_DATE(:End_Date, 'dd.mm.yy') NOT IN (SELECT NEXT_DAY(NEXT_DAY(LAST_DAY(TRUNC(TO_DATE(:End_Date, 'dd.mm.yy') -1, 'mm')) -7, 'Saturday') -7, 'Monday') +6 FROM DUAL)
	AND tblOrd.Ord_Date BETWEEN (SELECT NEXT_DAY(TO_DATE(:End_Date, 'dd.mm.yy'), 'Sunday') - 13 FROM DUAL
									) AND (SELECT NEXT_DAY(TO_DATE(:End_Date, 'dd.mm.yy'), 'Sunday') - 6 - 1 / (24 * 60 * 60) FROM DUAL) 
	AND tblOrd.Comp = '01' AND tblOrd.Cust = 'ABC' AND tblOrd.Stat = 'A'  
	OR 
		:Start_Date IS NULL AND :End_Date IS NOT NULL AND TO_DATE(:End_Date, 'dd.mm.yy') IN(SELECT NEXT_DAY(NEXT_DAY(LAST_DAY(TRUNC(TO_DATE(:End_Date, 'dd.mm.yy') -1, 'mm')) -7, 'Saturday') -7, 'Monday') +6 FROM DUAL) 
	AND tblOrd.Ord_Date BETWEEN (SELECT NEXT_DAY(NEXT_DAY(TRUNC(TO_DATE(:End_Date, 'dd.mm.yy') -1, 'mm'), 'Saturday') -7, 'Monday') FROM DUAL
									) AND (SELECT NEXT_DAY(NEXT_DAY(LAST_DAY(TRUNC(TO_DATE(:End_Date, 'dd.mm.yy') -1, 'mm')) -7, 'Saturday') -7, 'Monday') +7 - 1 / (24 * 60 * 60) FROM DUAL)  
	AND tblOrd.Comp = '01' AND tblOrd.Cust = 'ABC' AND tblOrd.Stat = 'A' 
	OR 
		:Start_Date IS NOT NULL AND :End_Date IS NOT NULL 
	AND tblOrd.Ord_Date BETWEEN TRUNC(TO_DATE(:End_Date, 'yymmdd')) 
									AND TO_DATE(:End_Date||'23:59:59', 'dd.mm.yyhh24:mi:ss')  
	AND tblOrd.Comp = '01' AND tblOrd.Cust = 'ABC' AND tblOrd.Stat = 'A' 
ORDER BY CDate

Open in new window

Walter Ritzel

Yes, there is a cleaner way of doing that.
First, you need to create a date table. This is a resource largely used in analytical databases, to ease up the hard work of aggregate information in different ways in a time dimension.

The table structure that I use is below. I have 2 type of data being represented here: the actual representation of a date and the fiscal representation of a date. After create a table, you just need to run a query to fill all that information.

CREATE TABLE DIM_TIME
(
  DATE_KEY                     INTEGER,  --could be an autoincrement field or simply the date converted to numeric
  DAY_DATE                     DATE,  --the date in question
  DAY_IN_WEEK                  INTEGER, --numeric representation of the week day - 1 to 7(sunday to saturday)
  DAY_IN_WEEK_NAME             VARCHAR2(10 BYTE),  --week day
  DAY_IN_YEAR                  INTEGER, 
  DAY_IN_MONTH                 INTEGER,
  IS_WEEK_END                  CHAR(1 BYTE),
  WEEK_IN_YEAR                 INTEGER,
  WEEK_IN_MONTH                INTEGER,
  MONTH_NUM                    INTEGER,
  MONTH_SHORT_NAME             CHAR(3 BYTE),
  MONTH_FULL_NAME              VARCHAR2(30 BYTE),
  QTR_NUM                      INTEGER,
  HALF_NUM                     INTEGER,
  YEAR_NUM                     INTEGER,
  YEAR_WEEK                    VARCHAR2(10 BYTE),
  YEAR_MONTH                   VARCHAR2(10 BYTE),
  YEAR_QTR                     VARCHAR2(10 BYTE),
  YEAR_HALF                    VARCHAR2(10 BYTE),
  FISCAL_WEEK                  INTEGER,
  FISCAL_WEEK_NAME             VARCHAR2(20 BYTE),
  FISCAL_PERIOD                INTEGER,
  FISCAL_PERIOD_KEY            INTEGER,
  FISCAL_PERIOD_NAME           VARCHAR2(20 BYTE),
  FISCAL_QTR                   INTEGER,
  FISCAL_QTR_NAME              VARCHAR2(20 BYTE),
  FISCAL_HALF                  INTEGER,
  FISCAL_HALF_NAME             VARCHAR2(20 BYTE),
  FISCAL_YEAR                  INTEGER,
  FISCAL_YEAR_NAME             VARCHAR2(20 BYTE),
  AUDIT_KEY                    INTEGER,
  LAST_FISC_DAY_WEEK           VARCHAR2(1 BYTE),
  LAST_FISC_DAY_MONTH          VARCHAR2(1 BYTE),
  LAST_FISC_DAY_QTR            VARCHAR2(1 BYTE),
  LAST_FISC_DAY_YEAR           VARCHAR2(1 BYTE),
  LAST_FISC_DAY_HALF_YEAR      VARCHAR2(1 BYTE),
  LAST_CALENDAR_DAY_WEEK       VARCHAR2(1 BYTE),
  LAST_CALENDAR_DAY_MONTH      VARCHAR2(1 BYTE),
  LAST_CALENDAR_DAY_QTR        VARCHAR2(1 BYTE),
  LAST_CALENDAR_DAY_YEAR       VARCHAR2(1 BYTE),
  LAST_CALENDAR_DAY_HALF_YEAR  VARCHAR2(1 BYTE)
)

Open in new window

With this table joined to your other tables, you have ability to group by your information by year, quarter, month or week pretty easily.
See an example that would work:

-- to list pallet quantities by the fiscal week
SELECT dim_time.fiscal_week AS CDate, tblOrd_D.Pall_Code, sum(tblOrd_D.Pall_Qty) 
FROM tblOrd
    INNER JOIN tblOrd_D ON tblOrd.Comp = tblOrd_D.Comp AND tblOrd.Cust = tblOrd_D.Cust AND tblOrd.Ord_Num = tblOrd_D.Ord_Num
    INNER JOIN dim_time ON tblOrd.Ord_date = dim_time.day_date 
order dim_time.fiscal_week, tblOrd_D.Pall_Code

-- to list orders with the fiscal week
SELECT dim_time.fiscal_week AS CDate, tblOrd.Ord_Num, tblOrd_D.Pall_Code, tblOrd_D.Pall_Qty 
FROM tblOrd
    INNER JOIN tblOrd_D ON tblOrd.Comp = tblOrd_D.Comp AND tblOrd.Cust = tblOrd_D.Cust AND tblOrd.Ord_Num = tblOrd_D.Ord_Num
    INNER JOIN dim_time ON tblOrd.Ord_date = dim_time.day_date 
order dim_time.fiscal_week

Open in new window

NEVAEHSIN

ASKER

I would love to be able to do this, however...

"..I can really only work from the SSRS/MSVS side (no functions/stored procedures on the Oracle side..."

I should've added I cannot add tables to the current oracle database. I may be able to get our provider to add a function / procedure.

Also, this scenario is an odd one where I would need to create a table specifically identifying this customers month ends...

https://www.experts-exchange.com/questions/26494839/Oracle-Dooozy-Cumulative-Sum-by-Month.html

...I was thinking more along the line of the way I've written the where clause and if there's an easier, more efficient way of writing it as the final report will have 8-12 datasets and each of them needing something similar to the where clause above.

Walter Ritzel

If you are using SSRS, you probably already have a time dimension. It is just a matter of knowing if you can use that. I've read the other thread, this is why I have suggested this approach.

Also, I dont see this as odd, if you consider this is basically a standard in terms of analytical databases and reports.

ASKER CERTIFIED SOLUTION

Walter Ritzel

membership

This solution is only available to members.

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

Start Free Trial