Application.Wait (Now + #12:00:05 AM#)

--######################################
--this only works in debug mode...sometimes.
-- below gives 5 second delay.
--######################################

Public Sub Workbook_NewSheet(ByVal Sh As Object)
    On Error GoTo err_NewWS
    
    'Debug.Print "test"
    'Dim oWS As Excel.Worksheet
    'Set oWS = Application.Worksheets(ActiveSheet.Name)
    Sh.Activate
    
    Application.Wait (Now + #12:00:05 AM#)
    If Left(Sh.Name, 5) = "Sheet" Then
    
        If Sh.Range("D3").Value = "[$tblSnapshot].[SNAPSHOT_STATUS]" Then
            
            Sh.Range("A3:CK3").AutoFilter Field:=4, Criteria1:="ACKNOWLEDGED"
             Debug.Print "executed " & Sh.Name & " "; Now
        End If
        
    Else
        Debug.Print "DID NOT WORK " & Sh.Name & " "; Now
    End If
    
    'Set oWS = Nothing
exit_NewWS:
    Exit Sub
err_NewWS:
    MsgBox Err.Number & " " & Err.Description
    GoTo exit_NewWS
End Sub

ORACLE concatenate values

SELECT emplid, EFFDT, Last_name || ', ' || First_Name as Name2
FROM [table] where emplid = '00000001'

Identify SQL Server Connections

sp_who

SELECT p.spid
,   right(convert(varchar, 
            dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'), 
            121), 12) as 'batch_duration'
,   P.program_name
,   P.hostname
,   P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and      P.status not in ('background', 'sleeping')
and      P.cmd not in ('AWAITING COMMAND'
                    ,'MIRROR HANDLER'
                    ,'LAZY WRITER'
                    ,'CHECKPOINT SLEEP'
                    ,'RA MANAGER')
order by batch_duration desc

--kill 60

Declare @spid int
    ,   @stmt_start int
    ,   @stmt_end int
    ,   @sql_handle binary(20)

set @spid = 60 -- Fill this in

select  top 1
    @sql_handle = sql_handle
,   @stmt_start = case stmt_start 

                    when 0 then 0 

                    else stmt_start / 2 

                    end
,   @stmt_end = case stmt_end 

                    when -1 then -1 

                    else stmt_end / 2 

                    end
from    master.dbo.sysprocesses
where   spid = @spid
order by ecid

SELECT
    SUBSTRING( text,
    COALESCE(NULLIF(@stmt_start, 0), 1),
    CASE @stmt_end
    WHEN -1
    THEN DATALENGTH(text)
    ELSE
    (@stmt_end - @stmt_start)
    END
    )
FROM ::fn_get_sql(@sql_handle)

/*
Executed as user: NAM\SQLAdmin100. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.5058.0 for 32-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  5:01:39 AM  Error: 2014-09-02 05:01:41.52  
Code: 0x00000000     Source: Daily_tblSnapshot_Insert      Description: Invalid column name 'ASSESSEE_HR_EMAIL'.  End Error  Error: 2014-09-02 05:01:41.52  
Code: 0x00000000     Source: Daily_tblSnapshot_Insert      Description: Invalid column name 'ASSESSEE_HR_MANAGER_EMPLID'.  End Error  Error: 2014-09-02 05:01:41.58  
Code: 0xC002F210     Source: Daily_tblSnapshot_Insert Execute SQL Task     Description: Executing the query "EXEC [snapshot].Daily_tblSnapshot_Insert;" failed with the following error: "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  5:01:39 AM  Finished: 5:01:41 AM  Elapsed:  2.594 seconds.  The package execution failed.  The step failed.
*/

SQL JOIN to find non-matching rows

SELECT t1.ID 
FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.ID = t2.ID 
WHERE t2.ID IS NULL

Excel: remove #VALUE formula result

If a cell shows #VALUE it is because it cannot evaluate what you asked for.
eg If A1 has a value of 5, and A2 has "No", then =SUM(A1:A5) will give #VALUE because it cannot add a number and text together. 

=IF(ISERROR(SUM(A1:A2)),""No Data",SUM(A1:A2))

So you can get around this by querying if there is an error: =IF(ISERROR(SUM(A1:A2)),""No Data",SUM(A1:A2))
Thus if there is an error, the formula returns "No Data", otherwise you get the sum.

Similarly, for #DIV/0!, you can (should?) test to see if the denominator is 0. If it is then put "", else the result of the division.

Hope this helps

MS-Access: How to refer to subform control in SQL

Forms![main form name]![subform control name].Form![control name]


To refer to a control on a subform, use the following syntax: 

   Forms![main form name]![subform control name].Form![control name]
    

To refer to a control on a subreport, use the following syntax: 

   Reports![main report name]![subreport control name].Report![control
   name]
    

NOTE: Only subforms are discussed in the rest of this article, but all the information applies to both subforms and subreports. 

It is important to note that you cannot refer to controls on a subform with the following syntax: 

   Forms![subform name]![control name]
    

Private Sub Command3_Click()
    On Error GoTo err_Command3_Click

    Dim intHRGroupID As Integer
    Dim sql As String
    
        Debug.Print "############  begin   ##################'"
    
    Forms![Update HR to HR Groups]![HR Mgr to Group Update subform].Form![HR Manager Group ID].SetFocus
    HRGroupID = Forms![Update HR to HR Groups]![HR Mgr to Group Update subform].Form![HR Manager Group ID].Text
    
    sql = "UPDATE [HR_TLV_HR_Staff_DB] z INNER JOIN ([HR Group to HR Mgr Relationships] x INNER JOIN [HR Manager Group] y ON x.[HR Manager Group ID] = y.[HR Manager Group Code] " & vbCrLf & _
          "    ) ON z.[ID] = x.[HR Manager Emplid] " & vbCrLf & _
          "SET x.[Included in Group] = 1 " & vbCrLf & _
          "WHERE y.[HR Manager Group Code] = " & HRGroupID & vbCrLf & _
          "  AND " & Forms![Update HR to HR Groups]![HR Mgr to Group Update subform].Form.Filter & " AND 1 = 1"
    
    Debug.Print sql
        
    sql = Replace(sql, "[HR Mgr to Group Update]", "z")
    
    Debug.Print "############  revised   ##################'" & vbCrLf
    Debug.Print sql
    
    DoCmd.RunSQL sql

exit_Command3_Click:
    Exit Sub
err_Command3_Click:
    MsgBox Err.Number & " " & Err.Description
    GoTo exit_Command3_Click
End Sub

How to Return the First or Last Match in an Array

http://support.microsoft.com/kb/214069

You can use the LOOKUP() function to search for a value within an array of sorted data and return the corresponding value contained in that position within another array. If the lookup value is repeated within the array, it returns the last match encountered. This behavior is true for the VLOOKUP(), HLOOKUP(), and LOOKUP() functions. 

To find the first value instead of the last value in an array, use the INDEX() and MATCH() functions.

=IF(INDEX('CMS LILO DATA'!$AA$2:$AA$500, MATCH(A4, 'CMS LILO DATA'!$A$2:$A$500,0),1)=1,1,"")


=IF(
        INDEX('Sheet1'!$A$2:$A$500, MATCH(A4, 'Sheet2'!$A$2:$A$500,0),1)
           =1,1,"")

Oracle MONTHS_BETWEEN

Why don't you just use MONTHS_BETWEEN Function? Doesn't that help?

SQL> SELECT sysdate FROM Dual;

SYSDATE
---------
01-JUN-09

SQL> SELECT MONTHS_BETWEEN (SYSDATE,
  2                         TO_DATE ('01/01/2009', 'DD/MM/YYYY')
  3                        ) diff_in_months
  4    FROM DUAL;

DIFF_IN_MONTHS
--------------
             5

SQL>

Oracle Month function

SELECT LAST_NAME, FIRST_NAME, ORIG_HIRE_DT, BIRTHDATE, extract(month from BIRTHDATE)
from ps_pwc_employees
where pwc_mktcls_descr = 'Philadelphia Metro'
and  extract(month from BIRTHDATE) =5

Schemabinding

CREATE VIEW [dbo].[Transform_Rolling_12_Months]
with schemabinding
AS
SELECT A.Month_ID, B.Month_ID AS Rolling12Month
FROM dbo.Dim_Month AS A LEFT OUTER JOIN dbo.Dim_Month AS B ON B.Month_ID BETWEEN A.Month_ID - 11 AND A.Month_ID
WHERE (A.Month_ID > 11)

GO