SQL query

Question

Hi,

 

  I got the below-mentioned output based on the query.I want to show only one Tammy Hanson and  sum of Commission paid for this .Is it posible.Please any one help me.

 output and query:

Vendor  Date of Last Check  Date of check for month Amount  Sales Rep      Commission
Boston Warehouse    12/12/2017  12/12/2017  919.00  NULL    Tammy Hanson    183.80
Woodlink            12/12/2017  12/12/2017  979.86  NULL    Tammy Hanson    195.97

SELECT Vendor,CASE WHEN CONVERT(varchar(50), DateModified, 101)='01/01/1900' THEN '' ELSE CONVERT(varchar(50), DateModified, 101) end
   as 'Date of Last Check', CASE WHEN CONVERT(varchar(50), PaycommissionDate, 101)='01/01/1900' THEN '' ELSE CONVERT(varchar(50), PaycommissionDate, 101) end
   AS 'Date of check for month',SUM([Original $ Total]) 'Amount',NULL,[Sales Rep],SUM([Commission $ paid]) 'Commission $ paid'
from Tbl_Commission_Reconcilation  where Vendor is not null and Month([Order Date])= MONTH (Getdate())
group by Tbl_Commission_Reconcilation.Vendor,CONVERT(varchar(50), DateModified, 101),[Sales Rep],PaycommissionDate

Answer 1

1. imports namesapce in class

using System.Data.SqlClient;

2. create a function in that class which retrun sqlConnection

public static SqlConnection Connection()

         {

            string connectionString = "Data Source=sandeepss-PC;database=Development;user=sa;password=knowdev";

            SqlConnection con = new SqlConnection();

            con.ConnectionString = connectionString;

            return con;

        }

3 create a function which return all login names in a data Table

 public static DataTable getUserLogin()

         {

             DataTable dt = new DataTable();

             SqlConnection con = Connection();

             SqlCommand cmd = new SqlCommand();

             cmd.CommandText = "select userLogin from tbl_login";

             cmd.Connection = con;

             con.Open();

             SqlDataReader reader = cmd.ExecuteReader();

             dt.Load(reader);

             return dt;

         }