Simplify script

1

How do I simplify the below script. Hopefully to use one select statement.and not use UNION at all.

Select
Case
When xfe.ID in (5,8) Then 'BCC Matters'
When xfe.ID in (10) Then 'LCC Matters'
When xfe.ID in (1) Then 'BA Matters'
Else 'Unallocated'
End As 'Matter Group'
, Sum(Case When xmfs.M_FileStatus <> 5 then 1 Else 0 End) As 'Live Instructions'
, Sum(Case When xmfs.M_FileStatus = 0 then 1 Else 0 End) As 'New Instructions'
, Sum(Case When xmfs.M_FileStatus = 1 then 1 Else 0 End) As 'Attempting PTP'
, Sum(Case When xmfs.M_FileStatus = 2 then 1 Else 0 End) As 'Payment Arrangement'
, Sum(Case When xmfs.M_FileStatus = 3 then 1 Else 0 End) As 'No Arrangement'
From ExcaliburV4_WH.dbo.Matter xm with (Nolock)
Inner Join ExcaliburV4_WH.dbo.MatterFileStatus xmfs on xmfs.IDX = xm.M_IDX And xmfs.M_FileStatus <> 5
Inner Join ExcaliburV4_WH.dbo.BookListView xb on xb.ID = xm.M_ClientID
Inner Join ExcaliburV4_WH.dbo.Client xcl on xcl.ID = xb.ID
Left Join ExcaliburV4_WH.dbo.LawFirmEntities xfe on xfe.ID = xcl.C_LawFirmEntityID
Where xfe.ID in (5,8) -- BCC Matters
--Where xfe.ID in (10) -- LCC Matters
--Where xfe.ID in (1) -- BA Matters
--Where xfe.ID is null -- No Allocation
Group By xfe.ID
UNION ALL
Select
Case
When xfe.ID in (5,8) Then 'BCC Matters'
When xfe.ID in (10) Then 'LCC Matters'
When xfe.ID in (1) Then 'BA Matters'
Else 'Unallocated'
End As 'Matter Group'
, Sum(Case When xmfs.M_FileStatus <> 5 then 1 Else 0 End) As 'Live Instructions'
, Sum(Case When xmfs.M_FileStatus = 0 then 1 Else 0 End) As 'New Instructions'
, Sum(Case When xmfs.M_FileStatus = 1 then 1 Else 0 End) As 'Attempting PTP'
, Sum(Case When xmfs.M_FileStatus = 2 then 1 Else 0 End) As 'Payment Arrangement'
, Sum(Case When xmfs.M_FileStatus = 3 then 1 Else 0 End) As 'No Arrangement'
From ExcaliburV4_WH.dbo.Matter xm with (Nolock)
Inner Join ExcaliburV4_WH.dbo.MatterFileStatus xmfs on xmfs.IDX = xm.M_IDX And xmfs.M_FileStatus <> 5
Inner Join ExcaliburV4_WH.dbo.BookListView xb on xb.ID = xm.M_ClientID
Inner Join ExcaliburV4_WH.dbo.Client xcl on xcl.ID = xb.ID
Left Join ExcaliburV4_WH.dbo.LawFirmEntities xfe on xfe.ID = xcl.C_LawFirmEntityID
--Where xfe.ID in (5,8) -- BCC Matters
Where xfe.ID in (10) -- LCC Matters
--Where xfe.ID in (1) -- BA Matters
--Where xfe.ID is null -- No Allocation
Group By xfe.ID
UNION ALL
Select
Case
When xfe.ID in (5,8) Then 'BCC Matters'
When xfe.ID in (10) Then 'LCC Matters'
When xfe.ID in (1) Then 'BA Matters'
Else 'Unallocated'
End As 'Matter Group'
, Sum(Case When xmfs.M_FileStatus <> 5 then 1 Else 0 End) As 'Live Instructions'
, Sum(Case When xmfs.M_FileStatus = 0 then 1 Else 0 End) As 'New Instructions'
, Sum(Case When xmfs.M_FileStatus = 1 then 1 Else 0 End) As 'Attempting PTP'
, Sum(Case When xmfs.M_FileStatus = 2 then 1 Else 0 End) As 'Payment Arrangement'
, Sum(Case When xmfs.M_FileStatus = 3 then 1 Else 0 End) As 'No Arrangement'
From ExcaliburV4_WH.dbo.Matter xm with (Nolock)
Inner Join ExcaliburV4_WH.dbo.MatterFileStatus xmfs on xmfs.IDX = xm.M_IDX And xmfs.M_FileStatus <> 5
Inner Join ExcaliburV4_WH.dbo.BookListView xb on xb.ID = xm.M_ClientID
Inner Join ExcaliburV4_WH.dbo.Client xcl on xcl.ID = xb.ID
Left Join ExcaliburV4_WH.dbo.LawFirmEntities xfe on xfe.ID = xcl.C_LawFirmEntityID
--Where xfe.ID in (5,8) -- BCC Matters
--Where xfe.ID in (10) -- LCC Matters
Where xfe.ID in (1) -- BA Matters
--Where xfe.ID is null -- No Allocation
Group By xfe.ID
UNION ALL
Select
Case
When xfe.ID in (5,8) Then 'BCC Matters'
When xfe.ID in (10) Then 'LCC Matters'
When xfe.ID in (1) Then 'BA Matters'
Else 'Unallocated'
End As 'Matter Group'
, Sum(Case When xmfs.M_FileStatus <> 5 then 1 Else 0 End) As 'Live Instructions'
, Sum(Case When xmfs.M_FileStatus = 0 then 1 Else 0 End) As 'New Instructions'
, Sum(Case When xmfs.M_FileStatus = 1 then 1 Else 0 End) As 'Attempting PTP'
, Sum(Case When xmfs.M_FileStatus = 2 then 1 Else 0 End) As 'Payment Arrangement'
, Sum(Case When xmfs.M_FileStatus = 3 then 1 Else 0 End) As 'No Arrangement'
From ExcaliburV4_WH.dbo.Matter xm with (Nolock)
Inner Join ExcaliburV4_WH.dbo.MatterFileStatus xmfs on xmfs.IDX = xm.M_IDX And xmfs.M_FileStatus <> 5
Inner Join ExcaliburV4_WH.dbo.BookListView xb on xb.ID = xm.M_ClientID
Inner Join ExcaliburV4_WH.dbo.Client xcl on xcl.ID = xb.ID
Left Join ExcaliburV4_WH.dbo.LawFirmEntities xfe on xfe.ID = xcl.C_LawFirmEntityID
--Where xfe.ID in (5,8) -- BCC Matters
--Where xfe.ID in (10) -- LCC Matters
--Where xfe.ID in (1) -- BA Matters
Where xfe.ID is null -- No Allocation
Group By xfe.ID

