CODEWITHSUNDEEP
sqlt-sql
Andrew Richards
Andrew Richards

Reputation: 325

CTE returning incorrect results

[Apologies for the initial post without the DDL / DML statements to create the tables - now fixed]

I am creating a database which essentially logs the results of inspections of local facilities. An "inspection" may sometimes result in a "case" being raised, if it identifies failings. Cases can also be raised without an inspection, for example if a member of the public makes a complaint. One inspection can result in more than one case being raised - for example if the facility is a local park, it may be that an inspection results in a case being raised for (a) poor grass cutting, and (b) bins not being emptied.

A case cannot lead to an inspection, but it can lead to a further case, so in my Case table, I have DerivedFromInspectionID and DerivedFromCaseID columns.

There are two tables: Inspection and GMCase (for "Grounds Maintenance case" records)

The salient parts of the tables can be created as follows:


CREATE TABLE [Inspection](
    [InspectionID] [int] NOT NULL PRIMARY KEY,
    [InspectionDate] [datetime] NULL,
    [InspectionType] [varchar](10) NULL
    )

CREATE TABLE [GMCase](
    [CaseID] [int] NOT NULL PRIMARY KEY,
    [CaseDate] [datetime] NULL,
    [CaseType] [varchar](20) NULL,
    [DerivedFromCaseID] [int] NULL CONSTRAINT fk_Case_Case FOREIGN KEY REFERENCES GMCase (CaseID),
    [DerivedFromInspectionID] [int] NULL CONSTRAINT fk_Case_Inspection FOREIGN KEY REFERENCES Inspection(InspectionID)
    )


INSERT INTO Inspection(InspectionID, InspectionDate,InspectionType)
VALUES 
(2,GETDATE(),'Scheduled'),
(3,GETDATE(),'Scheduled'),
(5,GETDATE(),'Ad-hoc')


INSERT INTO GMCase (CaseID,CaseDate,CaseType,DerivedFromCaseID,DerivedFromInspectionID)
VALUES
(1 , GETDATE(),'Compliment',NULL,NULL),
(2, GETDATE(),'Complaint',NULL,2),
(3,GETDATE(),'Instruction',2,NULL),
(5,GETDATE(),'Rectification',3,NULL),
(14,GETDATE(),'Complaint',NULL,2)

I want to create a view which will show the chain of 

[Inspection1] (Optionally)
    >>> Case1 
        >>> Case2 
            >>> Case3.

This is to be used within the database application, when looking at either Case or Inspection details.

In the application, I need to be able to look at any of the cases in the chain, and see how it fits within that chain. So, if I look at Case2, it should show all the items above.

In order to get the source data, I initially created a view containing a UNION query to bring together the equivalent columns from the Inspection and GMCase tables. As I needed to be able to identify each case/inspection, I brought in the CaseID and the InspectionID - initially into two separate columns, but I thought it would be useful to have a single ID column. I wanted to ensure that there wasn't apparent duplication in this ID (containing 2 for Inspection 2 but also for Case2), so I hit upon the idea of making the ID negative if it's the InspectionID (perhaps this is a mistake?)

I added a Level column to help identify whether a case is at a root level, or is derived from an inspection, and to help as a starting point for the level calculation within the CTE.

I also added a column called RootCase, which would, for Inspections, allow me to see which GMCase record the Inspection linked to, if any. This was to allow that if I look at Case 3, for example, I can see that it is ultimately derived from Inspection 2, because its root case would be the same RootCase of as the RootCase of Inspection 2. I'm also now wondering if this was a mistake...

The SQL for the view is as follows:


CREATE VIEW vwCaseHierarchySource 
AS
    SELECT CaseID, 
    CaseDate, 
    CaseType,
    ISNULL(-g.DerivedFromInspectionID,g.DerivedFromCaseID) AS ParentCaseID,
    CASE WHEN g.DerivedFromCaseID IS NULL AND g.DerivedFromInspectionID IS NULL THEN 0 ELSE 1 END AS [Level],
    CaseID AS RootCase
    FROM GMCase g
    UNION
    SELECT -i.InspectionID
          ,i.InspectionDate
          ,CONVERT(VARCHAR(20),i.InspectionType + ' inspection') AS CaseType
          ,NULL AS ParentCaseID
          ,CASE WHEN g.CaseID IS NULL THEN 0 ELSE 1 END AS [Level]
          ,g.CaseID AS RootCase
    FROM Inspection i
    LEFT JOIN GMCase g
    ON i.InspectionID = g.DerivedFromInspectionID

I've then attempted numerous ways of creating a CTE to show this hierarchy. CTEs are not my forte, and I can't remember all the things I've tried, but below is my current effort.

