Query syntax

Hello,

I have an sql statement that returns data regarding patient visits. Like this:

Select pat_id, VisitDate, ….. etc from PatientData order by pat_id asc, VisitDate asc

A patient can have more than one visit so it returns data that looks like:
pat_id  visitdate,
23, 2012-03-01, …….
23, 2012-07-22, …….
23, 2014-08-03, ……
25, 2012-05-01, …….
25, 2012-09-20, …….
26, 2012-11-19, …….
26, 2013-02-22, …….
26, 2015-02-03, ……

Open in new window

Now I would like to add a flag which indicates the first visit for each patient. Like
pat_id, visitdate, firstvisit,
23, 2012-03-01, 1, …….
23, 2012-07-22, 0, …….
23, 2014-08-03, 0, ……
25, 2012-05-01, 1, …….
25, 2012-09-20, 0, …….
26, 2012-11-19, 1, …….
26, 2013-02-22, 0, …….
26, 2015-02-03, 0,……

Open in new window


I guess I need to have some sub select over each patients records but I can’t get my head round it.

Anyone who can help?
soozhAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SharathConnect With a Mentor Data EngineerCommented:
Select pat_id, visit_date,
Case rn when 1 then 1 else 0 end as FirstVisit
From (
Select *, row_number() over (partition by pat_id order by visit_date) rn 
From PatientData) t1

Open in new window

0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Does this help? (Works on SQL Server 2012 and above).

DECLARE @patientTable TABLE (pat_id INT, visitdate DATE)

INSERT INTO @patientTable (pat_id, visitdate)
VALUES (23, '2012-03-01'),
       (23, '2012-07-22'),
       (23, '2014-08-03'),
       (25, '2012-05-01'),
       (25, '2012-09-20'),
       (26, '2012-11-19'),
       (26, '2013-02-22'),
       (26, '2015-02-03');

SELECT pt.pat_id,
       pt.visitdate,
       CASE WHEN pt.visitdate = MIN(pt.visitdate) OVER (PARTITION BY pt.pat_id ORDER BY pt.visitdate ASC)
            THEN 1
            ELSE 0
       END AS IsFirstvisit
FROM @patientTable AS pt;

Open in new window


If it does not help, can you please share the version of SQL Server that you are using?
0
 
soozhAuthor Commented:
Hello,

Well maybe i should have told you the sql server version.

It is SQL Server 2008.  

So i wonder if the order by works in the partition statement because i am getting a syntax error.

/richard
0
 
Nakul VachhrajaniConnect With a Mentor Technical Architect, Capgemini IndiaCommented:
Yes, the version is important because a lower version restricts the ability to use better and more readable syntax alternatives :)

A CTE can be used to implement this in SQL 2008. See below.

DECLARE @patientTable TABLE (pat_id INT, visitdate DATE)

INSERT INTO @patientTable (pat_id, visitdate)
VALUES (23, '2012-03-01'),
       (23, '2012-07-22'),
       (23, '2014-08-03'),
       (25, '2012-05-01'),
       (25, '2012-09-20'),
       (26, '2012-11-19'),
       (26, '2013-02-22'),
       (26, '2015-02-03');

----SQL 2012 and beyond
--SELECT pt.pat_id,
--       pt.visitdate,
--       CASE WHEN pt.visitdate = MIN(pt.visitdate) OVER (PARTITION BY pt.pat_id ORDER BY pt.visitdate ASC)
--            THEN 1
--            ELSE 0
--       END AS IsFirstvisit
--FROM @patientTable AS pt;

--SQL 2008
;WITH PatientInitialVisits (PatientId, InitialVisitDate)
AS (SELECT pt.pat_id AS PatientId,
           MIN(pt.visitdate) AS InitialVisitDate
    FROM @patientTable AS pt
    GROUP BY pt.pat_id
   )
SELECT outerPt.pat_id,
       outerPt.visitdate,
       CASE WHEN piv.InitialVisitDate IS NOT NULL THEN 1
            ELSE 0
            END AS IsFirstVisit
FROM @patientTable AS outerPt
LEFT OUTER JOIN PatientInitialVisits AS piv ON outerPt.pat_id = piv.PatientId
                                           AND outerPt.visitdate = piv.InitialVisitDate;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.