Count question.. - 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

Transact-SQL (2000)

Count question..

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2006-07-19 : 19:01:47

I'm working on a report on the number of claims filed within the last 12 months. I have my query built so that I get eveything I need with the the exception of one column which is giving me problems.

I know that 416 claims have been filed within the requested time frame. But, what I have found is that in some cases the same person has filed multiple claims. Is it possible to get just a COUNT or SUM based off the employee ID number of just the number of employees who have filed.

Here is my query which is working and where I'd like to add the last item.

Any suggestions, thanks.

GC

SELECT Airport as 'Station',
COUNT(*)as 'Reported Injuries',
SUM([NoLTCnt]) as 'Non L. T. Injuries',
SUM([IDCnt]) as 'I. D. Injuries',
SUM([TDCnt]) as 'T. D. Injuries',
SUM([IDandTD]) as 'ID & TD Injuries',
SUM([OSHA]) as 'OSHA Recordable',
SUM([CurOnID]) as 'Currently Off Work on I.D.',
SUM([CurOnTD]) as 'Currently On T.D.',
SUM([CurOnOther]) as 'Currently Off Work Due Other Reason'
From(
SELECT First_Report_Events.Emp_Name as 'Name',
First_Report_Events.Emp_No as 'EmpID',
CTwentyOne.EmployeeStatus as 'C21Status',
First_Report_Events.Emp_Company_Code as 'CoCode',
Profit_Centers_Table.AirportCode as 'Airport',
First_Report_Events.Station as 'Station',
First_Report_Events.Branch as 'Branch',
First_Report_Events.Injury_Date as 'IDDate',
First_Report_Events.AccCntSince1997 as 'PriorIDs',
First_Report_Events.Claim_Status as 'ClaimStatus',
CASE WHEN First_Report_Events.Claim_Status = 'ID' THEN 1 ELSE 0 END 'CurOnID',
CASE WHEN First_Report_Events.Claim_Status = 'TD' THEN 1 ELSE 0 END 'CurOnTD',
CASE WHEN First_Report_Events.Claim_Status NOT IN ('FD','ID','TD') THEN 1 ELSE 0 END 'CurOnOther',
First_Report_Events.Claim_Status_Desc as 'ClaimStatusDesc',
First_Report_Events.BodyPart as 'BodyPart',
First_Report_Events.OSHA_Recordable as 'OSHATrk',
CASE WHEN First_Report_Events.OSHA_Recordable IN ('R', 'Y') THEN 1 ELSE 0 END 'OSHA',
CASE WHEN (First_Report_Events.ID_Count < 1)
and (First_Report_Events.TD_Count < 1) THEN 1 ELSE 0 END 'NoLTCnt',
CASE WHEN (First_Report_Events.ID_Count > 0)
and (First_Report_Events.TD_Count > 0) THEN 1 ELSE 0 END 'IDandTD',
First_Report_Events.ID_Count as 'IDs',
CASE WHEN (First_Report_Events.ID_Count > 0)
and (First_Report_Events.TD_Count < 1) THEN 1 ELSE 0 END 'IDCnt',
First_Report_Events.TD_Count as 'TDs',
CASE WHEN (First_Report_Events.TD_Count > 0)
and (First_Report_Events.ID_Count < 1) THEN 1 ELSE 0 END 'TDCnt'
FROM First_Report_Events
JOIN Profit_Centers_Table ON First_Report_Events.Station = Profit_Centers_Table.StationCode
and First_Report_Events.Emp_Company_Code = Profit_Centers_Table.CompanyCode
JOIN Branches_Table ON First_Report_Events.Branch = Branches_Table.BranchCode
LEFT JOIN CTwentyOne ON First_Report_Events.Emp_No = CTwentyOne.EmployeeNumber
WHERE First_Report_Events.Emp_Company_Code = 'AA'
and Profit_Centers_Table.StationsreportingtoPSVC = 'DOMESTIC'
and Profit_Centers_Table.StationsreportingtoRegion = 'Western'
and Profit_Centers_Table.AirportCode NOT IN ('HOU', 'PIT')
and Branches_Table.BRANCHCATEGORYSMALL IN ('PSVC', 'RSVC', 'APTMGT')
and (Profit_Centers_Table.StationsReportingToCARGO <> 'CARGO'
or Branches_Table.CARGOCATEGORIES <> 'CGO - Field')
and DateDiff(Day,First_Report_Events.Injury_Date, Getdate())<= 365
) X
GROUP BY Airport
ORDER BY Airport

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-19 : 20:19:57

If u want to get the count, grouped by emp #,

Select Empid, count(*) from UrTbl group by Empid

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources