1

I'm using PostgreSQL. I have a table which stores data related to some orders. It has four to five columns, including:

cust_code char varying (20),
sugg_order integer,
ordered_qty integer,
date date

Now I want to find all the rows which occur within a specific time frame. Let us assume that this time frame starts on 1st of April every year and ends on 31st of March next year (like 2017-04-01 to 2018-03-31 and 2018-04-01 to 2019-03-31 and so on).

Below are the examples of the output which I want from the query:

  • If I run this query on 2018-08-01 then I need all the data from 2018-04-01 to 2018-08-01.

  • If I run this query on 2019-03-01 then I need all the data from 2018-04-01 to 2019-03-01.

  • If I run this query on 2019-05-01 then I need all the data from 2019-04-01 to 2019-05-01

In other words, I need all data between today (the day I run the query) and the latest (most recent) 1st of April before today.

I need a query which will work for any year and I cannot get the start date of the time frame from outside.

  • Would it also be correct that on 2018-05-09 you'd want all data from 2018-04-01 to 2018-05-09? or would it still be 2018-04-01 to 2018-05-01? – RDFozz May 9 '18 at 17:02
  • 1
    I attempted to clarify your requirements based on my understanding of them; please feel free to roll back the edit if you think I got them wrong. – mustaccio May 9 '18 at 17:55
  • It should be from 2018-04-01 to 2018-05-09 (all the date in between those two dates). – Neeraj Wadhwa May 9 '18 at 17:56
  • I've completely revised my answer and I'm now 100% sure that it gives the correct answer for whatever the data and year may be. You might want to take a look? – Vérace May 10 '18 at 16:49
3

I think you want something like this:

with param (date, year) as
  ( select 
        @date_param,
        extract(year from @date_param)
        - case when extract(month from @date_param) < 4 then 1 else 0 end
  )
select t.*
from table_name as t, param as p
where t.date >= make_date(p.year, 4, 1) 
  and t.date <= p.date ; 
| improve this answer | |
  • Can you please explain the query? I am not very familiar with PostgreSQL. – Neeraj Wadhwa May 10 '18 at 4:51
  • I modified the above query just a bit and it worked for me. – Neeraj Wadhwa May 10 '18 at 12:11
3

In order to answer this, I did the following:

Created the table:

CREATE TABLE cust (cust_code VARCHAR(20), sugg_order integer, ordered_qty integer, ord_date date);

Created some sample data (note how it spans from early 2017 - before Apr 1st) to after today.

INSERT INTO cust VALUES ('cust_1', 45, 450, '2018-01-28');
INSERT INTO cust VALUES ('cust_1', 45, 350, '2018-02-28');
INSERT INTO cust VALUES ('cust_1', 45,  50, '2018-03-28');
INSERT INTO cust VALUES ('cust_1', 45, 451, '2018-04-01');
INSERT INTO cust VALUES ('cust_1', 45, 150, '2018-04-28');
INSERT INTO cust VALUES ('cust_1', 45,  87, '2018-05-28');
INSERT INTO cust VALUES ('cust_1', 45,  93, '2018-06-28');
INSERT INTO cust VALUES ('cust_1', 45,  56, '2018-07-28');
INSERT INTO cust VALUES ('cust_1', 45, 400, '2018-08-28');
INSERT INTO cust VALUES ('cust_1', 45, 234, '2017-01-28');
INSERT INTO cust VALUES ('cust_1', 45, 432, '2017-02-28');
INSERT INTO cust VALUES ('cust_1', 45, 657, '2017-03-28');
INSERT INTO cust VALUES ('cust_1', 45,  43, '2017-04-28');
INSERT INTO cust VALUES ('cust_1', 45,  32, '2017-05-28');
INSERT INTO cust VALUES ('cust_1', 45, 111, '2017-06-28');

