#! /bin/sh SDATE="$1" EDATE="$2" ## Testing #SDATE="01/04/2010" #EDATE="30/04/2010" #----------------------------------------------------- # convert SDATE to FROMDATE FROMDATE=`awk -v "ADATE=$SDATE" -v "sep=-" ' BEGIN { dd = 0 + substr(ADATE,1,2); mm = 0 + substr(ADATE,4,2); yyyy = 0 + substr(ADATE,7,4); YYYYMMDD = sprintf("%04d%s%02d%s%02d", yyyy, sep, mm, sep, dd); print YYYYMMDD exit }'` echo "FROMDATE=[$FROMDATE]" # convert EDATE to TODATE TODATE=`awk -v "ADATE=$EDATE" -v "sep=-" ' BEGIN { dd = 0 + substr(ADATE,1,2); mm = 0 + substr(ADATE,4,2); yyyy = 0 + substr(ADATE,7,4); YYYYMMDD = sprintf("%04d%s%02d%s%02d", yyyy, sep, mm, sep, dd); print YYYYMMDD exit }'` echo "TODATE=[$TODATE]" MMYYYY=`SDATEtoMMYYYY $SDATE` echo "MMYYYY = [$MMYYYY]" billperiod=`MMYYYYtobillperiod $MMYYYY _` echo "billperiod = [$billperiod]" #----------------------------------------------------- #/* ---------------------------- */ #/* spServiceListExtensionReport */ #/* ---------------------------- */ #CREATE PROCEDURE [dbo].[spServiceListExtensionReport] #@p_FromDate varchar(10), #@p_ToDate varchar(10), #@p_CustomerList varchar(8000) /* comma separated Customer.ID list */ # returns... # Extension, # LastName AS [Last Name], # FirstName AS [First Name], # Tec, # Division AS [Ship To], # dbo.tCustomer.FullName AS Customer, # [Section], # Title, # Email, # Site AS PABX, # ExtensionGroup AS [Service ID], # InstallationDate AS [Installation Date], # CAST(YEAR(InstallationDate) AS VARCHAR(4)) + ''/'' + RIGHT(''0'' + CASTMONTH(InstallationDate) AS VARCHAR(2)), 2) AS [Installation Period], # TerminationDate AS [Termination Date], # CAST(YEAR(TerminationDate) AS VARCHAR(4)) + ''/'' + RIGHT(''0'' + CAST(ONTH(TerminationDate) AS VARCHAR(2)), 2) AS [Termination Period] # outfile="indata/$billperiod/sler.unl" tbssql >"$outfile" <"$outfile" <"$outfile" <