Data Metrics

Posted on September 9, 2013October 26, 2015 By David Kittell

In a plan to make metrics easier to record and show I have built my own database tables to record custom metrics.

Metrics – Year View C#
Metrics Admin Page C#
Table Creation

SET NOCOUNT ON

DECLARE @month INT
	,@year INT
	,@start_date DATETIME
	,@end_date DATETIME

SET @month = (
		SELECT CASE
				WHEN MONTH(GETDATE()) = '1'
					THEN '12'
				ELSE MONTH(GETDATE()) - 1
				END
		)
--SELECT @month AS Month_To_Report
SET @year = (
		SELECT CASE
				WHEN MONTH(GETDATE()) = '1'
					THEN YEAR(GETDATE()) - 1
				ELSE YEAR(GETDATE())
				END
		)
--SELECT @YEAR AS Year_To_Report
SET @start_date = CAST(CAST(@month AS VARCHAR) + '/01/' + CAST(@year AS VARCHAR) AS DATETIME)
-- @start_date AS Start_Date
SET @end_date = (
		SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, @start_date) + 1, 0))
		)

--		SELECT @start_date
--SELECT CAST(CAST(@month AS VARCHAR) + '/01/' + CAST(@year-1 AS VARCHAR) AS DATETIME)
SELECT (
		SELECT CASE
				WHEN (parent > 0)
					THEN (
							(
								SELECT NAME
								FROM tblMetricsCategory tmc2
								WHERE tmc2.id = tmc1.parent
								) + ' - ' + tmc1.NAME
							)
				ELSE tmc1.NAME
				END
		FROM tblMetricsCategory tmc1
		WHERE tmm.category = tmc1.id
		) AS Category
	,Measure
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start >= DATEADD(month, - 11, @start_date)
			AND [end] <= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 11, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [12 Months Ago]
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start >= DATEADD(month, - 10, @start_date)
			AND [end] <= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 10, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [11 Months Ago]
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start >= DATEADD(month, - 9, @start_date)
			AND [end] <= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 9, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [10 Months Ago]
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start >= DATEADD(month, - 8, @start_date)
			AND [end] <= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 8, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [9 Months Ago]
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start >= DATEADD(month, - 7, @start_date)
			AND [end] <= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 7, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [8 Months Ago]
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start >= DATEADD(month, - 6, @start_date)
			AND [end] <= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 6, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [7 Months Ago]
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start >= DATEADD(month, - 5, @start_date)
			AND [end] <= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 5, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [6 Months Ago]
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start >= DATEADD(month, - 4, @start_date)
			AND [end] <= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 4, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [5 Months Ago]
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start >= DATEADD(month, - 3, @start_date)
			AND [end] <= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 3, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [4 Months Ago]
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start >= DATEADD(month, - 2, @start_date)
			AND [end] <= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 2, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [3 Months Ago]
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start >= DATEADD(month, - 1, @start_date)
			AND [end] <= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 1, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [2 Months Ago]
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start >= @start_date
			AND [end] <= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, @start_date) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [1 Month Ago]
FROM tblMetricsMeasure tmm

SELECT tmm.ID
	,(
		CONVERT(VARCHAR(10), tmm.category) + ' - ' + (
			SELECT CASE
					WHEN (parent > 0)
						THEN (
								(
									SELECT NAME
									FROM tblMetricsCategory tmc2
									WHERE tmc2.id = tmc1.parent
									) + ' - ' + tmc1.NAME
								)
					ELSE tmc1.NAME
					END
			FROM tblMetricsCategory tmc1
			WHERE tmm.category = tmc1.id
			)
		) AS Category
	,tmm.measure
FROM tblMetricsMeasure tmm

SELECT (
		SELECT CASE
				WHEN (parent > 0)
					THEN (
							(
								SELECT NAME
								FROM tblMetricsCategory tmc2
								WHERE tmc2.id = tmc1.parent
								) + ' - ' + tmc1.NAME
							)
				ELSE tmc1.NAME
				END
		FROM tblMetricsCategory tmc1
		WHERE tm.category = tmc1.id
		) AS Category
	,(
		SELECT measure
		FROM tblMetricsMeasure tmm
		WHERE tm.measure = tmm.id
		) AS Measure
	,dbo.fnFormatDate([start], 'Mon yyyy') AS [Month]
	,[count] AS [Count]