The problem I have is, I suspect, due to the fact that an inspection can lead (and in my sample data does lead) to more than one case, because whenever I use the CTE, I get the correct information, but also incorrect information. If I try to get the results for Case 2, I get the hierarchy for case 2, but also the row for case 14. And if I try for case 14, I get that, but also all the rows for case 2.

Here's the CTE:

CREATE VIEW [dbo].[vwCaseInspectionHierarchy] 
AS

WITH CaseList AS 
(
SELECT
    CaseID,
    CaseDate,
    CaseType,
    ParentCaseID,
    RootCase,
    RootCase.[Level]
FROM vwCaseHierarchySource RootCase
WHERE RootCase.ParentCaseID IS NULL

UNION ALL

SELECT
    ChildCase.CaseID,
    ChildCase.CaseDate,
    ChildCase.CaseType,
    ChildCase.ParentCaseID,
    CaseList.RootCase,
    CaseList.[Level]+1
FROM vwCaseHierarchySource ChildCase
INNER JOIN CaseList ON
    ChildCase.ParentCaseID=CaseList.CaseID
)

SELECT * 
      ,REPLICATE(' ',[Level]*5) +   CaseType AS IndentedCaseType 
FROM CaseList

And my code to test it:

SELECT *
FROM [vwCaseInspectionHierarchy] 
WHERE RootCase=2

Can anyone help me? Apologies for a long post, but hopefully I've included everything relevant...

Andrew

Upvotes: 2

Views: 222

Answers (1)

LukStorms
LukStorms

Reputation: 29657

I don't think you actually need that view.

Just a recursive CTE on the tables themselves should do the trick.

And you can seed the recursive with the type string from both tables.

Example snippet: 

declare @Inspection table (InspectionID int primary key, InspectionDate datetime, InspectionType varchar(30));
declare @GMCase table (CaseID int primary key, CaseDate date, CaseType varchar(30), DerivedFromCaseID int, DerivedFromInspectionID int);

insert into @Inspection (InspectionID, InspectionDate, InspectionType) values
(102, '2019-01-22 00:00:00.000', 'Scheduled'),
(103, '2019-02-06 14:25:55.133', 'Scheduled'),
(105, '2019-02-06 16:59:04.820', 'Ad-hoc');

insert into @GMCase (CaseID, CaseDate, CaseType, DerivedFromCaseID, DerivedFromInspectionID) values
(1,  '2019-01-23', 'Compliment', NULL, NULL),
(2,  '2018-12-04', 'Complaint', NULL, 102),
(3,  '2018-12-04', 'Instruction', 2, NULL),
(5,  '2018-12-21', 'Rectification', 3, NULL),
(14, '2019-02-06', 'Complaint', NULL, 2),
(15, '2019-02-07', 'Compliment', NULL, NULL),
(16, '2019-02-08', 'Complaint', 15, NULL);

 with RCTE as
(
   -- Seeding the recursive CTE
   select i.InspectionID, n as Lvl, t.CaseID as RootCaseID, case when n = 1 then t.CaseID end as CaseID, t.DerivedFromCaseID, iif(n=0, i.InspectionType, t.CaseType) as [Type]
   from @GMCase t
   left join @Inspection i ON i.InspectionID = t.DerivedFromInspectionID
   cross join (values (0),(1)) nums(n)
   where t.DerivedFromCaseID is null

   union all

   select r.InspectionID, r.Lvl + 1, r.RootCaseID, t.CaseID, t.DerivedFromCaseID, t.CaseType 
   from RCTE r
   join @GMCase t on t.DerivedFromCaseID = r.CaseID
)
select c.RootCaseID, c.CaseID, c.InspectionID, t.CaseDate,
concat(replicate(' ',c.Lvl*5),COALESCE([Type],'Inspection')) AS IndentedType 
from RCTE c
left join @GMCase t on t.CaseID = c.CaseID
order by c.RootCaseID, c.Lvl;

Result:

RootCaseID  CaseID  InspectionID    CaseDate    IndentedType
1           NULL        NULL        NULL        Inspection
1           1           NULL        2019-01-23       Compliment
2           NULL        102         NULL        Scheduled
2           2           102         2018-12-04       Complaint
2           3           102         2018-12-04            Instruction
2           5           102         2018-12-21                 Rectification
14          NULL        NULL        NULL        Inspection
14          14          NULL        2019-02-06       Complaint
15          NULL        NULL        NULL        Inspection
15          15          NULL        2019-02-07       Compliment
15          16          NULL        2019-02-08            Complaint

Upvotes: 1

Related Questions