Link to home
Start Free TrialLog in
Avatar of smartin0924
smartin0924

asked on

Calculate by Field

Hello,

I am trying to write a query that will show me the number of patients I have on each team.  Here is the query I pulled out of the query builder in sql, but I can not figure out how to get it to sort by team and calculate the number of patients per team.  Any help would be greatly appreciated.

SELECT     TOP (100) PERCENT dbo.VW_PT_BRANCH.TEAM_DESCRIPTION AS Expr1
FROM         dbo.O_DATASET INNER JOIN
                      dbo.PT_BASIC ON dbo.O_DATASET.DATASET_ID = dbo.PT_BASIC.DATASET_ID INNER JOIN
                      dbo.PT_ADMISSION ON dbo.PT_BASIC.PATIENT_ID = dbo.PT_ADMISSION.PATIENT_ID INNER JOIN
                      dbo.VW_PT_BRANCH ON dbo.PT_BASIC.PATIENT_CODE = dbo.VW_PT_BRANCH.PATIENT_CODE
GROUP BY dbo.PT_ADMISSION.ADMIT_DATE, dbo.PT_ADMISSION.TERMINATION_DATE, dbo.O_DATASET.DATASET_NAME,
                      dbo.VW_PT_BRANCH.TEAM_DESCRIPTION
HAVING      (dbo.O_DATASET.DATASET_NAME = 'Hospice Care Team Inc') AND (NOT (dbo.PT_ADMISSION.ADMIT_DATE IS NULL)) AND
                      (dbo.PT_ADMISSION.TERMINATION_DATE IS NULL)

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ps: avoid the query builder as much as possible. it produces quite ugly and usually unefficient code :)
Avatar of smartin0924
smartin0924

ASKER

You Rock!!!!  Thanks for the quick responce.