#!/bin/sh
###############################################################################
# Program: acct_profyle
# Purpose: This program lets the user, for any selected month of tradesrec, create a
# table that shows total trades for each unique combination of
# branch and account, grouped by BOT, SLD, qwx, BOT (solicited),
# SLD (solicited) and qwx (solicited).
# For example, the command to create the table ${DEST_TABLE} for
# the month of Jan 1999, simply enter the month and date in YYYYMM
# format at the command prompt:
#
# ddcompv2 $ acct_profyle 199901
#
# Modified by Date Reason
# ----------- -------- ------------------------------------------------------
# Bill StJohn 02/10/00 Original
# P.Smith 02/18/00 Changed DCDTYPE for Calendar Month.
# Bill StJohn 03/03/00 Added account switch function.
# Bill StJohn 03/06/00 Renamed some variables. Replaced #TMPXXX tables with #TMP_DATA
#
###############################################################################
DCDTYPE=CALENDAR_MONTH_BATCH # Set the type of DCD to use.
PREVDCDTYPE=PREV_CALENDAR_MONTH_BATCH # Set the type of DCD to use.
LOGDIR= # Alternate directory to use for log file.
. ${BATCH}/common/ComFuncDir
Initprog "$@" # Pass any parameters passed to this program.
SERVER="COMPL_PROD"
DATABASE="dvl_db"
DEST_TABLE="acct_profyle"
SOURCE_TABLE="compl..tradesrec"
ACCT_UPDATE="compl..ACCT_UPDATE"
TEMPTABLE="TEMP_acct_profyle"
DATAFILEOUT="acct_profyle_flatfyle"
PARAMETERS="Parameters"
Start_log
Echo_text "Beginning ${BASENAME}"
# Make starting and ending datecarddates from command line input
YYYYMM=$1
DCDBEFORE="${YYYYMM}00"
DCDAFTER="${YYYYMM}99"
# Added by PLK. To pass monthly parameters.
Do_select -S ${SERVER} <<SQL_END
select Param_Int
from ${PARAMETERS}
where Param_Name = "${DCDTYPE}"
go
SQL_END
DCDAFTER=${VALUE}
Do_select -S ${SERVER} <<SQL_END
select Param_Int
from ${PARAMETERS}
where Param_Name = "${PREVDCDTYPE}"
go
SQL_END
DCDBEFORE=${VALUE}
#Drop TEMPTABLE (The following line is identical to one near the end of this script)
Drop_SQL_table ${SERVER} ${DATABASE} ${TEMPTABLE} SHOULD_NOT_BE_THERE
#Drop account profile table (which gets re-created and populated later in this script)
Drop_SQL_table ${SERVER} ${DATABASE} ${DEST_TABLE} SHOULD_NOT_BE_THERE
Do_sql -S ${SERVER} <<SQL_END
use ${DATABASE}
go
-- For the user-selected YYYYMM month in taable tradesrec, take columns branch, account, activity
-- and solicited, then put them into a tempporary table named ${TEMPTABLE}
select branch, account, activity, solicited
into ${TEMPTABLE}
from ${SOURCE_TABLE}
where key_date > ${DCDBEFORE} and key_date <= ${DCDAFTER}
--and custname like 'Z%'
go
-----------------------------------------------------------
-- change all old branch and account numberrs in temp table to new ones
update ${TEMPTABLE}
set ${TEMPTABLE}.branch = ${ACCT_UPDATE}.new_branch,
${TEMPTABLE}.account = ${ACCT_UPDATE}.new_account
from ${TEMPTABLE}, ${ACCT_UPDATE}
where ${TEMPTABLE}.branch = ${ACCT_UPDATE}.old_branch
and ${TEMPTABLE}.account = ${ACCT_UPDATE}.old_account
go
-- Is the following line needed?
-- ROWSUPDATED=${ROWSAFFECTED}
------------------------------------------------------------
-- Create acct_profyle table.
create table ${DEST_TABLE}
( branch int not null,
account int not null,
bot int not null,
botsol int not null,
sld int not null,
sldsol int not null,
qwx int not null,
qwxsol int not null)
go
------------------------------------------------------------
-- From ${TEMPTABLE}, insert into the trunccated (empty) table acct_profyle
-- all distinct (unique) combinations of brranch and account number.
-- Then add six new fields, all containing the integer zero (0).
insert ${DEST_TABLE}
select distinct branch, account, 0, 0, 0, 0, 0, 0
from ${TEMPTABLE}
go
---------------------------------------------------------------------
-- Using data from temp table ${TEMPTABLE},,
-- the following section counts, for each ddistinct combination of branch and account,
-- how many times "BOT" appears in the actiivity column,
-- then creates a 3-column temp table namedd #TMP_DATA, which lists
-- branch, account and (in the temp column "updata") the count sum.
select branch, account, "updata" = count(*)
into #TMP_DATA
from ${TEMPTABLE}
where activity = "BOT"
group by branch, account
go
-- For each combination of brand and accounnt, this section updates
-- the column acct_profyle.bot with the figgure in #TMP_DATA.updata
update ${DEST_TABLE}
set ${DEST_TABLE}.bot = #TMP_DATA.updata
from #TMP_DATA
where ${DEST_TABLE}.branch = #TMP_DATA.branch
and ${DEST_TABLE}.account = #TMP_DATA.account
go
-- The temp table is no longer needed, so tto save space ...
drop table #TMP_DATA
go
---------------------------------------------------------------------
-- Using data from temp table ${TEMPTABLE},,
-- the following section counts, for each ddistinct combination of branch and account,
-- how many times "SLD" appears in the actiivity column,
-- then creates a 3-column temp table namedd #TMP_DATA, which lists
-- branch, account and (in the temp column "updata") the count sum.
select branch, account, "updata" = count(*)
into #TMP_DATA
from ${TEMPTABLE}
where activity = "SLD"
group by branch, account
go
-- For each combination of brand and accounnt, this section updates
-- the column acct_profyle.sld with the figgure in #TMP_DATA.updata
update ${DEST_TABLE}
set ${DEST_TABLE}.sld = #TMP_DATA.updata
from #TMP_DATA
where ${DEST_TABLE}.branch = #TMP_DATA.branch
and ${DEST_TABLE}.account = #TMP_DATA.account
go
-- The temp table is no longer needed, so tto save space ...
drop table #TMP_DATA
go
---------------------------------------------------------------------
-- Using data from temp table ${TEMPTABLE},,
-- the following section counts, for each ddistinct combination of branch and account,
-- how many times "BOT" appears in the actiivity column and "Y" appears in the solicited column
-- then creates a 3-column temp table namedd #TMP_DATA, which lists
-- branch, account and (in the temp column "updata") the count sum.
select branch, account, "updata" = count(*)
into #TMP_DATA
from ${TEMPTABLE}
where activity = "BOT" and solicited = "Y"
group by branch, account
go
-- For each combination of brand and accounnt, this section updates
-- the column acct_profyle.botsol with the figure in #TMP_DATA?.updata
update ${DEST_TABLE}
set ${DEST_TABLE}.botsol = #TMP_DATA.updata
from #TMP_DATA
where ${DEST_TABLE}.branch = #TMP_DATA.branch
and ${DEST_TABLE}.account = #TMP_DATA.account
go
-- The temp table is no longer needed, so tto save space ...
drop table #TMP_DATA
go
---------------------------------------------------------------------
-- Using data from temp table ${TEMPTABLE},,
-- the following section counts, for each ddistinct combination of branch and account,
-- how many times "SLD" appears in the actiivity column and "Y" appears in the solicited column,
-- then creates a 3-column temp table namedd #TMP_DATA, which lists
-- branch, account and (in the temp column "updata") the count sum.
select branch, account, "updata" = count(*)
into #TMP_DATA
from ${TEMPTABLE}
where activity = "SLD" and solicited = "Y"
group by branch, account
go
-- For each combination of brand and accounnt, this section updates
-- the column acct_profyle.sldsol with the figure in #TMP_DATA.updata
update ${DEST_TABLE}
set ${DEST_TABLE}.sldsol = #TMP_DATA.updata
from #TMP_DATA
where ${DEST_TABLE}.branch = #TMP_DATA.branch
and ${DEST_TABLE}.account = #TMP_DATA.account
go
-- The temp table is no longer needed, so tto save space ...
drop table #TMP_DATA
go
---------------------------------------------------------------------
-- Using data from temp table ${TEMPTABLE},,
-- the following section counts, for each ddistinct combination of branch and account,
-- how many times "qwx" appears in the actiivity column,
-- then creates a 3-column temp table namedd #TMP_DATA, which lists
-- branch, account and (in the temp column "updata") the count sum.
select branch, account, "updata" = count(*)
into #TMP_DATA
from ${TEMPTABLE}
where activity = "qwx"
group by branch, account
go
-- For each combination of brand and accounnt, this section updates
-- the column acct_profyle.qwx with the figgure in #TMP_DATA.updata
update ${DEST_TABLE}
set ${DEST_TABLE}.qwx = #TMP_DATA.updata
from #TMP_DATA
where ${DEST_TABLE}.branch = #TMP_DATA.branch
and ${DEST_TABLE}.account = #TMP_DATA.account
go
-- The temp table is no longer needed, so tto save space ...
drop table #TMP_DATA
go
---------------------------------------------------------------------
-- Using data from temp table ${TEMPTABLE},,
-- the following section counts, for each ddistinct combination of branch and account,
-- how many times "qwxSOL" appears in the aactivity column,
-- then creates a 3-column temp table namedd #TMP_DATA, which lists
-- branch, account and (in the temp column "updata") the count sum.
select branch, account, "updata" = count(*)
into #TMP_DATA
from ${TEMPTABLE}
where activity = "qwx" and solicited = "Y"
group by branch, account
go
-- For each combination of brand and accounnt, this section updates
-- the column acct_profyle.qwxsol with the figure in #TMP_DATA.updata
update ${DEST_TABLE}
set ${DEST_TABLE}.qwxsol = #TMP_DATA.updata
from #TMP_DATA
where ${DEST_TABLE}.branch = #TMP_DATA.branch
and ${DEST_TABLE}.account = #TMP_DATA.account
go
-- #TMP_DATA will drop after SQL_END below
SQL_END
# Convert integer fields to character fields (mostly 7 characters in length), then
# save flat file in
# $GATEWAYOUT (ddcompv2:/develop/log/compl/compl/compl/batch/YYYY-MM/acct_profyle_flatfyle)
#Do_sql -S ${SERVER} <<SSQL_END
Do_select -S ${SERVER} <<SELECT_END > /dev/null
use ${DATABASE}
go
select
-- Output one more digit than required to llet us check for overflow.
-- Place a space between each field
right( replicate("0", 4) + convert(varchar, branch ), 4) +
right( replicate("0", 6) + convert(varchar, account), 6) + replicate(" ", 1) +
right( replicate("0", 7) + convert(varchar, bot ), 7) + replicate(" ", 1) +
right( replicate("0", 7) + convert(varchar, botsol ), 7) + replicate(" ", 1) +
right( replicate("0", 7) + convert(varchar, sld ), 7) + replicate(" ", 1) +
right( replicate("0", 7) + convert(varchar, sldsol ), 7) + replicate(" ", 1) +
right( replicate("0", 7) + convert(varchar, qwx ), 7) + replicate(" ", 1) +
right( replicate("0", 7) + convert(varchar, qwxsol ), 7)
from ${DEST_TABLE}
go
SELECT_END
#SQL_END
echo "${VALUE}" > ${GATEWAYOUT}/${DATAFILEOUT}
# Drop temp table
Drop_SQL_table ${SERVER} ${DATABASE} ${TEMPTABLE}
Write_log -s ${SERVER} -d ${DATABASE} -t ${DEST_TABLE} -r ${ROWSAFFECTED}
Echo_text "${BASENAME} complete."
Endprog
#End program