FROM [tblMetrics] tm
WHERE start >= '9-1-2013'
	AND [end] <= '9-30-2013'
ORDER BY start
	,[category]
	,measure

UPDATE tblMetrics
SET [count] = NULL
WHERE [count] = 0;

DELETE
FROM tblMetrics
WHERE [count] IS NULL

SELECT (
		SELECT CASE
				WHEN (parent > 0)
					THEN (
							(
								SELECT NAME
								FROM tblMetricsCategory tmc2
								WHERE tmc2.id = tmc1.parent
								) + ' - ' + tmc1.NAME
							)
				ELSE tmc1.NAME
				END
		FROM tblMetricsCategory tmc1
		WHERE tm.category = tmc1.id
		) AS Category_name
	,(
		SELECT measure
		FROM tblMetricsMeasure tmm
		WHERE tm.measure = tmm.id
		) AS Measure
	,MIN([count]) AS Minimum
	,MAX([count]) AS Maximum
	,CAST(ROUND(AVG([count]), 2, 1) AS DECIMAL(9, 2)) AS Average
FROM tblMetrics tm
GROUP BY measure
	,category
ORDER BY category
	,measure

Metrics YEar View Page C#

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="index.aspx.cs" Inherits="InternalTools_Metrics" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
	<title>Metrics - Year View</title>
</head>
<body>
	<form id="form1" runat="server">
	<div>
		<asp:GridView ID="gvMetrics" runat="server" AllowSorting="True" AutoGenerateColumns="False"
			CellPadding="4" DataSourceID="dsMetrics" ForeColor="#333333">
			<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
			<Columns>
				<asp:BoundField DataField="Category" HeaderText="Category" ReadOnly="True" SortExpression="Category" />
				<asp:BoundField DataField="Measure" HeaderText="Measure" SortExpression="Measure" />
				<asp:BoundField DataField="13 Months Ago" HeaderText="13 Months Ago" SortExpression="13 Months Ago"
					ReadOnly="True" />
				<asp:BoundField DataField="12 Months Ago" HeaderText="12 Months Ago" SortExpression="12 Months Ago"
					ReadOnly="True" />
				<asp:BoundField DataField="11 Months Ago" HeaderText="11 Months Ago" ReadOnly="True"
					SortExpression="11 Months Ago" />
				<asp:BoundField DataField="10 Months Ago" HeaderText="10 Months Ago" ReadOnly="True"
					SortExpression="10 Months Ago" />
				<asp:BoundField DataField="9 Months Ago" HeaderText="9 Months Ago" ReadOnly="True"
					SortExpression="9 Months Ago" />
				<asp:BoundField DataField="8 Months Ago" HeaderText="8 Months Ago" ReadOnly="True"
					SortExpression="8 Months Ago" />
				<asp:BoundField DataField="7 Months Ago" HeaderText="7 Months Ago" ReadOnly="True"
					SortExpression="7 Months Ago" />
				<asp:BoundField DataField="6 Months Ago" HeaderText="6 Months Ago" ReadOnly="True"
					SortExpression="6 Months Ago" />
				<asp:BoundField DataField="5 Months Ago" HeaderText="5 Months Ago" ReadOnly="True"
					SortExpression="5 Months Ago" />
				<asp:BoundField DataField="4 Months Ago" HeaderText="4 Months Ago" ReadOnly="True"
					SortExpression="4 Months Ago" />
				<asp:BoundField DataField="3 Months Ago" HeaderText="3 Months Ago" ReadOnly="True"
					SortExpression="3 Months Ago" />
				<asp:BoundField DataField="2 Months Ago" HeaderText="2 Months Ago" ReadOnly="True"
					SortExpression="2 Months Ago" />
				<asp:BoundField DataField="1 Month Ago" HeaderText="1 Month Ago" ReadOnly="True"
					SortExpression="1 Month Ago" />
			</Columns>
			<EditRowStyle BackColor="#999999" />
			<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
			<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
			<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
			<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
			<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
			<SortedAscendingCellStyle BackColor="#E9E7E2" />
			<SortedAscendingHeaderStyle BackColor="#506C8C" />
			<SortedDescendingCellStyle BackColor="#FFFDF8" />
			<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
		</asp:GridView>
		<asp:SqlDataSource ID="dsMetrics" runat="server" ConnectionString="<%$ ConnectionStrings:DEVConnectionString %>"
			SelectCommand="SET NOCOUNT ON

