Post reply

Count number of visits each day

  • February 18, 2016 at 6:52 pm

    #311587

    In a clinic we need to find how many visits a doctor got from each patient each day. A simple group by clause could do it but the problem is multiple visits by the same patient are considered one unless they are separated by another patient visit. This is a real life problem and not any school assignment.

    Here is the structure of the table . As you can see the first two visits by PAT01 should be considered one and same of PAT02 last two visits.

    CREATE TABLE VISITS (

    DOCTOR VARCHAR(50),

    PATIENT VARCHAR(50),

    [DATE OF VISIT] VARCHAR(15),

    [TIME OF VISIT] VARCHAR(5)

    );

    INSERT INTO VISITS VALUES ( 'DOC01', 'PAT01', '2016/02/18', '08:00');

    INSERT INTO VISITS VALUES ( 'DOC01', 'PAT01', '2016/02/18', '09:00');

    INSERT INTO VISITS VALUES ( 'DOC01', 'PAT02', '2016/02/18', '10:00');

    INSERT INTO VISITS VALUES ( 'DOC01', 'PAT01', '2016/02/18', '11:00');

    INSERT INTO VISITS VALUES ( 'DOC01', 'PAT02', '2016/02/18', '12:00');

    INSERT INTO VISITS VALUES ( 'DOC01', 'PAT02', '2016/02/18', '13:00');

    INSERT INTO VISITS VALUES ( 'DOC01', 'PAT03', '2016/02/18', '14:00');

  • February 18, 2016 at 7:10 pm

    #1859718

    WITH cte AS (SELECT DOCTOR, PATIENT, [DATE OF VISIT], [TIME OF VISIT],

    LEAD(PATIENT, 1, 'x') OVER (ORDER BY DOCTOR, [DATE OF VISIT], [TIME OF VISIT]) AS NEXT_PATIENT

    FROM dbo.VISITS)

    SELECT DOCTOR, PATIENT, COUNT(*) as VISITS

    FROM cte

    WHERE NEXT_PATIENT = 'x'

    OR NEXT_PATIENT != PATIENT

    GROUP BY DOCTOR, PATIENT;

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • February 18, 2016 at 11:54 pm

    #1859729

    we're not able to understand Exact requirement . please give us to sample output. we will try to help you.

    select COUNT(Patient), CONVERT(varchar,[DATE OF VISIT],121),PATIENT from #VISITS

    group by [DATE OF VISIT],PATIENT

  • February 19, 2016 at 2:51 am

    #1859750

    This is called a problem in the "gaps and islands" category (islands in this case). Itzik Ben-Gan has come up with some very creative and smart solutions for problems in this area.

    Itzik writes about this here: http://sqlmag.com/sql-server-2012/solving-gaps-and-islands-enhanced-window-functions

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • February 19, 2016 at 2:58 am

    #1859752

    Thanks. This works!

  • February 19, 2016 at 8:39 am

    #1859841

    mansur.azam (2/19/2016)

    Thanks. This works!

    Not sure what you're referring to. I'm thinking that it's Orlando's code that worked (seems like it should just from reading it).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 19, 2016 at 6:31 pm

    #1859943

    Jeff Moden (2/19/2016)

    mansur.azam (2/19/2016)

    Thanks. This works!

    Not sure what you're referring to. I'm thinking that it's Orlando's code that worked (seems like it should just from reading it).

    Yes. That's the one I'm talking about.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply