Aggregated double join tSQL

  • we have three tables name case, case flow and note. Case to Case flow relation is one to N, and case to note relation is also. The case flow has two fields: start date, end date. And the Note has one date field named created on. We want to be able to show the notes grouped by case flows (based on comparison of created on and start, end date) and then grouped by cases. Is there a way to build such query by tSQL?

    Sample Data in attached picture

     

    Attachments:
    You must be logged in to view attached files.
  • It would be easier if you posted the T-SQL table definition and some sample data instead of having it to type over from a screenshot.

    My guess:

    select case, flow, note

    from case inner join flows on case.case=flow.case /* matching cases with flows*/

    left join casenotes on casenotes.case=case.case /*show casenotes if one found, null note when not*/

  • Test data:

    create table #CaseNames (
    "Case ID" varchar(10) not null,
    "Case Name" varchar(50) null,
    constraint CaseName_PK primary key (
    "Case ID")
    )
    create table #CaseFlows (
    "Case Flow ID" varchar(10) not null,
    "Case ID" varchar(10) not null,
    "Start Date" date not null,
    "End Date" date not null,
    constraint CaseFlow_PK primary key (
    "Case Flow ID")
    )
    create table #Notes (
    "Note ID" varchar(10) not null,
    "Case ID" varchar(10) not null,
    "Created On" date not null,
    constraint Note_PK primary key (
    "Note ID")
    )

    insert into #CaseNames ("Case ID", "Case Name")
    values ('cs1', 'Test1'),
    ('cs2', 'Test2')

    insert into #CaseFlows ("Case Flow ID", "Case ID", "Start Date", "End Date")
    values ('cf1', 'cs1', '20210725', '20210729'),
    ('cf2', 'cs2', '20210726', '20210727'),
    ('cf3', 'cs2', '20210727', '20210728'),
    ('cf4', 'cs2', '20210728', '20210731')

    insert into #Notes ("Note ID", "Case ID", "Created On")
    values ('nt1', 'cs1', '20210725'),
    ('nt2', 'cs1', '20210727'),
    ('nt3', 'cs2', '20210727'),
    ('nt4', 'cs2', '20210730')

    Based on your brief description of your requirements, I would offer a query like:

    select
    c."Case ID" as "Case",
    f."Case Flow ID" as Flows,
    n."Note ID" as Notes
    from #CaseNames c
    inner join #CaseFlows f on f."Case ID" = c."Case ID"
    left join #Notes n on n."Case ID" = c."Case ID" and n."Created On" between f."Start Date" and f."End Date"
    group by c."Case ID", f."Case Flow ID", n."Note ID"
    order by "Case", Flows, Notes

    However, that doesn't give us quite the result you're looking for:

    Case       Flows      Notes
    ---------- ---------- ----------
    cs1 cf1 nt1
    cs1 cf1 nt2
    cs2 cf2 nt3
    cs2 cf3 nt3
    cs2 cf4 nt4

    (5 rows affected)

    What am I missing?

  • You query produced the correct results. You need to use your reporting tool to handle the display that you want. SQL will not produce what you are looking for as far as display

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • You query produced the correct results. You need to use your reporting tool to handle the display that you want. SQL will not produce what you are looking for as far as display

     

    You are right about the resulting dataset needing some post-processing in a UI tool in order to mimic the excact layout of the table the OP has delivered, but what I was referring to was the fact that the OP has a null value in his table showing the desired outcome.  That was what made me think I was missing some vital information.

    But until the OP chips in, I can't know if the query is incorrect or if some of the information the OP provided was less than stellar.

     

    • This reply was modified 1 month, 3 weeks ago by  kaj.

Viewing 5 posts - 1 through 5 (of 5 total)

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