SELECT (
		SELECT CASE
				WHEN (parent &gt; 0)
					THEN (
							(
								SELECT NAME
								FROM tblMetricsCategory tmc2
								WHERE tmc2.id = tmc1.parent
								) + ' - ' + tmc1.NAME
							)
				ELSE tmc1.NAME
				END
		FROM tblMetricsCategory tmc1
		WHERE tmm.category = tmc1.id
		) AS Category
	,Measure
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start &gt;= DATEADD(month, - 12, @start_date)
			AND [end] &lt;= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 12, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [13 Months Ago] -- September 2012
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start &gt;= DATEADD(month, - 11, @start_date)
			AND [end] &lt;= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 11, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [12 Months Ago] -- October 2012
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start &gt;= DATEADD(month, - 10, @start_date)
			AND [end] &lt;= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 10, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [11 Months Ago] -- November 2012
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start &gt;= DATEADD(month, - 9, @start_date)
			AND [end] &lt;= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 9, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [10 Months Ago] -- December 2012
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start &gt;= DATEADD(month, - 8, @start_date)
			AND [end] &lt;= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 8, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [9 Months Ago] -- January 2013
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start &gt;= DATEADD(month, - 7, @start_date)
			AND [end] &lt;= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 7, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [8 Months Ago] -- February 2013
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start &gt;= DATEADD(month, - 6, @start_date)
			AND [end] &lt;= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 6, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [7 Months Ago] -- March 2013
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start &gt;= DATEADD(month, - 5, @start_date)
			AND [end] &lt;= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 5, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [6 Months Ago] -- April 2013
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start &gt;= DATEADD(month, - 4, @start_date)
			AND [end] &lt;= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 4, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [5 Months Ago] -- May 2013
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start &gt;= DATEADD(month, - 3, @start_date)
			AND [end] &lt;= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 3, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [4 Months Ago] -- June 2013
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start &gt;= DATEADD(month, - 2, @start_date)
			AND [end] &lt;= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 2, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [3 Months Ago] -- July 2013
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start &gt;= DATEADD(month, - 1, @start_date)
			AND [end] &lt;= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, - 1, @start_date)) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [2 Months Ago] -- August 2013
	,(
		SELECT [count]
		FROM tblMetrics tm
		WHERE start &gt;= @start_date
			AND [end] &lt;= (
				SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, @start_date) + 1, 0))
				)
			AND tm.measure = tmm.id
		) AS [1 Month Ago] -- September 2013
FROM tblMetricsMeasure tmm
">
			<SelectParameters>
				<asp:QueryStringParameter DefaultValue="9-1-2013" Name="start_date" QueryStringField="s" />
			</SelectParameters>
		</asp:SqlDataSource>
	</div>
	</form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class InternalTools_Metrics : System.Web.UI.Page
{

	protected void Page_Load(object sender, EventArgs e)
	{
		if (Request.QueryString["s"] == null)
		{
			var firstDayPrevMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(-1);

			Response.Redirect("index.aspx?s=" + firstDayPrevMonth.ToString("yyyy-MM-dd"));
		}
	}
}

Metrics Admin Page C#

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Metrics-Admin.aspx.cs" Inherits="InternalTools_Metrics_Admin" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
	<title>Metrics</title>
	<link rel="stylesheet" media="all" type="text/css" href="http://code.jquery.com/ui/1.9.1/themes/smoothness/jquery-ui.css" />
	<link rel="stylesheet" media="all" type="text/css" href="/css/jquery-ui-timepicker-addon.css" />
	<script type="text/javascript" src="http://code.jquery.com/jquery-1.8.2.min.js"></script>
	<script type="text/javascript" src="http://code.jquery.com/ui/1.9.1/jquery-ui.min.js"></script>
	<script type="text/javascript" src="/js/jquery-ui-timepicker-addon.js"></script>
	<script type="text/javascript" src="/js/jquery-ui-sliderAccess.js"></script>
	<script type="text/javascript">
		$(function () {
			//
			$("#start").datepicker({
				changeMonth: true,
				changeYear: true,
				showOtherMonths: true,
				selectOtherMonths: true
			});

			$("#end").datepicker({
				changeMonth: true,
				changeYear: true,
				showOtherMonths: true,
				selectOtherMonths: true
			});

		});
	</script>