WITH cte1 AS
(
  SELECT
    CASE
      WHEN DATE_PART('MONTH', NOW()) >= 4 
        THEN TO_DATE(TO_CHAR(DATE_PART('YEAR', NOW())::INT,     '9999')     || '04' || '01', 'YYYYMMDD')
        ELSE TO_DATE(TO_CHAR(DATE_PART('YEAR', NOW())::INT - 1, '9999')  || '04' || '01', 'YYYYMMDD')
    END AS start_tax_year
)
SELECT * FROM cust, cte1
WHERE ord_date <= NOW() AND ord_date >= cte1.start_tax_year
ORDER BY ord_date

(There's a dbfiddle here)

I then ran 2 queries varying this line:

WHEN DATE_PART('MONTH', NOW()) >= 4 -- also used < 4 
                                   -- to simulate a previous year.

Results with >= 4

cust_code sugg_order ord_qty    ord_date start_tax_year
   cust_1         45     450  2018-04-01     2018-04-01
   cust_1         45     150  2018-04-28     2018-04-01

Only two records - which is to be expected since we're only at the beginning of the tax year - just into May. A check shows that there are, in fact, only two records between today (May 10th) and Apr 1st.

Results with < 4

cust_code sugg_order ord_qty    ord_date start_tax_year
   cust_1         45      43  2017-04-28     2017-04-01
   cust_1         45      32  2017-05-28     2017-04-01
   cust_1         45     111  2017-06-28     2017-04-01
   cust_1         45     450  2018-01-28     2017-04-01
   cust_1         45     350  2018-02-28     2017-04-01
   cust_1         45      50  2018-03-28     2017-04-01
   cust_1         45     451  2018-04-01     2017-04-01
   cust_1         45     150  2018-04-28     2017-04-01

8 records - again to be expected, as the period between now and Apr 1st 2017 covers most of the data. If the date as sometime late in 2017, it would pick up the records between then and Apr 1st 2017.

The query is rather convoluted:

THEN TO_DATE(TO_CHAR(DATE_PART('YEAR', NOW())::INT, '9999')     || '04' || '01', 'YYYYMMDD')

The whole point of the CTE is to get April 1st of the correct tax year - i.e. the one we're in at the moment, even if we've gone into the next calendar year.

1) get DATE_PART from NOW() cast as an INT when we're beyond April first in the year where the query is being performed. This becomes

DATE_PART('YEAR', NOW())::INT - 1, '9999')

if the month part of the current year is < 4, it's before April, so you have to go back to April 1st of the previous year - which is where the -1 comes in.

2) once you've got the correct year for April 1st, then all the rest is the same. You then use TO_CHAR to transform this into a string and then using the double pipe characters (||) to concatenate the month (04) and day (01) to the correct year. Then use TO_DATE to convert the correct string (20180401) using the format string 'YYYYMMDD'.

3) then use the CTE to feed this value into the main query - obtain the desired records. Et voilà.

| improve this answer | |
  • The query suggested by you has given me an idea for another query. – Neeraj Wadhwa May 11 '18 at 6:20
  • Can you give me an example of using with clause twice in the same SELECT query? Like you are using it once and I need a second CTE so that I can get some more data and instead of just one cust_id, I can get a list of them. – Neeraj Wadhwa May 11 '18 at 6:26
  • And what's wrong with putting something like AND cust_id IN (... list of cust_ids...) into the WHERE clause? p.s. +1 for your more elegant solution. – Vérace May 11 '18 at 7:45
  • Exactly what I am trying to do here but we need to get cust_ids dynamically and for calculating those I have a separate query. I am thinking of using that query as second CTE. I need suggestions for this. – Neeraj Wadhwa May 11 '18 at 7:49
  • I think that might be a second question. Can you provide a fiddle with sample data and your desired result and then point back to this question to help give context? Take a look here. – Vérace May 11 '18 at 7:54
2

The below query worked for my problem:

WITH get_year AS (SELECT
  CASE
  WHEN extract(MONTH FROM current_timestamp) < 4
  THEN extract(YEAR FROM  current_timestamp) - 1
  ELSE extract(YEAR FROM  current_timestamp)
  END)

  SELECT sugg_order, ordered_qty, date from table_1 t, get_year
  WHERE t.cust_id = $1
  AND t.date >= make_date(get_year.date_part::integer, 4, 1) and t.date <= current_timestamp
  ORDER BY t.date DESC
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.