To pass one month range in sql script

UNIX for Advanced & Expert Users
1

Hi Guys,

i am having .sql script which inserts data from one table to another table based on date condition, i need to pass range on based on how many number of months, for e.g

set timing on;
 
whenever sqlerror exit failure;
 
 
 
spool myscript.log append
 
 
accept start_date char prompt 'Enter start date ( DD-MON-YYYY ) : '
accept   end_date char prompt 'Enter end date ( DD-MON-YYYY ) : '
 
 
prompt start date: &start_date
prompt   end date: &end_date
 
 
PROMPT >> Inserting records into master
 
INSERT INTO master t
SELECT /*+ PARALLEL(t1,8) */ * FROM T1 t1 where date_col between '&start_date' and '&end_date';
commit;
 
undef start_date
undef  end_date
exit;

i need run this script by passing start date and end date for eg 01-JAN-2005 to 30-APR-2005 this range will be fixed, only thing is i need to pass in sql script like this it should split accordingly to one month , the script should process only one month data once completed next month

@myscript 01-JAN-2005 31-JAN-2005
@myscript 01-FEB-2005 28-FEB-2005
@myscript 01-MAR-2005 31-MAR-2005
@myscript 01-APR-2005 30-APR-2005

, also if i give 2 months gap then it should consider first range as 01-JAN-2005 28-FEB-2005 and 01-MAR-2005 to 30-APR-2005, by default the gap should be always one month difference.

2

If it was me, I would do "where TRUNC(date_col, 'MM') = '1-APR-2005' ". That way you only need to pass in one date. Also if you use Oracle you can create a function based index on TRUNC(date_col) if you want. You then don't need to know the last day of the month. Another way to do that is to add a month to the first of the month and subtract a day. "ADD_MONTH('1-APR-2005', 1)-1 ". still you only need to pass in one date and will get a range scan if you have that column as the leading edge of an index.

3

i have achieved something like below

SELECT /*+ PARALLEL(t1,8) */ * FROM T1 t1 where date_col between ADD_MONTHS(TO_DATE('&start_date','DD-MON-YYYY'),(i-1)) and ADD_MONTHS(TO_DATE('&start_date','DD-MON-YYYY'),i);

But i wanted to achieve in scripting something like passing how month difference the range should be along with that after each insert i.e range it should prompt from the user that so on so range is completed should it proceed with next range displaying the range and start processing the ranges. Like this till it reaches the final date

4

Guys can any one advise??

5

You can try to use getopts where someone
passes in a date and a flag. Such as:

http://www.unix.com/shell-programming-and-scripting/180817-bash-getopts-command-help.html

  • -d '01-01-2016' -- one day
  • -w '01-01-2016' -- one week
  • -m '01-01-2016' -- one month
  • -y '01-01-2016' -- one year

You would then make sure that only one of the flags was passed in, and calculate two
dates based on the flag and use those dates as your low and high dates. You can do
that using the date functions in Oracle.

6

Thanks, can you guide me how i can achieve that

7

Here is some sample code.

#!/bin/bash

setDateParam() {
if [ ! -z "$2" ]
then
   echo "You can only pass in one parameter: -d, -w, -m, -y"
   exit 1
fi

export DATE_FLAG=$1
}


while getopts "dwmy" Option
do
   case $Option in
      d ) setDateParam "D"    "${DATE_FLAG}"  ;;
      w ) setDateParam "W"    "${DATE_FLAG}"  ;;
      m ) setDateParam "MM"   "${DATE_FLAG}"  ;;
      y ) setDateParam "YYYY" "${DATE_FLAG}"  ;;
      * )
         echo "You must pass in one parameter: -d, -w, -m, -y"
         exit 1 ;;
   esac
done

echo "DATE_FLAG: ${DATE_FLAG}"
8

How can i pass dates in my query

INSERT INTO master t
SELECT /*+ PARALLEL(t1,8) */ * FROM T1 t1 where date_col between '&start_date' and '&end_date';