locked
c# String Query to find top 10 Repeated records in database by Date Range RRS feed

  • Question

  • I would like to thanks everyone on this site for the help I have received.

    An earlier post I was seeking a way to find the Top 10 repeated records

    which was resolved, I want to take it a step further and find the Top 10

    records by a Date range. The code below is still repeating Top 10 in Data Base 

    Not by Date Range. dt10 and dt11 are the Date Time Picker.

     string queryString = "SELECT * FROM HotSheetID WHERE ILC.Today BETWEEN '{0}' AND '{1}' , dt10.Text, dt11.Text";
                    //    string.Format("WHERE ILC.Today BETWEEN '{0}' AND '{1}' ", dt10.Text, dt11.Text);
                    queryString = "SELECT TOP 10 " +
                                  "Part,(Part) As PartNumber" +
                                  "Part,COUNT(Part) As PartCount " +
                                  "FROM ILC " +
                                  "GROUP BY Part " +
                                  "Order by COUNT(Part) DESC";
    
                    loadDataGrid(queryString);


    Booney440

    Thursday, September 6, 2018 8:28 PM

Answers

  • Probably something like this:

     string queryString = String.Format("SELECT TOP 10 " +
                                  "Part,(Part) As PartNumber" +
                                  "Part,COUNT(Part) As PartCount " +
                                  "FROM ILC " +
                                  "WHERE ILC.Today BETWEEN '{0}' AND '{1}' " +
                                  "GROUP BY Part " +
                                  "Order by COUNT(Part) DESC",
                                  dt10.Text, dt11.Text);
    
                    loadDataGrid(queryString);

    • Marked as answer by Booney440 Saturday, September 8, 2018 11:41 AM
    Saturday, September 8, 2018 6:29 AM
    Answerer

All replies

  • Please add your "WHERE" clause inside your queryString.

    Your current code sets a simple query string with WHERE, then overwrites it with another SQL query with "ORDER BY" but no "WHERE" clause.

    Friday, September 7, 2018 6:18 AM
    Answerer
  • I dont understand could you Please explain further?

    Tried this but blank screen.

     string queryString = "SELECT Today FROM HotSheetID WHERE Today BETWEEN '{0}' AND '{1}' , dt10.Text, dt11.Text"+
                                         "SELECT TOP 10 " +
                                         "Part,(Part) As PartNumber" +
                                         "Part,COUNT(Part) As PartCount " +
                                         "FROM ILC " +
                                         "GROUP BY Part " +
                                         "Order by COUNT(Part) DESC";
    
                                         loadDataGrid(queryString);


    Booney440


    • Edited by Booney440 Friday, September 7, 2018 8:04 PM update
    Friday, September 7, 2018 7:47 PM
  • Probably something like this:

     string queryString = String.Format("SELECT TOP 10 " +
                                  "Part,(Part) As PartNumber" +
                                  "Part,COUNT(Part) As PartCount " +
                                  "FROM ILC " +
                                  "WHERE ILC.Today BETWEEN '{0}' AND '{1}' " +
                                  "GROUP BY Part " +
                                  "Order by COUNT(Part) DESC",
                                  dt10.Text, dt11.Text);
    
                    loadDataGrid(queryString);

    • Marked as answer by Booney440 Saturday, September 8, 2018 11:41 AM
    Saturday, September 8, 2018 6:29 AM
    Answerer
  • I see the WHERE statement has to be with the GROUP and Order,

    Thank you for the help.


    Booney440

    Saturday, September 8, 2018 11:42 AM