14,268,557 members
Rate this:
See more:
i have duty table in sql server
in the table i have 7 days
sat, sun, mond,..... friday
in all field the user insert 'M' as a duty type and another type of duty for 7 day of week, i want to count how many type of duty 'M' for week.

What I have tried:

```select Dutyid,EmpName,sum(CASE WHEN saturday ='M' THEN 1 ELSE 0 END) SAT.
from DutyView
Group by Dutyid,EmpName```
Posted
Updated 30-Jul-19 21:41pm
v2

Rate this:

## Solution 1

If I understand the question correctly you have a table containing a separate column for each weekday?

If this is the case, I would suggest different kind of structure. Instead of using separate columns, use rows. In other words the table could look something like

DutyTable
---------
- EmpName
- Weekday
- DutyType

And data could be something like (1 = sunday, 7 = saturday)
```EmpName   Weekday   DutyType
-------   -------   --------
Mr Smith  1         A
Mr Smith  2         M
Mr Smith  3         M
Mr Smith  4         M
Mr Smith  5         M
Mr Smith  6         M
Mr Smith  7         B```

Now your query could look something like

```SELECT EmpName,
SUM(CASE DutyType
WHEN 'M' THEN 1
ELSE 0
END)
FROM DutyTable
GROUP BY EmpName```

This is just a simple example so you probably need to add proper columns to match your requirements.

---------
Consider the following example
```CREATE TABLE DutyTest (
DutyId   int,
EmpName  varchar(100),
DayId    int,
DutyType char
);

INSERT INTO DutyTest VALUES
(1,  'Mr Smith', 1, 'M'),
(2,  'Mr Smith', 2, 'M'),
(3,  'Mr Smith', 3, 'M'),
(4,  'Mr Smith', 4, 'M'),
(5,  'Mr Smith', 5, 'A'),
(6,  'Mr Smith', 6, 'D'),
(7,  'Mr Smith', 7, 'A'),
(8,  'Mr Wick',  1, 'A'),
(9,  'Mr Wick',  2, 'A'),
(10, 'Mr Wick',  3, 'D'),
(11, 'Mr Wick',  4, 'A'),
(12, 'Mr Wick',  5, 'M'),
(13, 'Mr Wick',  6, 'A'),
(14, 'Mr Wick',  7, 'A');

SELECT EmpName,
SUM(CASE DutyType
WHEN 'M' THEN 1
ELSE 0
END) AS M_Days
FROM DutyTest
GROUP BY EmpName;```

The result for the query would be
```EmpName    M_Days
--------   ------
Mr Smith   4
Mr Wick    1```

-----------
To query the current table in the database you could use something like the following
```CREATE TABLE DutyTest2 (
DutyId    int,
EmpName   varchar(100),
Sunday    char,
Monday    char,
Tuesday   char,
Wednesday char,
Thursday  char,
Friday    char,
Saturday  char
);

INSERT INTO DutyTest2 VALUES
(1,  'Mr Smith', 'M', 'M', 'M', 'M', 'A', 'D', 'A'),
(2,  'Mr Smith', 'M', 'M', 'D', 'A', 'A', 'D', 'A'),
(3,  'Mr Wick',  'A', 'A', 'A', 'M', 'A', 'D', 'A'),
(4,  'Mr Wick',  'M', 'M', 'D', 'A', 'M', 'M', 'A');

SELECT DutyId,
EmpName,
SUM(CASE Sunday    WHEN 'M' THEN 1 ELSE 0 END
+ CASE Monday    WHEN 'M' THEN 1 ELSE 0 END
+ CASE Tuesday   WHEN 'M' THEN 1 ELSE 0 END
+ CASE Wednesday WHEN 'M' THEN 1 ELSE 0 END
+ CASE Thursday  WHEN 'M' THEN 1 ELSE 0 END
+ CASE Friday    WHEN 'M' THEN 1 ELSE 0 END
+ CASE Saturday  WHEN 'M' THEN 1 ELSE 0 END) AS M_Days
FROM DutyTest2
GROUP BY DutyId,
EmpName;```

The query should produce
```DutyId   EmpName    M_Days
------   --------   ------
1        Mr Smith   4
2        Mr Smith   2
3        Mr Wick    1
4        Mr Wick    4```

Hope this helps
v3
Member 11398913 31-Jul-19 9:44am

Thanks ,my table structure is
DutyID , EmpName, Sat,Sun,mon,tus,wed,thu,fri
in every Field the user insert Duty type as letter 'M' for example in 3 or more days
sat 'M'
sun 'M'
mon 'M'
tus 'M'
wed 'A'
thu 'D'
fri 'A'
i want to count if Emp. has more than 3 day duty type 'M'
than show hem Else not showen.
i hope my question is clear cause my english not ok
Wendelius 31-Jul-19 9:56am

See the updated solution
Wendelius 31-Jul-19 9:47am

As I suggested, don't use weekdays as columns, put them in rows instead. Based on the data you provided, your table structure could be
- DutyId
- EmpName
- WeekDay
- DutyType

In other words, currently you have 1 row containing all weekdays for a week. And what I'm suggesting is that you would have 7 rows for a single week, each weekday as a different row.
Member 11398913 31-Jul-19 19:00pm

thanks alot for your support, but i have alot of data in my table i cant change the structure i will lose a daa and time to do that, i try this code and its almost work but still total not counting the total days i hope some one help by this last step.
DECLARE @DayCount int =0
DECLARE @DayCount2 int =0
DECLARE @DayCount3 int =0
DECLARE @DayCount4 int =0
DECLARE @DayCount5 int =0
DECLARE @DayCount6 int =0
DECLARE @DayCount7 int =0
DECLARE @Totsi int =0
SELECT
DutyID,Saturday,
Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,
(CASE WHEN Saturday = 'M' THEN @DayCount +1 ELSE @DayCount END) Day1,
(CASE WHEN Sunday = 'M' THEN @DayCount2 +1 ELSE @DayCount2 END) Day2,
(CASE WHEN Monday = 'M' THEN @DayCount3 +1 ELSE @DayCount3 END) Day3,
(CASE WHEN Tuesday = 'M' THEN @DayCount4 +1 ELSE @DayCount4 END) Day4,
(CASE WHEN Wednesday = 'M' THEN @DayCount5 +1 ELSE @DayCount5 END) Day5,
(CASE WHEN Thursday = 'M' THEN @DayCount6 +1 ELSE @DayCount6 END) Day6,
(CASE WHEN Friday = 'M' THEN @DayCount7 +1 ELSE @DayCount7 END) Day7,
SUM(@DayCount + @DayCount2 + @DayCount3 +
@DayCount4 + @DayCount5 + @DayCount6 + @DayCount7) Totals
FROM MATRONDutyView
Group By
DutyID,Saturday,
Sunday,Monday,Tuesday,Wednesday,Thursday,Friday
HAVING
DutyID <> 1 ;
Wendelius 1-Aug-19 3:04am

Updated the solution, see addition 2
Wendelius 1-Aug-19 3:23am

Note that with the example data I used, you wouldn't need SUM nor GROUP BY clause, but it's up to your data if those are actually needed. I included them because the original post had both SUM and GROUP BY
Member 11398913 1-Aug-19 7:10am

i Appreciate your support, thank for all of you for helping...
Wendelius 1-Aug-19 8:01am

You're welcome :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 OriginalGriff 190 Maciej Los 185 MadMyche 120 CPallini 95 Richard MacCutchan 74

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100