Skip to Main Content
  • Questions

  • Identify batch number for given dates.

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: November 28, 2018 - 6:20 pm UTC

Last updated: December 11, 2018 - 3:16 pm UTC

Version: 11 g

Viewed 1000+ times

You Asked

Hi,

I need a sql query to calculate batch_number based on the dates. For the same name and country we need to show same batch number up to 12 months. For next batch we have to start from that date and consider upto 12 months as next batch.

Here is the sample data. I have manually added batch number in below query but we have to calculate them dynamically using sql.

create table test_batches as
select 'a' name , 'australia' country,to_date('01-Jan-2018') batch_Date, 'B1' batch_number from dual
union all
select 'a' name , 'australia' country,to_date('01-Jul-2018'), 'B1' batch_number from dual 
union all
select 'a' name , 'australia' country,to_date('01-Nov-2018'), 'B1' batch_number from dual
union all
select 'a' name , 'australia' country,to_date('31-Dec-2018'), 'B1' batch_number from dual
union all
select 'a' name , 'australia' country,to_date('10-Mar-2019'), 'B2' batch_number from dual
union all
select 'a' name , 'australia' country,to_date('01-Oct-2019'), 'B2' batch_number from dual
union all
select 'a' name , 'australia' country,to_date('01-Jan-2020') , 'B2' batch_number from dual
union all
select 'a' name , 'australia' country,to_date('09-Mar-2020'), 'B2' batch_number from dual
union all
select 'a' name , 'australia' country,to_date('10-Mar-2020'), 'B3' batch_number from dual
union all
select 'a' name , 'australia' country,to_date('01-Aug-2020'), 'B3' batch_number from dual
union all
select 'b' name , 'australia' country,to_date('01-Jan-2018') , 'B1' batch_number from dual


For user A
batch1 start from '1-Jan-2018' and for upto 12 months we have to show data as B1.
batch2 starts from '10-Mar-2019' as it is first date beyond 12 months, this batch continue for the next 12 months upto '09-Mar-2020'
batch3 will start from '10-Mar-2020'

For user B
we have to follow the same approach as above.

tried using function but it has performance issues, so need to do it in a query. Please help.

and Chris said...

I'm confused. Are the dates you've shown what's in your real data? Or do you just have the first date for each batch, then need to generate everything for the next 12 months?

Anyway, the basic idea is:

- Have a table of dates you want to display; you can generate this on the fly or use a real table. I've restricted to month starts to keep the data set small
- Get the first and last date you want to display for each name, batch, and country. If you only have one the initial row, just add 12 months and subtract a day. If you've got dates in between you'll need to do something more exotic
- Join these ranges to the dates that fall beteen the start and end

e.g.:

with dates as (
  select add_months ( date'2018-01-01', level - 1 ) mth
  from   dual
  connect by level <= 36
), ranges as (
  select name, country, batch_number, 
         min ( batch_date ) st_dt, 
         add_months ( min ( batch_date ), 12 ) - 1 en_dt
  from   test_batches
  group  by name, country, batch_number
)
  select mth, name, country, batch_number
  from   dates
  join   ranges
  on     mth between st_dt and en_dt
  order  by name, mth;

MTH           NAME   COUNTRY     BATCH_NUMBER   
01-JAN-2018   a      australia   B1             
01-FEB-2018   a      australia   B1             
01-MAR-2018   a      australia   B1             
01-APR-2018   a      australia   B1             
01-MAY-2018   a      australia   B1             
01-JUN-2018   a      australia   B1             
01-JUL-2018   a      australia   B1             
01-AUG-2018   a      australia   B1             
01-SEP-2018   a      australia   B1             
01-OCT-2018   a      australia   B1             
01-NOV-2018   a      australia   B1             
01-DEC-2018   a      australia   B1             
01-APR-2019   a      australia   B2             
01-MAY-2019   a      australia   B2             
01-JUN-2019   a      australia   B2             
01-JUL-2019   a      australia   B2             
01-AUG-2019   a      australia   B2             
01-SEP-2019   a      australia   B2             
01-OCT-2019   a      australia   B2             
01-NOV-2019   a      australia   B2             
01-DEC-2019   a      australia   B2             
01-JAN-2020   a      australia   B2             
01-FEB-2020   a      australia   B2             
01-MAR-2020   a      australia   B2             
01-APR-2020   a      australia   B3             
01-MAY-2020   a      australia   B3             
01-JUN-2020   a      australia   B3             
01-JUL-2020   a      australia   B3             
01-AUG-2020   a      australia   B3             
01-SEP-2020   a      australia   B3             
01-OCT-2020   a      australia   B3             
01-NOV-2020   a      australia   B3             
01-DEC-2020   a      australia   B3             
01-JAN-2018   b      australia   B1             
01-FEB-2018   b      australia   B1             
01-MAR-2018   b      australia   B1             
01-APR-2018   b      australia   B1             
01-MAY-2018   b      australia   B1             
01-JUN-2018   b      australia   B1             
01-JUL-2018   b      australia   B1             
01-AUG-2018   b      australia   B1             
01-SEP-2018   b      australia   B1             
01-OCT-2018   b      australia   B1             
01-NOV-2018   b      australia   B1             
01-DEC-2018   b      australia   B1

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank for the reply

A reader, November 29, 2018 - 6:19 pm UTC

The data which i gave is the actual data we have.We dont have any batch_numbers in table, we have to calculate them on the fly based on difference between the dates. I gave batch numbers for understanding purpose.
Minimum date of a user will be start of Batch1(B1) and upto 12 months from this date we use B1. In the example starting from 01-Jan-2018 to 31-Dec-2018 we have to get batch_number as B1.
If B1 is completed then next minimum date(10-Mar-2019) will be start of B2 and upto 12 months(09-Mar-2020) from this date will be B2 same for B3.
Chris Saxon
November 30, 2018 - 3:26 pm UTC

