• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

tsql syntax

Hello,


I have a table that has patient-id, visit year, plus some other columns....

It is possible to write some sql that produces a simple frequency table that shows me year and number of visits for each patient?

I am particularly interested in seeing which years the patient did not make any visit, but also need to see how many visits per year in the other years.  i.e. i dont want to exclude the years the patients did not visit.

Also if it is possible I would like to go a step further an see the distribution of the number of visits.  What i mean by this is I would like to see for each year how many patients made zero visits, how many one, how many two, how many three etc.

Thanks.
0
soozh
Asked:
soozh
  • 2
  • 2
1 Solution
 
devlab2012Commented:
The following example will help you:

CREATE TABLE Patients
(
      PatientID      int,
      VisitYear      varchar(4)
)
GO

INSERT INTO Patients VALUES(1, 2010)
INSERT INTO Patients VALUES(2, 2010)
INSERT INTO Patients VALUES(2, 2011)
INSERT INTO Patients VALUES(1, 2009)
INSERT INTO Patients VALUES(1, 2009)
GO

SELECT PatientID, VisitYear, count(*) YearlyVisits FROM Patients
GROUP BY PatientID, VisitYear
GO

WITH PatientsCTE(PatientID, VisitYear, YearlyVisits)
AS
(
      SELECT PatientID, VisitYear, count(*) YearlyVisits FROM Patients
      GROUP BY PatientID, VisitYear
)
select PatientID, isNull([2008], 0) Year2008,  isNull([2009],0) Year2009,
isNull([2010],0) Year2010, isNull([2011], 0) Year2011 from PatientsCTE
pivot(sum(YearlyVisits) for VisitYear IN ([2008], [2009], [2010], [2011]))
AS PatientVisits
GO
0
 
SharathData EngineerCommented:
Do you have fixed no. of years?
Do you want the result pivoted as mentioned above?
0
 
soozhAuthor Commented:
i dont know which years they are... i hoped that the years could be taken from the data.
0
 
SharathData EngineerCommented:
Can you provide some sample data with expected result?
0
 
soozhAuthor Commented:
Not exactly the solution i wanted... but it worked..
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now