Click here to Skip to main content
14,268,557 members
Rate this:
Please Sign up or sign in to vote.
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

1 solution

Rate this:
Please Sign up or sign in to vote.

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.

ADDITION:
---------
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



ADDITION 2
-----------
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
Comments
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)




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