A count query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

A count query

Author

Topic

coya33
Starting Member

6 Posts

Posted - 2011-12-16 : 06:16:52

Hi Everyone I wanted to create an SQL query where I wanted to count a certain actions and then acquire the total sum. The only issue i cannot use stored procedures.

Scenario: I wanted to know the query to count total of actions put in by a person on the same day on same case

This is how counting needs to be done:
Check that NO Action "A" is present then only check
1> If Action "B" is present,and count multiple "B" actions as 1.

2> If Action "C" is present,and count multiple "C" actions as 1.

3> If Action "D" is present,and count multiple "D" actions as 1.

OTHERWISE Count as 1 for multiple Action "A"

This information is to be derived from 2 tables and so the answer that would be obtained at the end would be as follows:

Count of A + Count of B + Count of C + Count of D

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-12-16 : 06:40:39

hi.

You'll probably get a better response to the question if you can provide some sample data and expected results.

Preferably if you can write DML to give the data to us.

Something like


DECLARE @table1 TABLE (
    [valA] CHAR(1)
    , [valB] VARCHAR(10)
    , .....
    )
INSERT @table1 ([valA], [valB], ....)
      SELECT 'a', 'foo', ....
UNION SELECT 'b', 'woo', ....

Etc.

Welcome to sql team.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 06:42:12

whats the field you have for getting person information? its better if you can post table structures

generically it would of the form


SELECT PersonID,
CASE WHEN COUNT(CASE WHEN ACtion='A' THEN 1 ELSE NULL END) =0
THEN COUNT(DISTINCT Action) ELSE 1 END AS Cnt
FROM Table
GROUP BY PersonID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

coya33
Starting Member

6 Posts

Posted - 2011-12-16 : 07:44:33

Thanks Vishakha and Charlie for Your suggestion. As per advice let me try and make the question more elaborate:
@Charlie: The only constraint is that I cannot write a DML nor SPs. It would need to be query based.

Table Names with required field names:
1. CaseRaw -
Fields here - CaseID (Primary Key)
CaseAssignedID - (Person)

2. CaseAction - Record duplication allowed here
Fields here - Case ID (Foreign Key)
CaseActionCodeDate
CaseActionCode
CCP ID (Person)

Sample Data:

CaseActionCode = CSSET, SMCLS, EXCLS, 6DAC, FOCLS

Scenario: To count total Action Codes a Person has worked on same day, same case.

Follow is the Logic to count the action codes:

If CSSET Action Code is present >=1 then count it as 1
IF CSSET Action Code is NOT PRESENT THEN ONLY count the following:If SMCLS Action Code is present >=1 then count it as 1
If EXCLS Action Code is present >=1 then count it as 1
If 6DAC Action Code is present >=1 then count it as 1
If FOCLS Action Code is present >=1 then count it as 1

So Say if there is a Person "X" who has worked on 10 cases in the same day with no Action Code "CSSET" present then I would need to obtain the count for remaining action codes as per the logic.
The end result would be the sum of Count for SMCLS (if any present) + Count for EXCLS (if any present)+ Count for FOCLS (if any present).....

But there are other 10 cases in the same day by the same person "X" where CSSET Action Code is present then count as per the logic and then in the end derive the complete total count.

This total count would include count for all action codes which were present that day or not as per the logic.

Hope this helps.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 09:39:03

that means if atleast 1 case has CSSET code, then count will become 1 right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

coya33
Starting Member

6 Posts

Posted - 2011-12-16 : 10:34:51

Thanks Vishaka for your help and guidance.

This means that if say 1 case on the same day and by the same Person has 10 CSSET action codes then count it as 1
if another case , say case 2 has 12 CSSET Action Codes then count it as 1, but the total Number of CSSET Action Codes becomes 2.

Hope this helps...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 10:43:11

[code]
SELECT [CCP ID],SUM(Cnt) AS TotalCnt
FROM
(
SELECT cr.[CCP ID],cr.[Case ID],
CASE WHEN COUNT(CASE WHEN CaseActionCode='CSSET' THEN 1 ELSE NULL END)>0 THEN 1 ELSE COUNT(DISTINCT CASE WHEN CaseActionCode<>'CSSET' THEN CaseActionCode ELSE NULL END) END AS Cnt
FROM CaseRaw cr
INNER JOIN CaseAction ca
ON ca.[Case ID] = cr.[Case ID]
AND cr.[CCP ID] = ca.[CaseAssignedID]
GROUP BY cr.[CCP ID],cr.[Case ID]
)t
GROUP BY [CCP ID]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

coya33
Starting Member

6 Posts

Posted - 2011-12-19 : 04:35:43

Hey Vishkha,

Thanks a ton for the help.... yes the output is correct. I will now alter the query such that I able to account the other Action Codes "SMCLS",EXCLS,FOCLS & 6DAC in absence of CSSET action code for the put in by the same person on the same case and on same day....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 04:43:50

you'r welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

coya33
Starting Member

6 Posts

Posted - 2011-12-19 : 10:32:12

Hi Vishakha,

I updated the logic and used it as follows:

SELECT SUM(csum) FROM (
Select SUm(CSSET) AS csum From (SELECT count (CASE WHEN CASEACTIONCODE ='CMNT' THEN CASEACTIONCODE
--(CASE WHEN (CASEACTIONCODE IN ('BKCNG','REV','CLOSE')) THEN 1 ELSE NULL END )
else null end ) as CSSET
FROM MERCED.M_MERCHANTCASERAW CR
INNER JOIN merced.m_merchantCaseAction ca
ON CA.CASEID = CR.CASEID
AND CA.CCPID = CR.CASEASSIGNEDID
--AND CA.CASEACTIONCODEDATE >= '11-AUG-11' AND CA.CASEACTIONCODEDATE <= '12-AUG-11' /*TO BE UN-COMMENTED*/
GROUP BY CA.CCPID, CA.CASEID, CA.CASEACTIONCODE)
WHERE CSSET IS NOT NULL
uNION
Select SUm(OTHER) From (SELECT count (CASE WHEN CASEACTIONCODE <>'CMNT' THEN
(CASE WHEN (CASEACTIONCODE IN ('BKCNG','REV','CLOSE')) THEN 1 ELSE NULL END )
else null end ) as OTHER
FROM MERCED.M_MERCHANTCASERAW CR
INNER JOIN merced.m_merchantCaseAction ca
ON CA.CASEID = CR.CASEID
AND CA.CCPID = CR.CASEASSIGNEDID
--AND CA.CASEACTIONCODEDATE >= '11-AUG-11' AND CA.CASEACTIONCODEDATE <= '12-AUG-11' /*TO BE UN-COMMENTED*/
GROUP BY CA.CCPID, CA.CASEID, CA.CASEACTIONCODE)
WHERE OTHER IS NOT NULL
)

Please let me know if you have any suggestions

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 11:04:55

but this will give you single sum but i thought what you need is sum per unique value of [CCP ID]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources