How to count duty days in duty table in SQL server

Question

1.00/5 (1 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:

SQL

select Dutyid,EmpName,sum(CASE WHEN saturday ='M' THEN 1 ELSE 0 END) SAT.
from DutyView
Group by Dutyid,EmpName

Posted 30-Jul-19 14:48pm

Member 11398913

Updated 30-Jul-19 21:41pm

Wendelius

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Accepted Answer · 2019-07-30T21:40:00

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

SQL

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

SQL

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

SQL

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