Need to exclude data in a specific month

Database

Need to exclude data in a specific month

Hello there,

I need to exclude data in a specific month, say 2005/8.

My query was like this

SELECT All_Sales.DaysNum, All_Sales.AvgRev, All_Sales.SpecificMon, All_Sales.Store

FROM

(SELECT COUNT(DISTINCT(t.saledate))AS DaysNum, sum(t.amt)/DaysNum AS AvgRev,

          EXTRACT(YEAR from t.saledate)|| EXTRACT(MONTH from t.saledate) AS SpecificMon,

          t.store

FROM trnsact t

WHERE t.stype='P'

GROUP BY SpecificMon,t.store) AS All_Sales

WHERE All_Sales.DaysNum>=20 and All_Sales.SpecificMon <> '2005 8';

I tested the subquery first, the SpecificMon column has the date format as '2005 8'. But when I run the above query, the results still has the data with the SpecificMon of '2005 8'.

How can I exclude the data in the month of '2005 8' in this case?

Any inputs will be appreciated.

2 REPLIES
Junior Contributor

Re: Need to exclude data in a specific month

When you have a close look at the data you should notice that it's not '2005 8', but '       2016          5'. Instead of casting two times to a string you better simply GROUP BY on both columns or combine them into a number using EXTRACT(YEAR from t.saledate) * 100 + EXTRACT(MONTH from t.saledate)

Additonally you should filter this month before aggregation and use HAVING instead of a Derived Table:

SELECT COUNT(DISTINCT(t.saledate))AS DaysNum, 
sum(t.amt)/DaysNum AS AvgRev,
EXTRACT(YEAR from t.saledate) AS SpecificYear,
EXTRACT(MONTH from t.saledate) AS SpecificMon,
t.store
FROM trnsact t
WHERE t.stype='P'
AND t.saledate NOT BETWEEN DATE '2005-05-01' AND LAST_DAY(DATE '2005-05-01')
GROUP BY SpecificYear, SpecificMon,t.store
HAVING All_Sales.DaysNum>=20

Re: Need to exclude data in a specific month

Thanks a lot for the input.

Good to learn more on the date functions.

Appreciate it.