?
Solved

Can someone write me a query.

Posted on 2011-02-22
4
Medium Priority
?
241 Views
Last Modified: 2012-06-21
To keep it simple I have a table that has two columns: PatientID, VisitYear.

PatientID is the patient identity and VisitYear is the year of their visit to a clinic.  It not a physical year, but the number of years since the patients first visit.

A patient can make a number of visits per year.  So the table could look like:

PatientID     VisitYear
A                     0
A                     0
A                     0
A                     1
A                     1
A                     2
B                     0
B                     1
B                     1
B                     2
B                     2
C                     0
C                     0
C                     1

Note that patient C has not made any visits during VisitYear 2.  This is important as you will see later.

I need some TSQL that can produce me a cross table that shows VisitYear against the number of patients that have made 0, 1, 2 etc visits.

So for the above data i would expect

VisitYear   0 visits   1 visit   2 visits   3 visits
0                   0            1           1            1           -- A made 3 visits, B made 1 and C made 2.
1                   0            1           2            0           -- A and B made 2 visits,  C made 1 visit.
2                   1            1           1            0          -- C made 0 visits, A made 1 visit, B made 2 visits

I think most of this is quite easy... what i can not do it return the fact that patient C did not make any visits in the second treatment year.   This is because there is no row in the table for the visits the patients have not made!

The query should be able to support any number of VisitYears.   The example uses years 0 to 2, but in the future there can be more years of treatment.

0
Comment
Question by:soozh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 34958533
Do you have control over the number of visits? In your example the max. number of visits are 3. If this is not a fixed number, the number of columns are varying. You need a complete dynamic query in that case. BTW, what is your database version?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 34958597
0
 

Author Comment

by:soozh
ID: 34958699
i am using sql sever 2008.  The number of treaments can vary... generally no more than 10 or 12 but i can not give a maximum.
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 1000 total points
ID: 34973826
try this. replace your_table with your actual table name. Also I have tested this attached the result set.
DECLARE  @sql NVARCHAR(MAX) 

SELECT @sql = 'select VisitYear, '; 

WITH ColCTE 
     AS (SELECT 'SUM(Case cnt when ' + CONVERT(VARCHAR,cnt) + ' then 1 else 0 end) [' + CONVERT(VARCHAR,cnt) + ' Visits]' AS dynamicCol
           FROM (SELECT DISTINCT ISNULL(t1.cnt,0) cnt 
                   FROM (SELECT DISTINCT PatientID FROM your_table) p 
                        CROSS JOIN (SELECT DISTINCT VisitYear FROM your_table) v 
                                   LEFT JOIN (  SELECT PatientID, VisitYear, COUNT(* ) cnt 
                                                  FROM your_table 
                                              GROUP BY PatientID, VisitYear) t1 
                                     ON p.PatientID = t1.PatientID 
                                        AND v.VisitYear = t1.VisitYear) t1) 
SELECT @sql = @sql + RTRIM(SUBSTRING(ISNULL((SELECT ',' + dynamicCol 
                                               FROM ColCTE 
                                             for xml path('')),' '),2,2000)) 

SELECT @sql = @sql + ' FROM (SELECT v.VisitYear, 
                 ISNULL(t1.cnt,0) cnt 
            FROM (SELECT DISTINCT PatientID FROM your_table) p 
                 CROSS JOIN (SELECT DISTINCT VisitYear FROM your_table) v 
                            LEFT JOIN (  SELECT PatientID, VisitYear, COUNT(* ) cnt 
                                           FROM your_table 
                                       GROUP BY PatientID, VisitYear) t1 
                              ON p.PatientID = t1.PatientID 
                                 AND v.VisitYear = t1.VisitYear) t2 
          GROUP BY VisitYear' 

EXEC( @Sql)
/*
VisitYear	0 Visits	1 Visits	2 Visits	3 Visits
0	0	1	1	1
1	0	1	2	0
2	1	1	1	0
*/

Open in new window

0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

771 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