Comman Table Expression in MS-SQL

Hi All,

Recently i had to generate a report on monthly throughput of invoices processed through BizTalk Server

In current architecture we are storing the each invoice information in MS-SQL server as primarily source of information. here we are stamping each batch predefined with standard format and ending with MMDDYY format.

In MS-SQL we have feature called Comman Table Expression(CTE) which better form of TempTable and with the help of this i can achieve the required report.

Here is example,

BATCH_ID
TEST-1-063118
TEST-2-063118
TEST-3-063118
TEST-1-070118
TEST-2-070218
TEST-3-070218
TEST-4-070218
TEST-2-070318
TEST-3-070318
TEST-3-070418
TEST-4-070418

WITH CTE AS (SELECT RIGHT(BATCH_ID,6) AS 'BATCHTRIM' FROM [dbo].BATCH_MASTER (NOLOCK))

SELECT BATCHTRIM, COUNT(BATCHTRIM) AS 'TOTALBATCHES' FROM CTE C
GROUP BY BATCHTRIM ORDER BY BATCHTRIM ASC

Output:
BATCHTRIM       TOTALBATCHES
063118                   3
070118                   1
070218                   3
070318                   2
070418                   2

Hope this Help!!!