MS SQL

Get Month Name from date:
select datename(month,getdate()) as Month_Name

Get Month Number from date:
select datepart(month,getdate()) as Month_No

Format datetime in SQL
SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM (or PM)
SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy                 
SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd          
SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy
SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy
SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy
SELECT convert(varchar, getdate(), 106) -- dd mon yyyy
SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy
SELECT convert(varchar, getdate(), 108) -- hh:mm:ss
SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)
SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd
SELECT convert(varchar, getdate(), 112) -- yyyymmdd
SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm
SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm

Pivot Table - Example #1

CREATE TABLE #tempProducts
(
Supplier varchar(15),
Product varchar(20),
Product_Quantity integer
)

INSERT INTO #tempProducts VALUES ('HP','Software',300000)
INSERT INTO #tempProducts VALUES ('HP','Hardware',150000)
INSERT INTO #tempProducts VALUES ('HP','Monitor',250000)
INSERT INTO #tempProducts VALUES ('HP','Keyboard',500000)

INSERT INTO #tempProducts VALUES ('Apple','Software',20000)
INSERT INTO #tempProducts VALUES ('Apple','Hardware',125000)
INSERT INTO #tempProducts VALUES ('Apple','Monitor',800000)
INSERT INTO #tempProducts VALUES ('Apple','Keyboard',60000)

INSERT INTO #tempProducts VALUES ('Dell','Software',120000)
INSERT INTO #tempProducts VALUES ('Dell','Hardware',250000)

INSERT INTO #tempProducts VALUES ('Sony','Software',900000)
INSERT INTO #tempProducts VALUES ('Sony','Hardware',450000)
INSERT INTO #tempProducts VALUES ('Sony','Monitor',100000)

/** Create crosstab using PIVOT **/
SELECT *
FROM #tempProducts
PIVOT
(
SUM(Product_Quantity)
FOR [Product]
IN ([Software],[Hardware],[Monitor],[Keyboard])
)
AS p

DROP TABLE #tempProducts

Pivot Table - Example #2
CREATE TABLE #tempRecords
(
Record int,
BookNo int,
BookValue varchar(50)
)

INSERT INTO #tempRecords VALUES (1,1,'One')
INSERT INTO #tempRecords VALUES (1,2,'Two')
INSERT INTO #tempRecords VALUES (1,3,'Three')
INSERT INTO #tempRecords VALUES (1,4,'Four')

INSERT INTO #tempRecords VALUES (2,1,'One')
INSERT INTO #tempRecords VALUES (2,2,'Two')
INSERT INTO #tempRecords VALUES (2,3,'Three')
INSERT INTO #tempRecords VALUES (2,7,'Seven')
INSERT INTO #tempRecords VALUES (2,8,'Eight')

INSERT INTO #tempRecords VALUES (3,9,'Nine')

INSERT INTO #tempRecords VALUES (4,2,'Two')
INSERT INTO #tempRecords VALUES (4,5,'Five')
INSERT INTO #tempRecords VALUES (4,10,'Ten')

/** Create crosstab using PIVOT **/
SELECT *
FROM #tempRecords
PIVOT
(
MAX(BookValue)
FOR [BookNo]
IN ([1],[2],[3],[4],[5],[6],[7],[8])
)
AS p

DROP TABLE #tempRecords

Pivot Table with dynamic columns
CREATE TABLE #tempProducts
(
Supplier varchar(15),
Product varchar(20),
Product_Quantity integer
)

INSERT INTO #tempProducts VALUES ('HP','Software',300000)
INSERT INTO #tempProducts VALUES ('HP','Hardware',150000)
INSERT INTO #tempProducts VALUES ('HP','Monitor',250000)
INSERT INTO #tempProducts VALUES ('HP','Keyboard',500000)

INSERT INTO #tempProducts VALUES ('Apple','Software',20000)
INSERT INTO #tempProducts VALUES ('Apple','Hardware',125000)
INSERT INTO #tempProducts VALUES ('Apple','Monitor',800000)
INSERT INTO #tempProducts VALUES ('Apple','Keyboard',60000)

INSERT INTO #tempProducts VALUES ('Dell','Software',120000)
INSERT INTO #tempProducts VALUES ('Dell','Hardware',250000)

INSERT INTO #tempProducts VALUES ('Sony','Software',900000)
INSERT INTO #tempProducts VALUES ('Sony','Hardware',450000)
INSERT INTO #tempProducts VALUES ('Sony','Monitor',100000)

DECLARE @columns VARCHAR(8000)

SELECT @columns = COALESCE(@columns + ',[' + cast(Product as varchar) + ']',
'[' + cast(Product as varchar)+ ']')

FROM #tempProducts
GROUP BY Product

DECLARE @query VARCHAR(8000)
SET @query = '

SELECT *
FROM #tempProducts
PIVOT
(
MAX(Product_Quantity)
FOR [Product]
IN (' + @columns + ')
)
AS p'

EXECUTE(@query)

DROP TABLE #tempProducts

SUM columns in multiple tables.

Method 1:

SELECT credit.contact_id, (IFNULL(credit, 0) - IFNULL(debit, 0)) as balance
     FROM (
    (SELECT SUM(credit) as credit, contact_id FROM table_a GROUP BY contact_id) as credit
       LEFT JOIN
    (SELECT SUM(debit) as debit, contact_id FROM table_b GROUP BY contact_id) as debit
     ON debit.contact_id = credit.contact_id);

Method 2:

SELECT contact_id, SUM(balance) FROM
    (SELECT contact_id ,credit as balance FROM table_a
       UNION ALL
    SELECT contact_id,- debit as balance FROM table_b
   ) as x
  GROUP BY contact_id;