Given below is the database table(TableA).
SlNo TeamName WorkInfo SubmitDate
---- ------- ------- ---------
1 Team1 0 10/10/2014
2 Team1 0 10/10/2014
3 Team1 1 11/10/2014
4 Team2 1 12/15/2014
5 Team2 1 10/10/2014
6 Team3 0 10/10/2014
7 Team3 1 10/10/2014
I have two text boxes for selecting start and end date. I want to display details in between that dates.
Given below code is used to find the date difference between start date and end date and displayed on another text box named as "`txtTotalDays`".
Code:
DateTime d1 = txtStartDate.Text != string.Empty ? Convert.ToDateTime(txtStartDate.Text) : DateTime.MinValue;
DateTime d2 = txtEndDate.Text != string.Empty ?
Convert.ToDateTime(txtEndDate.Text) : DateTime.MinValue;
TimeSpan tspan = d2 - d1;
txtTotalDays.Text = tspan.TotalDays.ToString()
Given below query is giving the details and count based on the start date and end date.
SQL Query
SELECT TeamName,Count(DISTINCT CASE WHEN WorkInfo = 1 THEN SlNo end) AS Count1 , Count(DISTINCT CASE WHEN WorkInfo = 0 THEN SlNo end) AS Count0 FROM tableA
WHERE (SubmitDate BETWEEN @start AND @end) GROUP BY TeamName
The above query shows the out put like given below:
TeamName Count1 Count0
-------- ----- ------
Team1 1 2
Team2 2 0
Team3 1 1
I want to display the percentage based on the `count and date difference` . Help me to modify the above solution. `The expecting output sample is given below:`
I selected start date and end date. I got the date difference (txtTotalDays.Text) as 10. Based on that date selection I want get values like given below.
TeamName Count1 Count0 Percentage1 Percentage0
-------- ----- ------ ----------- -----------
Team1 1 2 10% 20%
Team2 2 0 20% 0%
Team3 1 1 10% 10%
Help me to find a proper solution. Thank You.