SQL - A simple example of using IN / NOT IN

Below is a simple example of using IN / NOT IN of SQL2005

select TID, MobileNum, create_timestamp, success from XXX..XXX where TID not like 'ZHA%' and mobilenum not in
( select mobile_num as mobilenum from XXX..XXXX
)
order by create_timestamp asc

C# - example of time string formatting

Below is two simple example of how to use C# to display date/time in different format:

[Present Time]
strNow = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

[TimeStamp of File]
FileInfo fi = new FileInfo(strPath);
fi.LastWriteTime.ToString("yyyyMMddHHmmss");

SQL 2005 - Usage of recursive query and Union

Below is an example which I implemented in this morning. Need to use the SQL to perform join among three tables

select c.CID, c.event, c.SID, c.TID, c.create_timestamp,

SUBSTRING(d.userid,1,4)+'XXXX'+SUBSTRING(d.userid,len(d.userid)-3,4) as mobile

from

(select a.CID, b.alias as event, a.SID, a.TID, a.create_timestamp

from XXXX..TxEvent as a join XXXX..campaigns as b

on a.success='0' and a.CID=b.campaignid) c

join XXX..MMS_Usage as d on c.SID=d.session_id and d.inbound='0'


union


select a.campaign_id as CID, b.alias as event, a.session_id as SID, a.TID, a.create_timestamp,

SUBSTRING(a.userid,1,4)+'XXXX'+SUBSTRING(a.userid,len(a.userid)-3,4) as mobile

from XXXX..Master_TxEvent as a join XXXX..campaigns as b

on a.state='0' and a.campaign_id=b.campaignid

SQL - create user account

1. Execute commands as below to create login account
CREATE LOGIN deve1
WITH PASSWORD = 'deve1';
USE dbase_xxx;
CREATE USER deve1 FOR LOGIN deve1;
GO

2. Execute commands as below to assign user account to each database
use dbase_xxyy
exec sp_adduser deve1

3. Execute commands as below change grant / deny access rights to individual account

DENY UPDATE ON dbase_XXX..table_xxx to develop