asked on

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?

Nakul Vachhrajani

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?

soozh

ASKER

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

SOLUTION

Nakul Vachhrajani

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial