Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
stronius
Starting Member
8 Posts |
 Posted - 2010-07-22 : 12:43:02
|
| Hello,I have an application that creates a dynamic SQL command which is binded to a gridview. The gridview is populated with 2 rows comparing customer data over 2 different time periods. I'm trying to add a third row which will display the change between the 2 time periods, for example, if time period one displays 109 for the 'RDO' column, and time period 2 displays 104 for the 'RDO' column, I was the 3rd row to display '-5'. Any ideas on how to achieve this?Here is the code I use to determine the distinct field values, put them into columns, and display their count...[CODE] ' Get distinct field values to use for columns Dim Cols As String = Nothing Dim cmdText As String = Nothing Dim Builder1 As New StringBuilder() Using SqlConn As SqlConnection = New SqlConnection(WebConfigurationManager.ConnectionStrings("sqlConnection").ToString) Builder1.Append("DECLARE @Cols varchar(max);") Builder1.Append("WITH DistinctCols AS (SELECT DISTINCT [") Builder1.Append(Field) Builder1.Append("] FROM [dbo].[v_CustomerBatchData])") Builder1.Append("SELECT @Cols = COALESCE(@Cols + ',', '') + [") Builder1.Append(Field) Builder1.Append("] FROM DistinctCols SELECT @Cols") cmdText = Builder1.ToString Dim SqlCmd As SqlCommand = New SqlCommand(cmdText, SqlConn) SqlConn.Open() Try Cols = SqlCmd.ExecuteScalar Catch ex As Exception Console.WriteLine(ex.Message) Finally SqlCmd.Dispose() SqlConn.Close() End Try End Using 'Populate Gridview Dim selCommand As String = "SELECT CONVERT(VARCHAR,[Batch_ID]) Batch, CONVERT(VARCHAR,[Date]) BatchDate," Dim Columns() As String = Nothing Columns = Cols.Split(",") For Each c In Columns selCommand += "CONVERT(VARCHAR, COUNT(CASE [" & Field & "] WHEN '" & c & "' THEN [" & Field & "] END)) '" & c & "'," selCommand += "CONVERT(VARCHAR, CONVERT(Numeric(10,2), COUNT(CASE [" & Field & "] WHEN '" & c & "' THEN [" & Field & "] END) * 100.00 / (SELECT COUNT([" & Field & "]) FROM [v_CustomerBatchData] WHERE [Batch_ID] = " & Batch2 & "))) '%'," Next selCommand = selCommand.Substring(0, selCommand.LastIndexOf(",")) selCommand += " FROM dbo.v_CustomerBatchData WHERE [Batch_ID] = " & Batch1 & " OR [Batch_ID] = " & Batch2 & " GROUP BY [Batch_ID], [Date]" compareData.SelectCommand = selCommand gvCompare.DataBind() gvCompare.Visible = True[/CODE]Currently, this is the result I get ...[CODE]Batch BatchDate RDP % DYE %1 ROT %2 RDO %3 RGAS %4 STA %5 CHO %6 CPR %71 June 30 639 36.37 2 0.11 1 0.06 1093 62.21 4 0.23 8 0.46 1 0.06 9 0.5116 July 30 648 36.88 2 0.11 1 0.06 1084 61.70 4 0.23 8 0.46 1 0.06 9 0.51[/CODE]This is the result I want...[CODE]Batch BatchDate RDP % DYE %1 ROT %2 RDO %3 RGAS %4 STA %5 CHO %6 CPR %71 June 30 639 36.37 2 0.11 1 0.06 1093 62.21 4 0.23 8 0.46 1 0.06 9 0.5116 July 30 648 36.88 2 0.11 1 0.06 1084 61.70 4 0.23 8 0.46 1 0.06 9 0.51 Change +9 +0.51 ... etc.. etc.. etc..[/CODE]Any ideas? |
|
|
|
|
|
|
|
Dynamic SQL & GridView