2

You need to start with the most basic good practices. See this link

and look for the section titled "My Pet Peeve...", which shows some very good SQL code in layout and commentary. If you have proper indentation and commentary that tells what's being achieved, you'll be in a better position to start simplification. Until you have that there's little point in trying to simplify. And if you can't add meaningful commentary, then you don't even know what's being done and it would be folly to change it.

3

Step 1 is to compare the individual SELECTs to each other and find out what's different and what's the same. That's what we'd have to do but I'll leave that to you.

Then I'd take what was common in all of them and write it out and see if I could fold what's different together with that.

The problem here is that the reason for doing a UNION ALL between multiple nearly identical queries is to keep them SARGable so that it won't take a month of Sundays to run like it probably will when combined,

Remember that "Set Based" does NOT mean "All in one Select".

4 
SELECT --xfe.ID,  -- uncomment this to show ID
CASE
WHEN xfe.ID in (5,8) Then 'BCC Matters'
WHEN xfe.ID in (10) Then 'LCC Matters'
WHEN xfe.ID in (1) Then 'BA Matters'
ELSE 'Unallocated'
END As 'Matter Group'
, SUM(CASE WHEN xmfs.M_FileStatus <> 5 THEN 1 ELSE 0 END) AS 'Live Instructions'
, SUM(CASE WHEN xmfs.M_FileStatus = 0 THEN 1 ELSE 0 END) AS 'New Instructions'
, SUM(CASE WHEN xmfs.M_FileStatus = 1 THEN 1 ELSE 0 END) AS 'Attempting PTP'
, SUM(CASE WHEN xmfs.M_FileStatus = 2 THEN 1 ELSE 0 END) AS 'Payment Arrangement'
, SUM(CASE WHEN xmfs.M_FileStatus = 3 THEN 1 ELSE 0 END) AS 'No Arrangement'
FROM ExcaliburV4_WH.dbo.Matter xm with (Nolock)
INNER JOIN ExcaliburV4_WH.dbo.MatterFileStatus xmfs on xmfs.IDX = xm.M_IDX And xmfs.M_FileStatus <> 5
INNER JOIN ExcaliburV4_WH.dbo.BookListView xb on xb.ID = xm.M_ClientID
INNER JOIN ExcaliburV4_WH.dbo.Client xcl on xcl.ID = xb.ID
LEFT JOIN ExcaliburV4_WH.dbo.LawFirmEntities xfe on xfe.ID = xcl.C_LawFirmEntityID
WHERE xfe.ID in (5,8,10,1) OR xfe.ID IS NULL  -- this has been combined
GROUP BY xfe.ID

As @JeffModen mentioned, you needed to determine what was similar between each query segment of the UNION ALL. It turns out that all of the aspects were the same, except for the WHERE clauses that had different lines commented out. Since you are GROUPing by the same column that appears in the WHERE clause, you can safely combine all the WHERE conditions.

Regarding how to find similar lines, the method I used was to paste your query in a text editor (Notepad++) and sort the lines alphabetically. Duplicate lines will then appear adjacent to each other. I used a plugin that can also remove duplicate lines, which trimmed it down even further. I use this technique quite often to find duplicate code.

I commented out the ID column in the SELECT list, but you'll probably want to include that in the output.

5

Thanks for the help, much appreciated.