</head>
<body>
	<form id="form1" runat="server">
	<div id='divDataResults' runat="server">
	</div>
	<div id='divDataForm' runat="server">
	</div>
	<asp:button runat="server" text="Submit" id="btnSubmit" onclick="btnSubmit_Click" />
	</form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Specialized;

public partial class InternalTools_Metrics_Admin : System.Web.UI.Page
{
	public static string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
	public static string sFormElements = "<table cellpadding='2' cellspacing='0' border='0'>";
	public static string sDebug = "";
	public static string sEmployee = "";

	protected void Page_Load(object sender, EventArgs e)
	{

		System.Security.Principal.WindowsPrincipal p = System.Threading.Thread.CurrentPrincipal as System.Security.Principal.WindowsPrincipal;

		sEmployee = p.Identity.Name.Replace("<DOMAIN>", "").ToLower();

		string strSql = "";

		strSql = "SELECT tmm.ID,tmm.category,(SELECT CASE WHEN(parent>0)THEN((SELECT NAME FROM tblMetricsCategory tmc2 WHERE tmc2.id=tmc1.parent)+' - '+tmc1.NAME)ELSE tmc1.NAME END FROM tblMetricsCategory tmc1 WHERE tmm.category=tmc1.id)AS Category_Name,tmm.measure FROM tblMetricsMeasure tmm";

		//Response.Write(strSql);
		//Response.End();

		using (SqlConnection conn1 = new SqlConnection(connString))
		{
			using (SqlCommand cmd1 = new SqlCommand(strSql, conn1))
			{
				conn1.Open();
				SqlDataReader rdr1 = cmd1.ExecuteReader();
				if (rdr1.HasRows)
				{
					sFormElements += "<tr><td colspan='3'><strong>Metrics</strong></td></tr>";
					sFormElements += "<tr><td>&nbsp;&nbsp;&nbsp;</td><td align='right'>Start</td><td><input type='text' name='start' id='start' runat='server'></td></tr>";
					sFormElements += "<tr><td>&nbsp;&nbsp;&nbsp;</td><td align='right'>End</td><td><input type='text' name='end' id='end' runat='server'></td></tr>";
					string sCategory = "";
					while (rdr1.Read())
					{


						if (sCategory != rdr1["Category"].ToString().Trim())
						{
							sCategory = rdr1["Category"].ToString().Trim();
							sFormElements += "<tr><td colspan='3'><strong>" + rdr1["Category_Name"].ToString().Trim() + "</strong></td></tr>";
						}

						sFormElements += "<tr><td>&nbsp;&nbsp;&nbsp;</td><td align='right'>" + rdr1["measure"].ToString().Trim() + "</td><td><input type='text' name='measure_" + sCategory + "_" + rdr1["id"].ToString().Trim() + "' id='measure_" + sCategory + "_" + rdr1["id"].ToString().Trim() + "' runat='server'></td></tr>";


						//Response.Write(rdr1["id"].ToString().Trim());
					}
				}
			}
		}

		divDataForm.InnerHtml = sFormElements + "</table>";
	}

