Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Can someone write me a query.

Posted on 2011-02-22
4
Medium Priority
?
242 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

596 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