SQL volume report shell script

vol.sh

#!/bin/bash
#
#
# this script is not for you, it's not validated.
#
# "tried" on ***REMOVED***
#
# written in a hurry to solve a problem. lots of IO's exist.
#
# note script takes ~15 minutes to pull data for one month
# do not be alarmed if nothing happens for a while when running the script
#
# this script pulls volume data using image_location by month acquired.
# it outputs YYYYMM, exam count, modality, and megabytes.
# the date range specified is acquisition_dttm, not study_dttm.
#
# usage scriptname.sh DBSERVER START_DATE END_DATE
#
# sample ./scriptname.sh LOCAL 20141201 20150201|grep "^ [0-9]"
#	to output only the results and not column names/timers
#
# no need to look past here
#basename=`basename $0`
dbserver=$1
startDate=$2
endDate=$3
#make sure the user running this script is sybase
if [ ! `/usr/bin/whoami`="***REMOVED***" ]; then
        echo "You must be logged in as ***REMOVED*** to use this utility, exiting..."
        exit 1
fi
#make sure the user specified which ***REMOVED*** server to connect to
if [ -z $dbserver ]; then
        echo "You must specify the server name as the first input parameter, exiting..."
        echo "Hint: choose from $(egrep -w "***REMOVED***" ***REMOVED***"|tr '\n' ' '|sed '$s/ $/\n/'
        exit 1
fi
#make sure the dates are valid
date -d $startDate > /dev/null 2>&1
if [ $? -ne 0 ]; then
        echo "The start date ($startDate) is invalid, exiting..."
        exit 1
fi
date -d $endDate > /dev/null 2>&1
if [ $? -ne 0 ]; then
        echo "The end date ($endDate) is invalid, exiting..."
        exit 1
fi
#make sure the db server is valid
egrep -w ***REMOVED*** > /dev/null 2>&1
if [ $? -ne 0 ]; then
        echo "The server specified is invalid, exiting..."
        echo "Hint: choose from $(egrep -w "***REMOVED***" ***REMOVED***"|tr '\n' ' '|sed '$s/ $/\n/'
        exit 1
fi

increment=month
***REMOVED***
***REMOVED***
maxusersindb=25
fmtstartDate=$(date +"%y%m%d" -d "$startDate")
fmtendDate=$(date +"%y%m%d" -d "$endDate")

#echo $startDate
#echo $fmtstartDate
#echo $endDate
#echo $fmtendDate


#find out if the server has a lot of people connected to it
loggedincnt=$(isql -HVOLSCRIPT -w1000 ***REMOVED*** ***REMOVED*** ***REMOVED*** -***REMOVED*** <<!| grep affected | tr -cd '[[:digit:]]'
sp_who
go
exit
!)
if [ $loggedincnt -ge $maxusersindb ]; then
        echo "This database seems to be too busy for this script to run ($loggedincnt connections)."
        echo "Try MAINT or the inactive DB, exiting..."
        echo "Hint: choose from $(egrep -w "***REMOVED***" ***REMOVED***)"|tr '\n' ' '|sed '$s/ $/\n/'
        exit 1
fi
while [ $fmtstartDate -le $fmtendDate ]
do
        tmpendDate=$(date +"%Y%m%d" -d "$startDate+1 $increment")
        isql -HVOLSCRIPT -w1000 ***REMOVED*** ***REMOVED*** ***REMOVED*** -S$dbserver <<!
set nocount on
go
declare @fromDate datetime
declare @toDate datetime
set @fromDate = '$fmtstartDate'
set @toDate = '$tmpendDate'
--timer to provide length of time query took to complete
declare @tmrstart datetime
declare @tmrend datetime
declare @tmrdiff int
set @tmrstart = GetDate()
select
        convert(char(4),datepart(yy,dateadd(ss,-(inv_acq_time),'2030.01.01')))+
        right('0'+convert(varchar(2),datepart(mm,dateadd(ss,-(inv_acq_time),'2030.01.01'))),2)
                        as 'YYYYMM',
          count(distinct e.exam_ckey) as 'EXAM CNT',
          convert (char(6),  ep.modality_code) as 'MOD',
          sum(distinct convert(float, il.image_size))/1024/1024  as 'MBytes'
from
          ***REMOVED*** e,
          ***REMOVED*** ep,
          ***REMOVED*** eq,
          ***REMOVED*** es,
          ***REMOVED*** s,
          ***REMOVED*** i,
          ***REMOVED*** il
where
          s.equipment_ckey=eq.equipment_ckey
          and e.procedure_ckey=ep.procedure_ckey
          and e.exam_ckey=es.exam_ckey
          and es.series_ckey=s.series_ckey
          and s.series_ckey=i.series_ckey
          and i.image_ckey=il.image_ckey
          and s.study_ckey=il.study_ckey
          and e.inv_acq_time between
                                                                  datediff(ss, @toDate, '2030.01.01')
                                                          and datediff(ss, @fromDate, '2030.01.01')
        --  and e.exam_ckey=8429552
        --  and ep.modality_code='CT'
group by
          convert (char(6),  ep.modality_code),
          convert(char(4),datepart(yy,dateadd(ss,-(inv_acq_time),'2030.01.01')))+
                right('0'+convert(varchar(2),datepart(mm,dateadd(ss,-(inv_acq_time),'2030.01.01'))),2)
        --having count(e.exam_ckey)>1
order by
          convert(char(4),datepart(yy,dateadd(ss,-(inv_acq_time),'2030.01.01')))+
                right('0'+convert(varchar(2),datepart(mm,dateadd(ss,-(inv_acq_time),'2030.01.01'))),2)
set @tmrend = GetDate()
print ''
print 'Date range: from %1! to %2!', @fromDate, @toDate
set @tmrdiff = datediff(mi,@tmrstart,@tmrend)
print 'Execution time: %1! minutes', @tmrdiff
go
!
  startDate=$(date +"%Y%m%d" -d "$startDate+1 $increment")
  fmtstartDate=$(date +"%y%m%d" -d "$startDate")
done