lmontealegre
lmontealegre
Reputation Top 10%
lmontealegre montealegre
1 Snippet  (615th place)
Published
1 Channel
Created
2 Channels
Following
30 points  (1722nd place)
Reputation
Junior Code Generator
Junior Publisher
Junior Popular Coder
Junior Autobiographer

Recent Snippets See all snippets by lmontealegre

public by lmontealegre  631  1  4  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
'''
;