Solved

tsql syntax

Posted on 2011-02-18
5
303 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

707 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

12 Experts available now in Live!

Get 1:1 Help Now