convert date format to period

[bharath28]

  Hi All,

Here is my existing query which i run in qlikview script, to fetch data from oracle-

load *;

select * from transactionTbl       

WHERE

            DATE >= TO_DATE('$(vAppStartDate)', 'DD/MM/YYYY')     

            AND  DATE <= TO_DATE('$(vAppEndDate)', 'DD/MM/YYYY');

variables-

--vAppStartDate = 01/06/2014

--vAppEndDate = 30/06/2015

Instead of using the Date column in the WHERE condition, i want to use PeriodID column existing in same table, to make the query faster.

The Period column is in below format.

PeriodID format--> 201294   //means 2012Q4. So 9 means Q

Since i am using variables, the values are always in date format, so my requirement is to first change date coming from variable to period format.

I wrote the pseudocode, because i am sure sure how to convert the date to periodID format in oracle. Also in the periodID 9 means Q.

select * from transactionTbl       

WHERE

            PeriodID >= period_function(TO_DATE('01-JUL-14', 'DD/MM/YYYY'), 'YYYYxx')

            AND  PeriodID >= period_function(TO_DATE('01-JUL-15', 'DD/MM/YYYY'), 'YYYYxx')

Please help me with the query.

Thanks!

[jonathandienst · Accepted solution]

Assuming:

vAppStartDate = 01/06/2014

vAppEndDate = 30/06/2015

Then, before the SQL query

Let vPeriodStart = Year(vAppStartDate) & '9' & Num(Ceil(Month(vAppStartDate)/3), '00');

Let vPeriodEnd = Year(vAppEndDate ) & '9' & Num(Ceil(Month(vAppEndDate )/3), '00');

SQL

SELECT * From transactionTbl      

Where PeriodID >= $(vPeriodStart)

     And PeriodID <= $(vPeriodEnd);

If PeriodID is a a text field, add quotes:

SQL

SELECT * From transactionTbl      

Where PeriodID >= '$(vPeriodStart)'

     And PeriodID <= '$(vPeriodEnd)';

[bharath28]

Thanks a lot!