Count specific text from all columns in a row

Question

1.00/5 (1 vote)

See more:

Hi all,

I have an employee attendance table data like this

 ID      NAME  DAY1  DAY2  DAY3  DAY4  DAY5  DAY6  DAY7
------- ------ ----  ----  ----  ----  ----  ----  ----
empid001  jo	2      G    G	  SL	G     G      G

I need the out put like,

 ID      NAME  DAY1  DAY2  DAY3  DAY4  DAY5  DAY6  DAY7   COUNTofG  COUNTofSL  COUNTof2
------- ------ ----  ----  ----  ----  ----  ----  ----  ---------  ---------  --------
empid001  jo	2      G    G	  SL	G     SL      G       4         2          1

my result set having entire employee list with the daily attendance details.
simply, i don't know how to get the count of the attendance types(cl,sl,etc.,) from all columns.

Thanks in Advance,
Rajan.

Posted 21-Nov-13 3:26am

NPSSR

Updated 21-Nov-13 3:51am

v2

Add a Solution

Comments

joshrduncan2012 21-Nov-13 9:38am

Help you with what? You haven't shown us what you have attempted and where you are stuck. Please "Improve Question" and provide what you have attempted and where you are stuck.

NPSSR 21-Nov-13 9:54am

my SP returns the set of attendance details without any counts of the attendance types.

Maciej Los 21-Nov-13 11:51am

Please, provide more details about employee and attendance table. What is the body of SP (code)? Help us to understand your issue...

2 solutions

Solution 2

Your table structure is wrong, you need to normalize it.

EmpID	Day	AttendanceCode
empid001	Day1	2
empid001	Day2	G
empid001	Day3	G
empid001	Day4	SL
empid001	Day5	G
empid001	Day6	SL
empid001	Day7	G

Now it's simple enough to make a count on the occurencies.
Then make a pivot for the presentation, which of course belongs to the presentation layer, not the database.

Posted 21-Nov-13 21:20pm

Jörgen Andersson

Comments

NPSSR 23-Nov-13 1:33am

When I was started from the scratch, thought to keep the details like this only. But, it takes many 30/31 records for every employee for a month. As, we have 500 employees it takes almost 15000 records per month. Thats why I decided to have 1 record per month. Thanks for your suggestions.

Jörgen Andersson 23-Nov-13 9:10am

The number of record isn't a problem, the amount of data is virtually the same for both models. But the aggregation is a problem with your denormalized table.
You should notice that Maciejs solution solves the problem you have by making an Unpivot into the table structure I've suggested.
So while Maciej has answered your question, in a very proper way I should add. The problem you have is with the structure.
Next problem you'll get is when the management wants a report per month. How will you solve that?

NPSSR 25-Nov-13 7:31am

Yes. I am having two more columns like EmpMonth and EmpYear. Anyhow, still I am having thought process regarding this table structure and fine tuning. Thanks for your support.

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)

Maciej Los · Accepted Answer · 2013-11-21T21:18:00

Please, have a look at example:

SQL

DECLARE @pvttable1 TABLE(ID VARCHAR(30), [NAME] VARCHAR(30), DAY1 VARCHAR(30), DAY2 VARCHAR(30), DAY3 VARCHAR(30), DAY4 VARCHAR(30), DAY5 VARCHAR(30), DAY6 VARCHAR(30), DAY7 VARCHAR(30))

INSERT INTO @pvttable1 (ID, [NAME], DAY1, DAY2, DAY3, DAY4, DAY5, DAY6, DAY7)
    VALUES('empid001', 'jo', '2', 'G', 'G', 'SL', 'G', 'G', 'G'),
    ('empid022', 'oj', 'G', 'SL', 'G', 'SL', 'G', 'G', 'G'),
    ('empid333', 'yo', '2', 'G', 'SL', 'SL', 'G', 'G', 'G'),
    ('empid999', 'oy', 'SL', 'G', 'G', 'SL', 'G', 'G', 'SL')


DECLARE @pvttable2 TABLE (ID VARCHAR(30), [Name] VARCHAR(30), [Code] VARCHAR(30), CountOfCode INT)

INSERT INTO @pvttable2 (ID, [Name], [Code], CountOfCode)
SELECT ID, [Name], [Code], COUNT([Code]) CountOfCode
FROM (
        SELECT ID, [NAME], [Day], [Code]
        FROM(
            SELECT ID, [NAME], DAY1, DAY2, DAY3, DAY4, DAY5, DAY6, DAY7
            FROM @pvttable1
            ) AS pvt
        UNPIVOT ([Code] FOR [Day] IN (DAY1, DAY2, DAY3, DAY4, DAY5, DAY6, DAY7)
        ) AS unpvt
    ) AS T
GROUP BY ID, [Name], [Code]
ORDER BY ID, [Name], [Code]

SELECT pvt1.ID, pvt1.[Name], pvt1.DAY1, pvt1.DAY2, pvt1.DAY3, pvt1.DAY4, pvt1.DAY5, pvt1.DAY6, pvt1.DAY7, COALESCE(pvt2.[2],0) AS CountOf2, COALESCE(pvt2.G,0)  AS CountOfG, COALESCE(pvt2.SL,0)  AS CountOfSL
FROM @pvttable1 AS pvt1 INNER JOIN (
        SELECT ID, [Name], [2], [G], [SL]
        FROM (
            SELECT *
            FROM @pvttable2
            ) As DT
        PIVOT(SUM(CountOfCode) FOR [Code] IN ([2], [G], [SL])) AS PVT
    ) AS pvt2 ON pvt1.ID = pvt2.ID

Result:

ID		Name	DAY1	DAY2	DAY3	DAY4	DAY5	DAY6	DAY7	Co..f2	Co..fG	Co..fSL
empid001	jo	2	G	G	SL	G	G	G	1	5	1
empid022	oj	G	SL	G	SL	G	G	G	0	5	2
empid333	yo	2	G	SL	SL	G	G	G	1	4	2
empid999	oy	SL	G	G	SL	G	G	SL	0	4	3