Bdtjtk

I'm new to databases, and I need help. I have a database with two tables ([tb_TotalH], [tb_PricePerH]). The first table [tb_TotalH] shows the total number of hours per day, and in the second table [tb_PricePerH] are the prices of the hour with the date they were created.

I need to calculate the total amount (hours per day multiply price per hour).
But the trick is that the price of the hour is not fixed, it changes and I need a price in the period when working hours were made. And I also need to get the price when I make query for filter of dates to get the price in label.
I have no idea how to start this!
This is some of my code that I've done so far:

     SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=|DataDirectory|Database1.mdf;Integrated Security=True");



    void DisplayData()

    {



        con.Open();

        DataTable dt = new DataTable();

        sda = new SqlDataAdapter("select * from tb_TotalH", con);

        sda.Fill(dt);

        dataGridView1.DataSource = dt;

        con.Close();



        dataGridView1.AllowUserToAddRows = false;

        dataGridView1.AutoResizeColumns();

    }





    void BetweenDates()

    {

        DateTime dtFrom = Convert.ToDateTime(dtp1.Value);

        DateTime dtTo = Convert.ToDateTime(dtp2.Value);

        SqlDataAdapter mda = new SqlDataAdapter("select * from DailyDataEntry where Date_H between '" + dtFrom.ToShortDateString() + "' and '" + dtTo.ToShortDateString() + "' ", con);







        System.Data.DataSet ds = new System.Data.DataSet();

        con.Open();

        mda.Fill(ds, "root");

        dataGridView1.DataSource = ds.Tables["root"];

        dataGridView1.Refresh();

        con.Close();

    }



    void getPrice()

    {

        SqlDataReader rdr = null;

        SqlCommand cmd = null;



        con.Open();





        string CommandText = "SELECT Price, Tax FROM tb_PricePerH WHERE Date_M = @date"; 

        cmd = new SqlCommand(CommandText);

        cmd.Connection = con;



        cmd.Parameters.AddWithValue("@date", "4/22/2018");  



        rdr = cmd.ExecuteReader();



        while (rdr.Read())

        {

            lbl1.Text = Convert.ToDouble(rdr["PricePerHour"].ToString());

            lbl2.Text = Convert.ToDouble(rdr["Tax"].ToString());

        }





        con.Close();

    }

I have tried to mock your scenario with List.

var tablePricePerHours = new List<TablePricePerHours>

    {

        new TablePricePerHours{ Price = 10, Tax = 19, Date = new DateTime(2018,1,5)},

        new TablePricePerHours{ Price = 10.5, Tax = 21.5, Date = new DateTime(2018,4,22)},

        new TablePricePerHours{ Price = 11, Tax = 22.5, Date = new DateTime(2018,6,19)},

        new TablePricePerHours{ Price = 11.5, Tax = 23, Date = new DateTime(2018,10,30)},

        new TablePricePerHours{ Price = 12, Tax = 23.2, Date = new DateTime(2018,11,1)}

    };



    var tableTotalHours = new List<TableTotalHours>

    {

        new TableTotalHours{ Date = new DateTime(2018,02,15), TotalHours = 5},

        new TableTotalHours{ Date = new DateTime(2018,02,19), TotalHours =10},

        new TableTotalHours{ Date = new DateTime(2018,02,25), TotalHours = 8},

        new TableTotalHours{ Date = new DateTime(2018,03,29), TotalHours = 7.5},

        new TableTotalHours{ Date = new DateTime(2018,07,05), TotalHours = 9},

        new TableTotalHours{ Date = new DateTime(2018,07,06), TotalHours = 1.5},

        new TableTotalHours{ Date = new DateTime(2018,07,07), TotalHours = 12},

        new TableTotalHours{ Date = new DateTime(2018,10,22), TotalHours = 10},

        new TableTotalHours{ Date = new DateTime(2018,10,22), TotalHours = 10},

        new TableTotalHours{ Date = new DateTime(2018,11,1), TotalHours = 8},

        new TableTotalHours{ Date = new DateTime(2018,12,21), TotalHours = 8.5},

        new TableTotalHours{ Date = new DateTime(2018,12,22), TotalHours = 9},

    };

Where TablePricePerHours and TableTotalHours are defined as

public class TableTotalHours

{

    public int Id {get;set;}

    public DateTime Date{get;set;}

    public double TotalHours{get;set;}

}





public class TablePricePerHours

{

    public int Id{get;set;}

    public double Price{get;set;}

    public double Tax{get;set;}

    public DateTime Date{get;set;}

}

Now you can get the required result with Linq as

var result = tableTotalHours.Select(x=> new 

                                    {

                                        Date=x.Date,

                                        TotalHours=x.TotalHours,

                                        CalculatedPrice = tablePricePerHours.Where(c=> (x.Date - c.Date).Ticks>0)

                                                                            .OrderBy(c=> (x.Date - c.Date).Ticks)

                                                                            .First()

                                                                            .Price * x.TotalHours

                                    });

Output

You can find the same here