	protected void btnSubmit_Click(object sender, EventArgs e)
	{
		if (Page.IsPostBack)
		{
			divDataForm.Visible = false;
			btnSubmit.Visible = false;
			NameValueCollection submittedValuesCollection = Request.Form;

			Response.Write("<b>Submitted Values:<br /></b>");

			Response.Write(Request.Form["start"] + "<br/>");
			Response.Write(Request.Form["end"] + "<br/>");

			string sInputControl = "";
			string sInputValue = "";
			string[] sDataElement;
			string sSQL = "";
			foreach (string key in submittedValuesCollection.AllKeys)
			{
				sInputControl = string.Format("{0}", key, submittedValuesCollection[key]);
				sInputValue = string.Format("{1}", key, submittedValuesCollection[key]);

				try
				{
					if (sInputValue != "")
					{

						if (sInputControl.Contains("measure_"))
						{
							sDataElement = sInputControl.Split('_');

							Response.Write("<p>Category: " + sDataElement[1]);
							Response.Write("<br>Measure: " + sDataElement[2]);
							Response.Write("<br>Value: " + sInputValue);


							sSQL = "INSERT INTO tblMetrics (category, measure, submitted, start, [end], [count], submittedby) Values ('"
							+ sDataElement[1]
							+ "','"
							+ sDataElement[2]
							+ "','"
							+ DateTime.Now.ToString()
							+ "','"
							+ Request.Form["start"]
							+ "','"
							+ Request.Form["end"]
							+ "','"
							+ sInputValue
							+ "','"
							+ sEmployee
							+ "');";

							//Response.Write("<br/>SQL: " + sSQL);
							sRunSQLStmnt(sSQL);

							Response.Write("</p>");
						}

					}
				}
				catch (Exception ex)
				{ }
			}
			Response.Write("<br /><br />");
		}
	}

	protected string sRunSQLStmnt(string sSQL)
	{
		string sReturn = "";
		try
		{
			//Declare the connection object
			SqlConnection Conn = new SqlConnection(connString);

			//Make the connection
			Conn.Open();

			//Declare the Command
			SqlCommand cmd = new SqlCommand(sSQL + " SELECT @@ROWCOUNT AS DELETED;", Conn);

			//Execute the query
			int numberOfRecords = cmd.ExecuteNonQuery();

			Conn.Close();

			sReturn = numberOfRecords.ToString() + " Rows Affected";
		}

		catch (Exception ex)
		{
			sDebug += ex;
		}

		return sReturn;
	}
}

Table Creation

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblMetrics] (
	[ID] [int] IDENTITY(1, 1) NOT NULL
	,[category] [int] NULL
	,[measure] [int] NULL
	,[submitted] [datetime] NULL
	,[start] [datetime] NULL
	,[end] [datetime] NULL
	,[count] [float] NULL
	,[submittedby] [nvarchar](255) NULL
	,CONSTRAINT [aaaaatblMetrics_PK] PRIMARY KEY NONCLUSTERED ([ID] ASC) WITH (
		PAD_INDEX = OFF
		,STATISTICS_NORECOMPUTE = OFF
		,IGNORE_DUP_KEY = OFF
		,ALLOW_ROW_LOCKS = ON
		,ALLOW_PAGE_LOCKS = ON
		) ON [PRIMARY]
	) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblMetricsCategory] (
	[ID] [int] IDENTITY(1, 1) NOT NULL
	,[parent] [int] NULL
	,[name] [nvarchar](255) NULL
	,CONSTRAINT [aaaaatblMetricsCategory_PK] PRIMARY KEY NONCLUSTERED ([ID] ASC) WITH (
		PAD_INDEX = OFF
		,STATISTICS_NORECOMPUTE = OFF
		,IGNORE_DUP_KEY = OFF
		,ALLOW_ROW_LOCKS = ON
		,ALLOW_PAGE_LOCKS = ON
		) ON [PRIMARY]
	) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblMetricsMeasure] (
	[ID] [int] IDENTITY(1, 1) NOT NULL
	,[category] [int] NULL
	,[measure] [nvarchar](255) NULL
	,CONSTRAINT [aaaaatblMetricsMeasure_PK] PRIMARY KEY NONCLUSTERED ([ID] ASC) WITH (
		PAD_INDEX = OFF
		,STATISTICS_NORECOMPUTE = OFF
		,IGNORE_DUP_KEY = OFF
		,ALLOW_ROW_LOCKS = ON
		,ALLOW_PAGE_LOCKS = ON
		) ON [PRIMARY]
	) ON [PRIMARY]
GO

Originally Posted on September 9, 2013
Last Updated on October 26, 2015

All information on this site is shared with the intention to help. Before any source code or program is ran on a production (non-development) system it is suggested you test it and fully understand what it is doing not just what it appears it is doing. I accept no responsibility for any damage you may do with this code.

Related

Code MSSQL