CREATE TABLE #InquiryTblXSS(CustName VARCHAR(100), YEARS VARCHAR(100), January VARCHAR(100), February VARCHAR(100),March VARCHAR(100), April VARCHAR(100), May VARCHAR(100),June VARCHAR(100), July VARCHAR(100), August VARCHAR(100),September VARCHAR(100),October VARCHAR(100), November VARCHAR(100),December VARCHAR(100), Total VARCHAR(100))
INSERT [#InquiryTblXSS]
SELECT CustName,
YEARS,
[1] AS January,
[2] AS February,
[3] AS March,
[4] AS April,
[5] AS May,
[6] AS June,
[7] AS July,
[8] AS August,
[9] AS September,
[10] AS October,
[11] AS November,
[12] AS December,
[1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12] [Total]
FROM(
SELECT C.IssueID, CustName, SUBSTRING(ActualDate, 5, 1) AS Months, SUBSTRING(ActualDate, 7, 4) AS YEARS
FROM #CustomerNamess C
LEFT JOIN #ActualDateTblx A
ON C.IssueID = A.IssueID
LEFT JOIN #CompleteRateTblx O
ON C.IssueID = O.IssueID
WHERE CompleteRate = '100' AND LEN(SUBSTRING(ActualDate, 7, 4))=4 AND SUBSTRING(ActualDate, 7, 4) = '2014' AND CustName = 'adada'
)TableDate
PIVOT
(
COUNT (IssueID) FOR Months IN ([1], [2], [3], [4], [5], [6], [7],[8],[9],[10],[11],[12])
) PIVOTCOUNT
SELECT * FROM #InquiryTblXSS