lmontealegre
lmontealegre
Reputation Top 10%
lmontealegre montealegre
1 Snippet  (515th place)
Published
1 Channel
Created
2 Channels
Following
Aug 23, 2017
Last Visit
Dec 7, 2014
Registered
30 points  (1064th place)
Reputation
Junior Code Generator
Junior Publisher
Junior Popular Coder
Junior Autobiographer

Recent Snippets See all snippets by lmontealegre

public by lmontealegre created Aug 22, 2017  152  0  3  0

CONSTANT Definitions for Database server and SQL scripts

Standard for defining connections and SQL definitions
#=================================================================
# Database Server Connection String
# Database = AGENTS  Server = <IP>
#=================================================================
CNN_AGENTS = {'Server':'<IP>', 'User':'<Name>', 'Password':'<Pwd>', 'Database':'<DBName>'}

#=================================================================
# SQL Script Definition to Get Gran Total Agent counts
# Database = AGENTS  Server = <IP>
#=================================================================
SQL_WR0TOTALCNTS = '''
Select
  Count(SOL.SOL_KEY) As NoRec,
  Count(Distinct SOL.AGT_SOL_NO) As CntSOL,
  Sum(SOL.AGT_SOL_NOFILE) As SumSOL_NF,
  Sum(SOL.AGT_SOL_DWLERR) As SumSOL_DWLERR,
  Sum(SOL.AGT_SOL_OK) As SumSOL_OK,
  Sum(SOL.AGT_SOL_DWLOK) As SumSOL_DWLOK,
  Min(Cast(SOL.AGT_DTE_POSTDATE As datetime)) As Min_POSTDATE,
  Max(Cast(SOL.AGT_DTE_POSTDATE As datetime)) As Max_POSTDATE,
  DateDiff(day, Min(Cast(SOL.AGT_DTE_POSTDATE As datetime)), Max(Cast(SOL.AGT_DTE_POSTDATE As datetime))) As DateRange,
  Count(Distinct Cast(SOL.AGT_DTE_POSTDATE As datetime)) As ProcDates,
  Sum(Case When Len(IsNull(SOL.AGT_SOL_DWLPATH, '')) > 0 Then 1 Else 0
  End) As DwlOK,
  Sum((Convert(bigint,SOL.AGT_SOL_DWLBYTE)) / (1048576)) As MB,
  Count(Distinct SOL.AGT_DTE_EVT) As Cnt_RAN_EVT
From
  AGENTS.dbo.DIBBS_AGN_SOL SOL
'''

#=================================================================
# SQL Script Definition to Get Gran Total Agent counts
# Database = AGENTS  Server = <IP> To be designed
#=================================================================
SQL_SelectedDates = '''
Select
  Count(SOL.SOL_KEY) As NoRec,
  Count(Distinct SOL.AGT_SOL_NO) As CntSOL,
  Sum(SOL.AGT_SOL_NOFILE) As SumSOL_NF,
  Sum(SOL.AGT_SOL_DWLERR) As SumSOL_DWLERR,
  Sum(SOL.AGT_SOL_OK) As SumSOL_OK,
  Sum(SOL.AGT_SOL_DWLOK) As SumSOL_DWLOK,
  Min(Cast(SOL.AGT_DTE_POSTDATE As datetime)) As Min_POSTDATE,
  Max(Cast(SOL.AGT_DTE_POSTDATE As datetime)) As Max_POSTDATE,
  DateDiff(day, Min(Cast(SOL.AGT_DTE_POSTDATE As datetime)), Max(Cast(SOL.AGT_DTE_POSTDATE As datetime))) As DateRange,
  Count(Distinct Cast(SOL.AGT_DTE_POSTDATE As datetime)) As ProcDates,
  Sum(Case When Len(IsNull(SOL.AGT_SOL_DWLPATH, '')) > 0 Then 1 Else 0
  End) As DwlOK,
  Sum((Convert(bigint,SOL.AGT_SOL_DWLBYTE)) / (1048576)) As MB,
  Count(Distinct SOL.AGT_DTE_EVT) As Cnt_RAN_EVT
From
  AGENTS.dbo.DIBBS_AGN_SOL SOL
'''
;