Solved

Simple query

Posted on 2013-11-21
7
175 Views
Last Modified: 2013-12-06
Hej,

MS SQL Server 2008.

I have a table that records patient visits.  This table has three columns:

visit_date - the date of the visit

clinic_id - the identity of the clinic where the visit was made

npi_points - a value we measure for the patient on each visit.


Can someone help me with a query that returns the average change in npi_points between the 1st and 2nd visits, 1st and 3rd visits, and 1st and 4th visit?

I also need the same values for a clinic that has an id of 0.

Something likes this:

Visit        Total average change          Clinic "0" average change
2nd          12.34                                   11,54
3rd          15.98                                    14,74
4th          18,65                                     18,6

Open in new window

0
Comment
Question by:soozh
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39665877
Can you give some examples of data in the actual table? Also will you please define the average change between one visit and another, how you calculate that value?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 39666769
how do you relate the visits to the same patient?  (surely you've got a patient id on the table as well?)
how/which patient at clinic 0 are the results to be shown with?

what happens if there are more or less than 4 visits?

can the patient change clinic's between visits , and what do you want to happen in those cases?

some example data and the expected result would assist.

... is there any time period for a vist to have occurred within to either exclude from the process or that would reset the visit sequence...

e.g. do you expect the visits to occur normally on a monthly basis ... and would ignore/reset if visits  had a gap of 6 months say?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39668315
taking a leap, I assume there is also a patient_id involved, and the the 1st, 2nd, 3rd, 4th visits have to be aligned by patient. with some random numbers and guesswork, does this help? Note the output here is one row per clinic.
    CREATE TABLE pat_visits
    	([id] int, [patient_id] int, [visit_date] datetime, [clinic_id] int, [npi_points] decimal(5,2))
    ;
    	
    INSERT INTO pat_visits
    	([id], [patient_id], [visit_date], [clinic_id], [npi_points])
    VALUES
    	(1, 111001, '2013-02-01 00:00:00', 2001, 12.09),
    	(2, 111002, '2013-02-01 00:00:00', 2001, 15.75),
    	(3, 111001, '2013-03-03 00:00:00', 2001, 7.16),
    	(4, 111002, '2013-04-02 00:00:00', 2001, 17.52),
    	(5, 111001, '2013-05-02 00:00:00', 2001, 5.06),
    	(6, 111002, '2013-06-01 00:00:00', 2001, 9.11),
    	(7, 111001, '2013-07-01 00:00:00', 2001, 13.93),
    	(8, 111002, '2013-07-31 00:00:00', 2001, 13.73),
    	(9, 111001, '2013-08-30 00:00:00', 2001, 10.39),
    	(10, 111002, '2013-09-29 00:00:00', 2001, 9.06),
    	(11, 111001, '2013-10-29 00:00:00', 2001, 11.66),
    	(12, 111002, '2013-11-28 00:00:00', 2001, 16.35),
    	(13, 111001, '2013-12-28 00:00:00', 2001, 11.97),
    	(14, 111002, '2014-01-27 00:00:00', 2001, 12.69),
    	(15, 111001, '2014-02-26 00:00:00', 2001, 8.35),
    	(16, 111002, '2014-03-28 00:00:00', 2001, 11.86),
    	(17, 111001, '2014-04-27 00:00:00', 2001, 7.74),
    	(18, 111002, '2014-05-27 00:00:00', 2001, 6.18),
    	(19, 111001, '2014-06-26 00:00:00', 2001, 6.19),
    	(20, 111002, '2014-07-26 00:00:00', 2001, 16.84)
    ;

**Query 1**:

    WITH
    CTE AS (
            SELECT
            *
            , row_number() over (partition BY patient_id ORDER BY visit_date ASC) AS rn
            FROM pat_visits
            )
    SELECT
          clinic_id
        , avg(v2change) AS v2change
        , avg(v3change) AS v3change
        , avg(v4change) AS v4change
    FROM (
          SELECT
                v1.patient_id
              , v1.clinic_id
              , v2.npi_points - v1.npi_points AS v2change
              , v3.npi_points - v2.npi_points AS v3change
              , v4.npi_points - v3.npi_points AS v4change
          FROM CTE AS v1
          INNER JOIN CTE AS v2 ON v1.patient_id = v2.patient_id AND v1.rn = 1 AND v2.rn = 2
          INNER JOIN CTE AS v3 ON v1.patient_id = v3.patient_id AND v2.rn = 2 AND v3.rn = 3
          INNER JOIN CTE AS v4 ON v1.patient_id = v4.patient_id AND v3.rn = 3 AND v4.rn = 4
    	) AS v
    GROUP BY
          clinic_id
    	
    	
    

**[Results][2]**:
    
    | CLINIC_ID | V2CHANGE | V3CHANGE | V4CHANGE |
    |-----------|----------|----------|----------|
    |      2001 |    -1.58 |   -5.255 |    6.745 |



  [1]: http://sqlfiddle.com/#!3/723f7/1

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 39668503
more like this perhaps?

but you do need to explain what is meant by a clinic
and what date ranges you will eventually be applying this to...

 ;WITH
    CTE AS (
            SELECT
            x.*
            , row_number() over (partition BY patient_id ,clinic_id
                                 ORDER BY visit_date ASC) AS rn
            FROM pat_visits as x
            )
            SELECT                          
               v2.rn 
              , Avg(case when v1.clinic_id = 0 then null else v2.npi_points - v1.npi_points end) as AvgChange
              , Avg(case when v1.clinic_id = 0 then v2.npi_points - v1.npi_points else null end) as clinic0
          FROM CTE AS v1
          INNER JOIN CTE AS v2 
            ON v1.patient_id = v2.patient_id 
           AND v1.rn = 1 AND v2.rn in (2,3,4)
    	  group by v2.rn 
         order by v2.rn
    	
	

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39668728
I see I missed the differences being from the 1st visit in all cases.

but perhaps also that the row_number() should be partitioned by both clinic and patient, in case a client moves between clinics
WITH
CTE AS (
        SELECT
        *
        , row_number() over (partition BY clinic_id, patient_id ORDER BY visit_date ASC) AS rn
        FROM pat_visits
        )
SELECT
      clinic_id
    , avg(v2change) AS v2change
    , avg(v3change) AS v3change
    , avg(v4change) AS v4change
FROM (
      SELECT
            v1.patient_id
          , v1.clinic_id
          , v2.npi_points - v1.npi_points AS v2change
          , v3.npi_points - v1.npi_points AS v3change
          , v4.npi_points - v1.npi_points AS v4change
      FROM CTE AS v1
      INNER JOIN CTE AS v2 ON v1.clinic_id = v2.clinic_id AND v1.patient_id = v2.patient_id AND v2.rn = 2
      INNER JOIN CTE AS v3 ON v1.clinic_id = v3.clinic_id AND v1.patient_id = v3.patient_id AND v3.rn = 3
      INNER JOIN CTE AS v4 ON v1.clinic_id = v4.clinic_id AND v1.patient_id = v4.patient_id AND v4.rn = 4
	) AS v
GROUP BY
      clinic_id
;

-- http://sqlfiddle.com/#!3/723f7/9 

Open in new window

0
 

Author Comment

by:soozh
ID: 39669869
sorry for the lack of feedback... i am on holiday in London... but i will look at the solutions later tonight.
0
 

Author Closing Comment

by:soozh
ID: 39700817
100% as usual.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

785 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