SQL Query Query

Sign in to answer this question.

• 

  Jignesh Kumar3y agoPosted Apr 19, 2023, 8:20 AM

  Accepted answer

  Hello Ridhi,

  Please try this one,

  SELECT Location AS "Stock Location", CertifiedBy AS "Stock Owner",
  cast(CertifiedOn as date) AS "Date : DD/MMM/YYYY HH:MM",
  COUNT(*) AS "Total Stock"
  FROM Certifications
  GROUP BY Location, CertifiedBy,cast(CertifiedOn as date);

   
• 

  Amit Mohanty3y agoPosted Apr 19, 2023, 8:41 AM

  Try this

  SELECT Location AS 'Stock Location', CertifiedBy AS 'Stock Owner', 
         CONVERT(VARCHAR(17), CertifiedOn, 113) AS 'Date : DD/MMM/YYYY HH:MM', 
         COUNT(*) AS 'Total Stock'
  FROM YourTableNameHere
  GROUP BY Location, CertifiedBy, CertifiedOn
  ORDER BY Location;

   
• 

  Naimish Makwana3y agoPosted Apr 18, 2023, 3:28 PM

  Use below query.

  SELECT Location AS "Stock Location", CertifiedBy AS "Stock Owner", 
  DATE_FORMAT(CertifiedOn, '%d/%b/%Y %H:%i') AS "Date : DD/MMM/YYYY HH:MM", 
  COUNT(*) AS "Total Stock"
  FROM Certifications
  GROUP BY Location, CertifiedBy;
  

  Thanks
• 

  Tuhin Paul3y agoPosted Apr 18, 2023, 3:07 PM

  In my above code Replace "your_connection_string_here" with your SQL Server connection string, and "your_query_here" with the SQL query from above. Replace "yourGridView" with the ID of your GridView control.
• 

  Tuhin Paul3y agoPosted Apr 18, 2023, 3:06 PM

  Assuming you have a SQL Server database table named "Certifications" with the columns "ID", "Location", "CertifiedBy", "CertifiedOn", "Month", and "Year", you can use the following SQL query:

  SELECT Location AS 'Stock Location', CertifiedBy AS 'Stock Owner', CONVERT(varchar(16), CertifiedOn, 106) + ' ' + CONVERT(varchar(5), CertifiedOn, 108) AS 'Date : DD/MMM/YYYY HH:MM', COUNT(*) AS 'Total Stock'
  FROM Certifications
  WHERE Month = 4 AND Year = 2023
  GROUP BY Location, CertifiedBy, CertifiedOn

  
  This query will group the certifications by location, certified by, and certified on date and time, and count the number of certifications for each group. The WHERE clause filters the results to only include certifications from April 2023. 
  To display the query results in a GridView in the front-end, you can bind the GridView to a DataTable filled with the query results. 

  using (SqlConnection connection = new SqlConnection("your_connection_string_here"))
  {
      SqlCommand command = new SqlCommand("your_query_here", connection);
      DataTable table = new DataTable();
      SqlDataAdapter adapter = new SqlDataAdapter(command);
      adapter.Fill(table);
      yourGridView.DataSource = table;
      yourGridView.DataBind();
  }