So you want to generate a number for all dates that fall in a 12-month period for each (name, country) pair? And if there's a gap between the end of one period, the next period starts from whatever the next date in the table is?

A reader, December 01, 2018 - 12:23 pm UTC

Yes you are correct, we need a number for the dates that fall in a 12-month period for each (name, country) pair,
And if there's a gap between the end of one period, the next period starts from whatever the next date in the table.

nemesis

Racer I., December 11, 2018 - 12:11 pm UTC

Hi,

We meet again, my old nemesis match_recognize.. ;)

{code}
select mr.name, mr.country, mr.batch_date, mr.start_bd, mr.end_bd, mr.cf, 'B' || mr.MNO new_batch_number, mr.batch_number old_batch_number
from test_batches MATCH_RECOGNIZE (
PARTITION BY name, country
ORDER BY batch_Date
MEASURES STRT.batch_Date AS start_bd,
LAST(ED.batch_Date) AS end_bd,
MATCH_NUMBER() AS mno,
CLASSIFIER() AS cf
ALL ROWS PER MATCH
--AFTER MATCH SKIP TO LAST DOWN
PATTERN (STRT ED+)
DEFINE
ED AS ED.batch_Date < ADD_MONTHS(STRT.batch_Date, 12)
) MR
order by mr.name, mr.country, mr.batch_date
{code}

Chris Saxon
December 11, 2018 - 3:16 pm UTC

Nemesis?! Pattern matching is perfect for this problem :)

Sadly the OP says they're on 11g. So it's recursive with instead (assuming 11.2):

with rws as (
  select t.*,
         row_number () over ( 
           partition by name, country
           order by batch_date
         ) rn
  from   test_batches t
), grps (
  name, country, batch_date, batch_number, rn, grp, grp_start
) as (
  select r.name, r.country, r.batch_date, r.batch_number, r.rn,
         1 grp,
         batch_date grp_start
  from   rws r
  where  rn = 1
  union  all
  select r.name, r.country, r.batch_date, r.batch_number, r.rn,
         case
           when r.batch_date < add_months ( grp_start, 12 ) then
             g.grp
           else 
             g.grp + 1
         end, 
         case
           when r.batch_date < add_months ( grp_start, 12 ) then
             g.grp_start
           else 
             r.batch_date
         end
  from   grps g
  join   rws r
  on     r.name = g.name
  and    r.country = g.country 
  and    r.rn = g.rn + 1
)
  select * from grps
  order  by name, country, batch_date;

NAME   COUNTRY     BATCH_DATE    BATCH_NUMBER   RN   GRP   GRP_START     
a      australia   01-JAN-2018   B1                1     1 01-JAN-2018   
a      australia   01-JUL-2018   B1                2     1 01-JAN-2018   
a      australia   01-NOV-2018   B1                3     1 01-JAN-2018   
a      australia   31-DEC-2018   B1                4     1 01-JAN-2018   
a      australia   10-MAR-2019   B2                5     2 10-MAR-2019   
a      australia   01-OCT-2019   B2                6     2 10-MAR-2019   
a      australia   01-JAN-2020   B2                7     2 10-MAR-2019   
a      australia   09-MAR-2020   B2                8     2 10-MAR-2019   
a      australia   10-MAR-2020   B3                9     3 10-MAR-2020   
a      australia   01-AUG-2020   B3               10     3 10-MAR-2020   
b      australia   01-JAN-2018   B1                1     1 01-JAN-2018

nemesis 2

Racer I., December 11, 2018 - 12:12 pm UTC

Hi,

select mr.name, mr.country, mr.batch_date, mr.start_bd, mr.end_bd, mr.cf, 'B' || mr.MNO new_batch_number, mr.batch_number old_batch_number
from test_batches MATCH_RECOGNIZE (
         PARTITION BY name, country
         ORDER BY batch_Date
         MEASURES  STRT.batch_Date AS start_bd,
                   LAST(ED.batch_Date) AS end_bd,
                   MATCH_NUMBER() AS mno,
                   CLASSIFIER() AS cf
         ALL ROWS PER MATCH
         --AFTER MATCH SKIP TO LAST DOWN
         PATTERN (STRT ED+)
         DEFINE
           ED AS ED.batch_Date < ADD_MONTHS(STRT.batch_Date, 12)
       ) MR
order by  mr.name, mr.country, mr.batch_date

NAME COUNTRY BATCH_DATE START_BD END_BD CF NEW_BATCH_NUMBER OLD_BATCH_NUMBER
a australia     01.01.2018 01.01.2018  STRT B1 B1 
a australia     01.07.2018 01.01.2018 01.07.2018 ED B1 B1 
a australia     01.11.2018 01.01.2018 01.11.2018 ED B1 B1 
a australia     31.12.2018 01.01.2018 31.12.2018 ED B1 B1 
a australia     10.03.2019 10.03.2019  STRT B2 B2 
a australia     01.10.2019 10.03.2019 01.10.2019 ED B2 B2 
a australia     01.01.2020 10.03.2019 01.01.2020 ED B2 B2 
a australia     09.03.2020 10.03.2019 09.03.2020 ED B2 B2 
a australia     10.03.2020 10.03.2020  STRT B3 B3 
a australia     01.08.2020 10.03.2020 01.08.2020 ED B3 B3


regards,

A reader, December 13, 2018 - 11:21 am UTC

Thank you Chris , Recursive Subquery Factoring worked for us.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.