Given below is the database table(TableA).
SlNo TeamName WorkInfo
---- ------- -------
1 Team1 0
2 Team1 0
3 Team1 1
4 Team2 1
5 Team2 1
6 Team2 1
7 Team3 0
8 Team3 1
I have two text boxes for selecting start and end date. I want to display details in between that dates.
Given below query is giving the details and count based on the start date and end date.
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 3 0
Team3 1 1
I want to display the percentage based on the `greatest count` in `Count1` and `Count0` . Help me to modify the above solution. `The expecting output sample is given below:`
Here in `Count1` 3 is the greates value.Based on that I have to find the percentage of Count1 values. And in `Count0` 2 is the greatest value.Based on that I have to find the percentage of Count0 values.
TeamName Count1 Count0 Percentage1 Percentage0
-------- ----- ------ ----------- -----------
Team1 1 2 33.33% 100%
Team2 3 0 100% 0%
Team3 1 1 33.33% 50%
Help me to find a proper solution. Thank You.