Power query - sql statement

[melq64]

I'm trying to extract subset of data from Oracle database. I have access and connection successfully setup via Excel Power Query.

The two tables connected to Excel report contains millions of rows of data. To extract 12months of this data is approx 11million rows. My Excel report using PowerPivot and slicers, is linked to these tables. Loading and refreshing the tables will take too long. Looking for more efficient method to overcome this.

So I thought of using the SQL statement in PowerQuery connection to extract smaller subset of this data - 6months data instead of 12months.

SQL is not my strength so I'm looking for an SQL statement that extracts only 6months of data and when report is refreshed, it will only refresh 6months data from present day back 6months i.e. [present date - 6months]


Hope this made sense. Appreciate any help

 

[Fazza]

Perhaps like

SELECT fields you want
FROM table/s
WHERE somedatefield > Date - 183

If you don't need individual records & instead can work with sums, then this could hugely reduce the dataset. Something like

SELECT some fields, SUM(a field), SUM(another field), SUM(one more)
FROM table/s
WHERE somedatefield > Date - 183
GROUP BY some fields

Maybe you could try
WHERE somedatefield >= DateAdd("m",-6,Date)

or even
WHERE somedatefield >= DateSerial(Year(Date),Month(Date)-6, Day(Date))

or some variation

 

[melq64]

Thanks for your tips but I need individual rows for further calculations within Powerpivot. However, am getting this error based on your suggestions: (sorry obviously am not familiar with sql)

statement 1:
select * from EXCEPTION_SUMMARY
where ACCEPTANCE_DATE > Date - 183;


statement 2:
select * from EXCEPTION_SUMMARY
where ACCEPTANCE_DATE >=DateSerial(Year(Date),Month(Date)-6,Day(Date));

-------------------------------------------------------------------------------------------
ORA-00936: missing expression
00936. 00000 - "missing expression"
*Cause:
*Action:
Error at Line: 5 Column: 45

-------------------------------------------------------------------------------------------

 

[Fazza]

The solution needs to suit the specifics of Oracle's database/SQL. I can't help with that - I have nil specific knowledge of Oracle.

If no-one else chimes in here (Mr Excel) suggest you search Oracle documentation. Or even start a new thread seeking Oracle specific advice.

regards

 

[melq64]

Ok sure I understand. Thanks for your help. Appreciate it

 

[Fazza]

via google. https://stackoverflow.com/questions/45816263/returning-records-from-last-6-months-with-select
HTH

 

[melq64]

Perfect. Thank you

 

[Fazza]

you're welcome