Solved

Can someone write me a query.

Posted on 2011-02-22
4
236 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
  • 2
4 Comments
 
LVL 40

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 250 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 40

Assisted Solution

by:Sharath
Sharath earned 250 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…

809 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