All Forums Database
lindabii 2 posts Joined 05/16
13 May 2016
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.
 
 
 

dnoeth 4628 posts Joined 11/04
14 May 2016

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 

 
 

Dieter

lindabii 2 posts Joined 05/16
17 May 2016

Thanks a lot for the input.
Good to learn more on the date functions.
Appreciate it.

You must sign in to leave a comment.