Solved

Can someone write me a query.

Posted on 2011-02-22
4
233 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 142

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

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. …
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…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

914 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

18 Experts available now in Live!

Get 1:1 Help Now