Solved

Can someone write me a query.

Posted on 2011-02-22
4
237 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 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 41

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Error - Query 6 49
Powershell finalizing the end of an array. 4 24
SQL Calculation syntax based on a parameter 2 30
Not listening to where 1 17
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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

763 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