Alternative to Cursos

Situation:
A monthly corporate reports which generates around 30 different KPIs (Key Performance Indicators). Each KPI is being generated using a different query from a number of joined tables in a SQL Server database.

In an attempt to automate the report generation process, I’ve created a table in my database, in which I have stored the variable parts in all the 30 queries for the KPIs.

The table is comprised of the following columns: (This is just a demo table)

KPI_Name     Criteria_1     Criteria_2     Criteria_3
=======     ========     ========     =======
KPI_1          XYZ             ABC               KLM
KPI_2          QWE             GHJ               MNO
KPI_3          ……..             …….               ……..

The query for KPI_1 is:
Select sum(Sales) from myTable
WHERE 1st_Criteria = ‘XYZ’ and 2nd_Criteria = ‘ABC’ and 3rd_Criteria = ‘KLM’

The query for KPI_2 is:
Select sum(Sales) from myTable
WHERE 1st_Criteria = ‘QWE’ and 2nd_Criteria = ‘GHJ’ and 3rd_Criteria = ‘MNO’

and so on and so forth.

The way I plan to generate the report is through a stored procedure, in which I will read the entire contents of my Criteria Table into a Cursor, and then construct my select statement in dynamic SQL:

DECLARE myCursor CURSOR FOR SELECT KPI_Name, Criteria_1, Criteria_2, Criteria_3
FROM myTable

OPEN myCursor
FETCH NEXT FROM myCursor
INTO @KPI_Name, @Criteria_1, @Criteria_2, @Criteria_3

WHILE @@FETCH_STATUS = 0
BEGIN

Declare @SQL as varchar(200)
Set @SQL = ‘Insert into Output_Table (KPI_Value) Select sum(Sales) from myTable WHERE
1st_Criteria = ‘ + @Criteria_1 +
and 2nd_Criteria = ‘ + @Criteria_2 +
and 3rd_Criteria = ‘ @Criteria_3
exec sp_executesql @SQL

Alternative:
Insert into Output_Table (KPI_Name, KPI_Value)
Select k.KPI_Name, sum(t.Sales)
from myTable t
JOIN KPItable k
ON t.1st_Criteria = k.Criteria_1
and t.2nd_Criteria = k.Criteria_2
and t.3rd_Criteria = k.Criteria_3
GROUP BY k.KPI_name

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s