The following query is for finding records in a table which  belong to previous(last) month. This can be useful for finding records and creating reports on those records’ basis for example : Number of patients for last month in a hospital , total number of transactions done for last month in a bank , etc .

select * from table where
datepart(m,yourdatecolumn) = DATEPART(m,DATEADD(m,-1,getdate())) AND 
DATEPART(yyyy, yourdatecolumn) = DATEPART(yyyy, DATEADD(m, -1, getdate()))

Sending Automated emails or paging to to servers / Users are important ,these mails can serve as alerts if something is changed or failed in a system.

• First we have to Assign permission to Certain user who wants to execute system procedures.

GRANT EXECUTE ON master..sp_OACreate to Username

• Enable Advanced options by :

USE MASTER

--Enable Database Mail XPs Advanced Options in SQL Server 
  SP_CONFIGURE 'Database Mail XPs', 1 RECONFIGURE WITH OVERRIDE
   GO
  SP_CONFIGURE 'show advanced options', 0 RECONFIGURE WITH OVERRIDE
  GO

• Then ,start the Database Mail Configuration Wizard
  1. From Object Explorer, connect to an instance of SQL Server.
  2. Expand Management, right-click Database Mail, and select Configure Database Mail.
  3. Choose the Set up Database Mail option to set up Database Mail for the first time.

  

• Execute the following command now:

EXEC msdb.dbo.sp_send_dbmail

@profile_name='username',

@recipients='yourrecepient@recepientdomain.com',

@subject='Testing Email from SQL Server',

@body='It Worked!Email sent successfully',

@body_format='HTML',

@from_address='sender@yourdomain.com',

@reply_to='sender@yourdomain.com'

 

Thanks

 

Here is an example of inserting into a temporary table from multiple joins:

select a.col,b.col,c.col into #temp1

from table1 a

join table2 b on a.column1ID= b.column2ID

join table3 c on a.column1ID= c.column3ID

where c.col= ‘anydata’