I have a query/stored procedure that runs fine in SQL Management Studio. Running the first Select statement before the UNION in C# works fine.
So, Would it be better to run this as a Stored Procedure with Multiple parameters or is is possible to run in the code I am posting here?
The specific differences in the three select statements,
First select statement would be for 1st of month, Second select is for the 15th of the month, and Third is for a date selected by the user AFTER the 15th of the month.
Thanks,
Here is the code I have
private void MnuCTCThreeDayReport_Click(object sender, EventArgs e) { // CTC Three Day Report try { Cursor.Current = Cursors.WaitCursor; dt.Reset(); dt = new DataTable(); //Hour = radDTP.Value.Hour.ToString(); //Day = radDTP.Value.Day.ToString(); radDTP2.Value = DateTime.Now; Month = radDTP.Value.Month.ToString(); Year = radDTP.Value.Year.ToString(); DateTime dn = DateTime.Now; DateTime firstDayOfMonth = new DateTime(dn.Year, dn.Month, 1); DateTime lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1); //sdate = radDTP2.Value.ToShortDateString(); SqlConnection conn = new SqlConnection(Globals.ConStats); conn.Close(); conn.Open(); string sql = "SELECT T1.ControlPointName AS 'CP Name', " + "T1.codeline AS Codeline, " + "T1.Type as Type, " + "T1.SubName AS 'Sub Division', " + "T1.Day AS 'Day', " + "T1.Month AS 'Month', " + "T1.Year AS 'Year', " + "SUM(T1.OutOfSyncCount) AS 'Out Of Sync', " + "SUM(T1.RecallLocalCount) AS 'Recalls Local', " + "SUM(T1.RecallRemoteCount) AS 'Recalls Remote', " + "SUM(T1.IndicationCount) AS 'Indications', " + "SUM(T1.ControlFailCount) AS 'Control Failures', " + "SUM(T1.ControlPointStatusDownCount) AS 'Control Point Down', " + "SUM(T1.ControlSentCount) AS 'Controls', " + "SUM(T1.FailureCount) AS 'Failures', " + "SUM(T1.ResyncCount) AS 'Resyncs', " + "SUM(T1.ControlDeliveryCount) AS 'Controls', " + "SUM(T1.RecallRetryCount) AS 'Recall Retries', " + "SUM(T1.ResendCount) AS 'Resends' " + "FROM(SELECT ControlPointName, " + "StationNumber, " + "codeline, " + "tmdsDatabaseStatic.dbo.tblCodelines.Legacytype as Type, " + "SubName, " + "Day, " + "Month, " + "Year, " + "OutOfSyncCount, " + "RecallLocalCount, " + "RecallRemoteCount, " + "IndicationCount, " + "ControlFailCount, " + "ControlPointStatusDownCount, " + "ControlSentCount, " + "FailureCount, " + "ResyncCount, " + "ControlDeliveryCount, " + "RecallRetryCount, " + "ResendCount " + "FROM tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay WITH (NOLOCK) INNER JOIN tmdsDatabaseStatic.dbo.tblCodeLines ON tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay.codeline = tmdsDatabaseStatic.dbo.tblCodeLines.CodelineNumber " + "WHERE ControlPointName NOT LIKE 'WD%' AND ControlPointName NOT LIKE 'WM%' and firstDayOfMonth and month = @month and year = @year) AS T1 " + "GROUP BY ControlPointName, Codeline, Type, SubName, Day, Month, Year, FailureCount " + "UNION" + "SELECT T1.ControlPointName AS 'CP Name', " + "T1.codeline AS Codeline, " + "T1.Type as Type, " + "T1.SubName AS 'Sub Division', " + "T1.Day AS 'Day', " + "T1.Month AS 'Month', " + "T1.Year AS 'Year', " + "SUM(T1.OutOfSyncCount) AS 'Out Of Sync', " + "SUM(T1.RecallLocalCount) AS 'Recalls Local', " + "SUM(T1.RecallRemoteCount) AS 'Recalls Remote', " + "SUM(T1.IndicationCount) AS 'Indications', " + "SUM(T1.ControlFailCount) AS 'Control Failures', " + "SUM(T1.ControlPointStatusDownCount) AS 'Control Point Down', " + "SUM(T1.ControlSentCount) AS 'Controls', " + "SUM(T1.FailureCount) AS 'Failures', " + "SUM(T1.ResyncCount) AS 'Resyncs', " + "SUM(T1.ControlDeliveryCount) AS 'Controls', " + "SUM(T1.RecallRetryCount) AS 'Recall Retries', " + "SUM(T1.ResendCount) AS 'Resends' " + "FROM(SELECT ControlPointName, " + "StationNumber, " + "codeline, " + "tmdsDatabaseStatic.dbo.tblCodelines.Legacytype as Type, " + "SubName, " + "Day, " + "Month, " + "Year, " + "OutOfSyncCount, " + "RecallLocalCount, " + "RecallRemoteCount, " + "IndicationCount, " + "ControlFailCount, " + "ControlPointStatusDownCount, " + "ControlSentCount, " + "FailureCount, " + "ResyncCount, " + "ControlDeliveryCount, " + "RecallRetryCount, " + "ResendCount " + "FROM tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay WITH (NOLOCK) INNER JOIN tmdsDatabaseStatic.dbo.tblCodeLines ON tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay.codeline = tmdsDatabaseStatic.dbo.tblCodeLines.CodelineNumber " + "WHERE ControlPointName NOT LIKE 'WD%' AND ControlPointName NOT LIKE 'WM%' and day = '15' and month = @month and year = @year) AS T1 " + "GROUP BY ControlPointName, Codeline, Type, SubName, Day, Month, Year, FailureCount " + "UNION" + "SELECT T1.ControlPointName AS 'CP Name', " + "T1.codeline AS Codeline, " + "T1.Type as Type, " + "T1.SubName AS 'Sub Division', " + "T1.Day AS 'Day', " + "T1.Month AS 'Month', " + "T1.Year AS 'Year', " + "SUM(T1.OutOfSyncCount) AS 'Out Of Sync', " + "SUM(T1.RecallLocalCount) AS 'Recalls Local', " + "SUM(T1.RecallRemoteCount) AS 'Recalls Remote', " + "SUM(T1.IndicationCount) AS 'Indications', " + "SUM(T1.ControlFailCount) AS 'Control Failures', " + "SUM(T1.ControlPointStatusDownCount) AS 'Control Point Down', " + "SUM(T1.ControlSentCount) AS 'Controls', " + "SUM(T1.FailureCount) AS 'Failures', " + "SUM(T1.ResyncCount) AS 'Resyncs', " + "SUM(T1.ControlDeliveryCount) AS 'Controls', " + "SUM(T1.RecallRetryCount) AS 'Recall Retries', " + "SUM(T1.ResendCount) AS 'Resends' " + "FROM(SELECT ControlPointName, " + "StationNumber, " + "codeline, " + "tmdsDatabaseStatic.dbo.tblCodelines.Legacytype as Type, " + "SubName, " + "Day, " + "Month, " + "Year, " + "OutOfSyncCount, " + "RecallLocalCount, " + "RecallRemoteCount, " + "IndicationCount, " + "ControlFailCount, " + "ControlPointStatusDownCount, " + "ControlSentCount, " + "FailureCount, " + "ResyncCount, " + "ControlDeliveryCount, " + "RecallRetryCount, " + "ResendCount " + "FROM tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay WITH (NOLOCK) INNER JOIN tmdsDatabaseStatic.dbo.tblCodeLines ON tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay.codeline = tmdsDatabaseStatic.dbo.tblCodeLines.CodelineNumber " + "WHERE ControlPointName NOT LIKE 'WD%' AND ControlPointName NOT LIKE 'WM%' and day = @day and month = @month and year = @year) AS T1 " + "GROUP BY ControlPointName, Codeline, Type, SubName, Day, Month, Year, FailureCount " + "Order by 'Control Failures' desc"; SqlCommand cmd = new SqlCommand { Connection = conn, CommandType = CommandType.Text, CommandText = sql }; cmd.Parameters.AddWithValue("@month", Month); cmd.Parameters.AddWithValue("@year", Year); cmd.Parameters.AddWithValue("@day", Day); SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd); radGv.Visible = true; radGv.Visible = true; radGv.EnableAlternatingRowColor = true; radGv.Text = "CTC Three Day Report"; dataAdapter.Fill(dt); radGv.DataSource = dt; conn.Close(); var excelML = new ExportToExcelML(radGv) { ExportVisualSettings = true }; //Format the columns radGv.Columns[0].ExcelExportType = DisplayFormatType.Custom; radGv.Columns[0].ExcelExportFormatString = "0; (0);"; radGv.Columns[1].ExcelExportType = DisplayFormatType.Custom; radGv.Columns[1].ExcelExportFormatString = "0; (0);"; radGv.Columns[2].ExcelExportType = DisplayFormatType.Custom; radGv.Columns[2].ExcelExportFormatString = "0; (0);"; radGv.Columns[3].ExcelExportType = DisplayFormatType.Custom; radGv.Columns[3].ExcelExportFormatString = "0; (0);"; radGv.Columns[4].ExcelExportType = DisplayFormatType.Custom; radGv.Columns[4].ExcelExportFormatString = "0; (0);"; radGv.Columns[5].ExcelExportType = DisplayFormatType.Custom; radGv.Columns[5].ExcelExportFormatString = "0; (0);"; radGv.Columns[6].ExcelExportType = DisplayFormatType.Custom; radGv.Columns[6].ExcelExportFormatString = "0; (0);"; radGv.Columns[7].ExcelExportType = DisplayFormatType.Custom; radGv.Columns[7].ExcelExportFormatString = "0; (0);"; radGv.Columns[8].ExcelExportType = DisplayFormatType.Custom; radGv.Columns[8].ExcelExportFormatString = "0; (0);"; radGv.Columns[9].ExcelExportType = DisplayFormatType.Custom; radGv.Columns[9].ExcelExportFormatString = "0; (0);"; radGv.Columns[10].ExcelExportType = DisplayFormatType.Custom; radGv.Columns[10].ExcelExportFormatString = "0; (0);"; radGv.Columns[11].ExcelExportType = DisplayFormatType.Custom; radGv.Columns[11].ExcelExportFormatString = "0; (0);"; radGv.Columns[12].ExcelExportType = DisplayFormatType.Custom; radGv.Columns[12].ExcelExportFormatString = "0; (0);"; radGv.Columns[13].ExcelExportType = DisplayFormatType.Custom; radGv.Columns[13].ExcelExportFormatString = "0; (0);"; radGv.Columns[14].ExcelExportType = DisplayFormatType.Custom; radGv.Columns[14].ExcelExportFormatString = "0; (0);"; radGv.Columns[15].ExcelExportType = DisplayFormatType.Custom; radGv.Columns[15].ExcelExportFormatString = "0; (0);"; excelML.FileExtension = "xml"; excelML.SheetMaxRows = ExcelMaxRows._1048576; excelML.SheetName = "CTC Three Day Report"; excelML.ExportVisualSettings = true; excelML.RunExport(@"C:\#support\Reports\CTCStatistics\CTCThreeDayReport.xml"); Cursor.Current = Cursors.Default; } catch (Exception ex) { MessageBox.Show(ex.Message, "Critical Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }