Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Set based super challenge Expand / Collapse
Author
Message
Posted Monday, December 9, 2013 3:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 3:40 AM
Points: 40, Visits: 143
Please read the attached txt file.

The challenge is to summarize the data per patient but only as / if the consultant changes.

This is giving me sleepless nights. I've tried ranking functions,self joins and iterative cte's to no avail

Thanks for your help





  Post Attachments 
consultant.txt (38 views, 2.28 KB)
Post #1521326
Posted Monday, December 9, 2013 5:41 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:10 PM
Points: 1,780, Visits: 5,645
Instead of asking people to imagine data in a table, if you provide that data in a readily consumable way, you will get much more help.

Have a read of this : How to post code questions

edit: weird things going on with the url...


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1521361
    Posted Monday, December 9, 2013 5:45 PM


    SSC-Insane

    SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

    Group: General Forum Members
    Last Login: Today @ 8:07 AM
    Points: 22,993, Visits: 31,473
    I may have an idea on how to do this, I just don't have time to convert the info in your text file into DDL and DML statements to do the work. Please read the first article I reference below in my signature block regarding asking for help. If you convert your information in the text file into a readily consumable format (think cut/paste/execute in ssms), you will get much better answers.

    Also, it would help if you would post the code that you have already tried. It is possible that you may have been close and just need a bit more guidance to get the answer.



    Lynn Pettis

    For better assistance in answering your questions, click here
    For tips to get better help with Performance Problems, click here
    For Running Totals and its variations, click here or when working with partitioned tables
    For more about Tally Tables, click here
    For more about Cross Tabs and Pivots, click here and here
    Managing Transaction Logs

    SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
    Post #1521362
    Posted Tuesday, December 10, 2013 6:55 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 8:25 AM
    Points: 7,115, Visits: 13,473
    This doesn't look too challenging. Can you correct both your sample data and your expected output for EpisodeNo 10010? Cheers.

    For those of you looking for sample data to play with in advance of LotusNotes posting the corrected versions:

    DROP TABLE #Consultant;
    WITH Consultant (PatientID, EpisodeNo, Activity_Date, EpisodeType_from, EpisodeType_to, Description, Staff_from, Staff_to, Ward_from, Ward_to) AS (
    SELECT 1, 10001, '01/04/2013 00:01', NULL, 7, 'Admitted', NULL, 'Mr A', 'Ward Z', 'Ward Z' UNION ALL
    SELECT 1, 10001, '01/04/2013 08:01', 7, 11, 'Discharged', 'Mr A', 'Mr A', 'Ward Z', 'Ward Z' UNION ALL

    SELECT 2, 10002, '01/04/2013 00:01', NULL, 7, 'Admitted', NULL, 'Mr A', 'Ward Z', 'Ward Z' UNION ALL
    SELECT 2, 10002, '02/04/2013 00:01', 7, 10, 'Transfer', 'Mr A', 'Mr A', 'Ward Z', 'Ward Y' UNION ALL
    SELECT 2, 10002, '02/04/2013 00:01', 10, 10, 'Transfer', 'Mr A', 'Mr A', 'Ward Y', 'Ward X' UNION ALL
    SELECT 2, 10002, '05/04/2013 00:01', 10, 11, 'Discharged', 'Mr A', 'Mr A', 'Ward X', 'Ward X' UNION ALL

    SELECT 3, 10010, '01/05/2013 00:01', NULL, 7, 'Admitted', NULL, 'Mr A', 'Ward Z', 'Ward Z' UNION ALL
    SELECT 3, 10010, '05/05/2013 00:01', 7, 10, 'Transfer', 'Mr A', 'Mr B', 'Ward Z', 'Ward Z' UNION ALL
    SELECT 3, 10010, '05/05/2013 08:01', 10, 10, 'Transfer', 'Mr B', 'Mr B', 'Ward Z', 'Ward Z' UNION ALL
    SELECT 3, 10010, '06/05/2013 00:01', 10, 10, 'Transfer', 'Mr B', 'Mr B', 'Ward Z', 'Ward Y' UNION ALL
    SELECT 3, 10010, '06/05/2013 08:01', 10, 10, 'Transfer', 'Mr B', 'Mr C', 'Ward Y', 'Ward Y' UNION ALL
    SELECT 3, 10010, '07/06/2013 00:01', 10, 11, 'Discharged', 'Mr C', 'Mr C', 'Ward Y', 'Ward Y' UNION ALL -- changed date

    SELECT 4, 20100, '01/05/2013 00:01', NULL, 7, 'Admitted', NULL, 'Mr A', 'Ward Z', 'Ward Z' UNION ALL
    SELECT 4, 20100, '05/05/2013 00:01', 7, 10, 'Transfer', 'Mr A', 'Mr B', 'Ward Z', 'Ward Z' UNION ALL
    SELECT 4, 20100, '05/05/2013 08:01', 10, 10, 'Transfer', 'Mr B', 'Mr B', 'Ward Z', 'Ward Z' UNION ALL
    SELECT 4, 20100, '06/05/2013 00:01', 10, 10, 'Transfer', 'Mr B', 'Mr B', 'Ward Z', 'Ward Y' UNION ALL
    SELECT 4, 20100, '06/05/2013 08:01', 10, 10, 'Transfer', 'Mr B', 'Mr C', 'Ward Y', 'Ward Z'
    )
    SELECT * INTO #Consultant FROM Consultant



    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
    Exploring Recursive CTEs by Example Dwain Camps
    Post #1521504
    Posted Wednesday, December 11, 2013 6:58 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 8:25 AM
    Points: 7,115, Visits: 13,473
    Gosh, I'm surprised that with a title like this, there have been so few takers - and so little feedback from the OP. Maybe those sleepless nights have caught up. Here's a simple solution for starters.

    ;WITH OrderedData AS (
    SELECT *,
    RangeStart = CASE WHEN cur.Staff_from IS NULL OR cur.Staff_from <> cur.Staff_to THEN 1 ELSE 0 END,
    RangeEnd = CASE WHEN rn = cnt OR cur.Staff_from <> cur.Staff_to THEN 1 ELSE 0 END
    FROM (
    SELECT *,
    rn = ROW_NUMBER() OVER(PARTITION BY c.PatientID, c.EpisodeNo ORDER BY c.Activity_Date),
    cnt = COUNT(*) OVER(PARTITION BY c.PatientID, c.EpisodeNo)
    FROM #Consultant c
    ) cur
    )
    SELECT
    currow.PatientID,
    currow.EpisodeNo,
    [Episode_Start] = currow.Activity_Date,
    [Episode_End] = nextrow.Activity_Date,
    [Staff] = currow.Staff_to,
    [EpisodeType_from] = currow.EpisodeType_to,
    [EpisodeType_to] = nextrow.EpisodeType_to
    FROM OrderedData currow
    OUTER APPLY (
    SELECT TOP 1
    Activity_Date, EpisodeType_to
    FROM OrderedData i
    WHERE i.RangeEnd = 1
    AND i.PatientID = currow.PatientID
    AND i.EpisodeNo = currow.EpisodeNo
    AND i.Activity_Date > currow.Activity_Date
    ORDER BY i.Activity_Date
    ) nextrow
    WHERE currow.RangeStart = 1



    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
    Exploring Recursive CTEs by Example Dwain Camps
    Post #1521879
    Posted Wednesday, December 11, 2013 11:05 AM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 7:06 AM
    Points: 36,711, Visits: 31,161
    ChrisM@Work (12/11/2013)
    Gosh, I'm surprised that with a title like this, there have been so few takers - and so little feedback from the OP. Maybe those sleepless nights have caught up. Here's a simple solution for starters.


    I don't know what's going on with the OP but when I see that ChrisM has worked a problem, I figure there's little left for me to do and move on.


    --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."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1521990
    Posted Wednesday, December 11, 2013 1:31 PM
    SSC Rookie

    SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

    Group: General Forum Members
    Last Login: Thursday, July 3, 2014 3:40 AM
    Points: 40, Visits: 143
    Thank you to all.

    In a fit of inspiration I have come up with my own solution. Although it gives a slightly different result set, I'd be interested in more optimal solutions. Correct me if I'm wrong but my solution appears to use less io and have a lower estimated subtree cost. I provide files with my solution and also the source data.

    Thanks - LotusNotes

    --Consultant Challenge
    -- Use <database>
    --Select * from dbo.consultant -- source table
    --set statistics io on

    with cte1 as
    (
    Select * from --have to do this to harden the ranking function columns
    --so can be used in a where clause
    (
    SELECT [PatientID]
    ,[EpisodeNo]
    ,[Activity_Date]
    --,[EpisodeType_from]
    --,[EpisodeType]
    ,[Description]
    ,[Staff_from]
    ,[Staff_to]
    ,row_number() over (partition by EpisodeNo order by Activity_Date) as Row_num
    ,case
    when [Description]='Admitted' or (Staff_from=Staff_to) then 0
    else row_number() over (partition by EpisodeNo order by Activity_Date)
    end as Staff_change -- any number >0 identifies a staff change
    --,[Ward_from]
    --,[Ward_to]
    FROM [dbo].[consultant]
    --order by EpisodeNo -- this may be a sticking point?
    ) as X


    where
    Row_num= 1 -- admitted
    or Row_num=Staff_change --staff change
    or Description='Discharged'

    ) -- end cte1


    -- do select * first of all to see all the columns
    --select * from
    select
    a.PatientID
    ,a.EpisodeNo
    ,a.Activity_Date as 'From'
    ,b.Activity_Date as 'To'
    ,b.Staff_from as 'Consultant'
    ,case
    when b.Description='Discharged' then b.Description+' by '+b.Staff_to
    else b.Description+' to '+b.Staff_to
    end as 'Result'
    from
    cte1 a join cte1 b -- self join
    on (a.EpisodeNo=b.EpisodeNo) -- groups the rows correctly
    and a.Staff_to=b.Staff_from -- to correlate the rows correctly
    where
    a.Description <>'Discharged' -- gets rid of discharged row





      Post Attachments 
    consultant - Copy.txt (6 views, 3.14 KB)
    consultant_data - Copy.txt (4 views, 5.02 KB)
    Post #1522040
    Posted Wednesday, December 11, 2013 3:39 PM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 7:06 AM
    Points: 36,711, Visits: 31,161
    Until you test it on a much larger set of rows, you don't actually know what's better. I can show you two solutions for a different problem where it looks like one solution will take 0% compared to the other which looks like it will take 100%. When you run the code, the diametric opposite happens. Even the actual execution plan shows the same "mistake".

    --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."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1522084
    Posted Wednesday, December 11, 2013 6:11 PM


    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: Yesterday @ 6:12 PM
    Points: 3,609, Visits: 5,220
    Jeff Moden (12/11/2013)
    ChrisM@Work (12/11/2013)
    Gosh, I'm surprised that with a title like this, there have been so few takers - and so little feedback from the OP. Maybe those sleepless nights have caught up. Here's a simple solution for starters.


    I don't know what's going on with the OP but when I see that ChrisM has worked a problem, I figure there's little left for me to do and move on.


    I feel the same way!

    I did look at this thread yesterday and saw that Chris suggested there was something wrong with the sample data/results so I passed on it at the time.



    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
    Since random numbers are too important to be left to chance, let's generate some!
    Learn to understand recursive CTEs by example.
    Splitting strings based on patterns can be fast!
    Post #1522114
    Posted Thursday, December 12, 2013 2:14 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 8:25 AM
    Points: 7,115, Visits: 13,473
    lotusnotes (12/11/2013)
    Thank you to all.

    In a fit of inspiration I have come up with my own solution. Although it gives a slightly different result set, I'd be interested in more optimal solutions. Correct me if I'm wrong but my solution appears to use less io and have a lower estimated subtree cost. I provide files with my solution and also the source data.

    Thanks - LotusNotes


    -- Your query loses a row from episode 20100. 
    -- If [Description] can be reliably used to determine the start and end of an episode
    -- and [EpisodeNo] is unique to each patient as your code suggests,
    -- then my query can be changed to this:

    ;WITH OrderedData AS (
    SELECT *,
    RangeStart = CASE WHEN [Description] = 'Admitted' OR Staff_from <> Staff_to THEN 1 ELSE 0 END,
    RangeEnd = CASE WHEN [Description] = 'Discharged' OR Staff_from <> Staff_to THEN 1 ELSE 0 END
    FROM #Consultant
    )
    SELECT
    currow.PatientID,
    currow.EpisodeNo,
    [Episode_Start] = currow.Activity_Date,
    [Episode_End] = nextrow.Activity_Date,
    [Staff] = currow.Staff_to,
    [EpisodeType_from] = currow.EpisodeType_to,
    [EpisodeType_to] = nextrow.EpisodeType_to
    FROM OrderedData currow
    OUTER APPLY (
    SELECT TOP 1
    Activity_Date, EpisodeType_to
    FROM OrderedData i
    WHERE i.RangeEnd = 1
    --AND i.PatientID = currow.PatientID
    AND i.EpisodeNo = currow.EpisodeNo
    AND i.Activity_Date > currow.Activity_Date
    ORDER BY i.Activity_Date
    ) nextrow
    WHERE currow.RangeStart = 1

    -- Here's some sample data to test against:

    DROP TABLE #Consultant;
    WITH Consultant (PatientID, EpisodeNo, Activity_Date, EpisodeType_from, EpisodeType_to, Description, Staff_from, Staff_to, Ward_from, Ward_to) AS (
    SELECT 1, 10001, '01/04/2013 00:01', NULL, 7, 'Admitted', NULL, 'Mr A', 'Ward Z', 'Ward Z' UNION ALL
    SELECT 1, 10001, '01/04/2013 08:01', 7, 11, 'Discharged', 'Mr A', 'Mr A', 'Ward Z', 'Ward Z' UNION ALL

    SELECT 2, 10002, '01/04/2013 00:01', NULL, 7, 'Admitted', NULL, 'Mr A', 'Ward Z', 'Ward Z' UNION ALL
    SELECT 2, 10002, '02/04/2013 00:01', 7, 10, 'Transfer', 'Mr A', 'Mr A', 'Ward Z', 'Ward Y' UNION ALL
    SELECT 2, 10002, '02/04/2013 00:02', 10, 10, 'Transfer', 'Mr A', 'Mr A', 'Ward Y', 'Ward X' UNION ALL
    SELECT 2, 10002, '05/04/2013 00:01', 10, 11, 'Discharged', 'Mr A', 'Mr A', 'Ward X', 'Ward X' UNION ALL

    SELECT 3, 10010, '01/05/2013 00:01', NULL, 7, 'Admitted', NULL, 'Mr A', 'Ward Z', 'Ward Z' UNION ALL
    SELECT 3, 10010, '05/05/2013 00:01', 7, 10, 'Transfer', 'Mr A', 'Mr B', 'Ward Z', 'Ward Z' UNION ALL
    SELECT 3, 10010, '05/05/2013 08:01', 10, 10, 'Transfer', 'Mr B', 'Mr B', 'Ward Z', 'Ward Z' UNION ALL
    SELECT 3, 10010, '06/05/2013 00:01', 10, 10, 'Transfer', 'Mr B', 'Mr B', 'Ward Z', 'Ward Y' UNION ALL
    SELECT 3, 10010, '06/05/2013 08:01', 10, 10, 'Transfer', 'Mr B', 'Mr C', 'Ward Y', 'Ward Y' UNION ALL
    SELECT 3, 10010, '07/06/2013 00:01', 10, 11, 'Discharged', 'Mr C', 'Mr C', 'Ward Y', 'Ward Y' UNION ALL -- changed date

    SELECT 4, 20100, '01/05/2013 00:01', NULL, 7, 'Admitted', NULL, 'Mr A', 'Ward Z', 'Ward Z' UNION ALL
    SELECT 4, 20100, '05/05/2013 00:01', 7, 10, 'Transfer', 'Mr A', 'Mr B', 'Ward Z', 'Ward Z' UNION ALL
    SELECT 4, 20100, '05/05/2013 08:01', 10, 10, 'Transfer', 'Mr B', 'Mr B', 'Ward Z', 'Ward Z' UNION ALL
    SELECT 4, 20100, '06/05/2013 00:01', 10, 10, 'Transfer', 'Mr B', 'Mr B', 'Ward Z', 'Ward Y' UNION ALL
    SELECT 4, 20100, '06/05/2013 08:01', 10, 10, 'Transfer', 'Mr B', 'Mr C', 'Ward Y', 'Ward Z'
    )
    SELECT * INTO #Consultant FROM Consultant

    CREATE UNIQUE CLUSTERED INDEX ucx_EpisodeNo_Activity_Date ON #Consultant (EpisodeNo, Activity_Date)

    -- For a real performance test, this sample data set should be scaled up significantly.
    -- I might give it a go over lunch.
    ----------------------------------------------------------------------------------------



    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
    Exploring Recursive CTEs by Example Dwain Camps
    Post #1522200
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse