Solved

tsql syntax

Posted on 2011-02-18
5
304 Views
Last Modified: 2012-05-11
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
Comment
Question by:soozh
  • 2
  • 2
5 Comments
 
LVL 13

Accepted Solution

by:
devlab2012 earned 500 total points
ID: 34931928
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
 
LVL 40

Expert Comment

by:Sharath
ID: 34932590
Do you have fixed no. of years?
Do you want the result pivoted as mentioned above?
0
 

Author Comment

by:soozh
ID: 34932761
i dont know which years they are... i hoped that the years could be taken from the data.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34944207
Can you provide some sample data with expected result?
0
 

Author Closing Comment

by:soozh
ID: 35068657
Not exactly the solution i wanted... but it worked..
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now