working on it ...

Filters

Explore Public Snippets

Sort by

Found 1 snippet

    public by lmontealegre  616  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
    '''

    List Complete ( 1 snippet total )

    Cannot find what you were looking for?
    Ask the Snip2Code Community

    • Public Snippets
    • Channels Snippets