Hey i m working on same...i also implemented it .i m giving u my code accrding u change ur query n also code..
public void calculateWeek()
{
try
{
DateTime date = Convert.ToDateTime(txtenddate2.Text);
DateTime startDate = date;
DateTime lastdate = DateTime.Now;
DateTime endDate = startDate.AddMonths(1).AddDays(-1);
int i = 0;
for (DateTime result = startDate; result <= endDate; result = lastdate.AddDays(1))
{
if (result.Day != 31)
{
weeks[i, 0] = result.ToString();
if (result.AddDays(14) >= endDate)
{
weeks[i, 1] = endDate.ToString();
lastdate = result.AddDays(14);
}
else
{
weeks[i, 1] = result.AddDays(14).ToString();
lastdate = result.AddDays(14);
}
}
else
{
weeks[i - 1, 1] = result.ToString();
lastdate = result.AddDays(14);
}
i++;
}
if (weeks.Length > 0)
{
Session["frt_1"] = Convert.ToDateTime(weeks[0, 0].ToString());
Session["frt_2"] = Convert.ToDateTime(weeks[0, 1].ToString());
Session["frt_3"] = Convert.ToDateTime(weeks[1, 0].ToString());
Session["frt_4"] = Convert.ToDateTime(weeks[1, 1].ToString());
}
}
catch (Exception ex)
{
}
}
string str = "select s.StaffName,isnull(t.HourlyCharges,0) as HourlyCharges,isnull(sum(convert(float,TotalTime)),0)as TotalTime, " +
" isnull((isnull(sum(convert(float,TotalTime)),0)* t.HourlyCharges),0)as Charges,isnull(sum(OpeAmt),0)as OpeAmt,dbo.TotalTime(isnull(sum(convert(float,TotalTime)),0)) as mints,convert(varchar(50),dbo.TotalTime(isnull(sum(convert(float,TotalTime)),0))/60) + '.' + convert(varchar(50),dbo.TotalTime(isnull(sum(convert(float,TotalTime)),0))%60) as timet " +
" from dbo.Staff_Master as s right join dbo.Job_Staff_Table as j on s.StaffCode=j.StaffCode " +
" left join dbo.TimeSheet_Table as t on t.JobId=j.JobId and t.StaffCode=j.StaffCode and t.Date>='" + Convert.ToDateTime(weeks[0, 0].ToString()) + "' " +
" and t.Date <='" + Convert.ToDateTime(weeks[1, 1].ToString()) + "' where j.JobId='" + DropJob.SelectedValue + "' and t.CLTId='" + DropClient.SelectedValue + "' group by s.StaffName,t.HourlyCharges";
DataTable dt = db.GetDataTable(str);