Adding a date filter

Database

Adding a date filter

I'm adding up totals within a database and would like to see how the totals change over different time periods.

I'm using this code:

<code>

Select Sum(CASE WHEN Offer_1 = 'Y' THEN 1 ELSE 0 END) as Offer1,

Sum(CASE WHEN Offer_2 = 'Y' THEN 1 ELSE 0 END) as Offer2,

Sum(CASE WHEN Offer_3 = 'Y' THEN 1 ELSE 0 END) as Offer3

From table1

</Code>

This seems to get me the totals for the entire table. But I'd like to be able to use the Date column in the table to filter the results so I can see the results for a specific amount of time (i.e. month of June).

If I want to filter the table by date I use code that looks like this:

<Code>

Select CAST(CAST(Date AS FORMAT 'MM/DD/YYYY') AS CHAR(10)) as New_Date

From table1

Where New_Date between '06/01/2011' and '06/30/2011'

</code>

But I don't know how to combine these two.

Any thoughts?

Trevor

5 REPLIES
WAQ
Enthusiast

Re: Adding a date filter

What do you mean by "combine these two"?

If you want to see the sum for the specific period of time then put a filter in your first query and it will give you the required result.

Enthusiast

Re: Adding a date filter

sel

new_date,

Sum(CASE WHEN Offer_1 = 'Y' THEN 1 ELSE 0 END) as Offer1,

Sum(CASE WHEN Offer_2 = 'Y' THEN 1 ELSE 0 END) as Offer2,

Sum(CASE WHEN Offer_3 = 'Y' THEN 1 ELSE 0 END) as Offer3

from table1

??

Enthusiast

Re: Adding a date filter

Query would look something like this:

sel New_Date,

Sum(CASE WHEN Offer_1 = 'Y' THEN 1 ELSE 0 END) as Offer1,

Sum(CASE WHEN Offer_2 = 'Y' THEN 1 ELSE 0 END) as Offer2,

Sum(CASE WHEN Offer_3 = 'Y' THEN 1 ELSE 0 END) as Offer3

from table1

where New_Date between '06/01/2011' and '06/30/2011'

Enthusiast

Re: Adding a date filter

Hi, I have data from 11-01-2013 to 12-31-2014. the data gets refreshed every week. So Please help me to filter the data only for latest 12 months.

I have date column 'WeekEndingDate' which has data from 11-01-2013 to 12-31-2014.  I want to query the data from Max(WeekEndingDate) and add_months(WeekEndingDate,-12) so the result set has data from '01-01-2014' to '12-31-2014'.

Junior Supporter

Re: Adding a date filter

Hi Saranya Karthi,



The following query will do the required:

CREATE MULTISET VOLATILE TABLE TESTTABLE
(
WEEKENDINGDATE DATE
)
ON COMMIT PRESERVE ROWS;

INSERT INTO TESTTABLE
SELECT
CALENDAR_DATE
FROM
SYS_CALENDAR.CALENDAR
WHERE
TO_CHAR(CALENDAR_DATE,'YYYY') IN ('2014','2013')
AND CALENDAR_DATE >= DATE '2013-11-01';

SELECT
A.*
FROM
TESTTABLE A
, (SELECT MAX(WEEKENDINGDATE) AS MAX_WEEKENDINGDATE FROM TESTTABLE) B
WHERE
WEEKENDINGDATE BETWEEN ADD_MONTHS(MAX_WEEKENDINGDATE,-12) + 1 AND MAX_WEEKENDINGDATE;

Please let me know in case of any issues.

Thanks,

Rohan Sawant