Legacy Data and Current System Data Merged together in a single SQL Database

There was a lot going on in this Database that I couldn't change, a lot of bad practices because the data was originally being pulled from an old mainframe database and then transformed by a horrible looking VB service and inserted into several (horrible) tables in an SQL Database so the information could be used by a VB.NET website (1.1 or something like that?)

I would like to know how clean this code is and whether or not there is anything that can be done to further my knowledge in retrieving information from Legacy Data stores.

This was an Ad-Hoc, and from the looks of the first Where clause it would be a very interesting Stored Procedure to say the least.

```
WITH Table1 AS
(
SELECT
CaseParticipantTable.DOCK_NO AS CaseNbr, ActivityTable.JUDGMENT_DATE AS JudgmentDate, ActivityTable.AMOUNT_TYPE AS AmountType,
ActivityTable.AMOUNT AS JudgmentAmount, CaseParticipantTable.FILING_NAME AS CaseParticipant
FROM
UJSJudgementQuery.dbo.CJ_SC_CASE_PARTICIPANT AS CaseParticipantTable
INNER JOIN UJSJudgementQuery.dbo.CJ_SC_JUDGMENT_ACTIVITY AS ActivityTable ON CaseParticipantTable.DOCK_NO = ActivityTable.DOCK_NO
WHERE CaseParticipantTable.FILING_NAME IN ('/CountyName COUNT AUDITORS OFFICE', '/CountyName COUNTY AUDITOR',
'/CountyName COUNTY AUDITOR''S OFFI', '/CountyName COUNTY AUDITORS OFFI', '/CountyName COUNTY AUDITORS OFFIC',
'/CountyName COUNTY AUDITORS OFFICE', '/CountyName COUNTY TREASURER', '/CountyName County Treasurer',
'/CountyName COUNTY/ AUDITOR', '/CountyName COUNTY/AUDITORS OFFIC', '/CountyName COUNTY C/O AUDITORS')
AND AMOUNT > 0
), ActiveJudgments AS (
SELECT Table1.*, Judgments.JUDGMENT_STATUS
FROM
Table1
INNER JOIN UJSJudgementQuery.dbo.CJ_SC_JUDGMENTS AS Judgments ON Table1.CaseNbr = Judgments.DOCK_NO
WHERE Judgments.JUDGMENT_STATUS = 'A'
), NoInactives AS (
SELECT ActiveJudgments.*
FROM ActiveJ

Obviously table1 is not a very good name, so I'll leave that as that. The other thing that immediately jumps at me is the poor formatting.

It's hard to read multiple items strung across a single line. Doubly when they're aliased.

SELECT 
    CaseParticipantTable.DOCK_NO AS CaseNbr, ActivityTable.JUDGMENT_DATE AS JudgmentDate, ActivityTable.AMOUNT_TYPE AS AmountType, 
        ActivityTable.AMOUNT AS JudgmentAmount, CaseParticipantTable.FILING_NAME AS CaseParticipant

Is easier to read like this:

SELECT 
    CaseParticipantTable.DOCK_NO AS CaseNbr, 
    ActivityTable.JUDGMENT_DATE AS JudgmentDate, 
    ActivityTable.AMOUNT_TYPE AS AmountType, 
    ActivityTable.AMOUNT AS JudgmentAmount, 
    CaseParticipantTable.FILING_NAME AS CaseParticipant

Similarly, I don't like having the entire JOIN...ON statement on one line. I usually put the ON statement on the next line, indented one tab.

FROM
    UJSJudgementQuery.dbo.CJ_SC_CASE_PARTICIPANT AS CaseParticipantTable
    INNER JOIN UJSJudgementQuery.dbo.CJ_SC_JUDGMENT_ACTIVITY AS ActivityTable 
        ON CaseParticipantTable.DOCK_NO = ActivityTable.DOCK_NO

I do like how you start an indented new line after keywords, but you don't do it consistently. This WHERE suffers from that. I also recommend a new line for the items in your in List.

WHERE 
    CaseParticipantTable.FILING_NAME IN (
                                    '/CountyName COUNT AUDITORS OFFICE', 
                                    '/CountyName COUNTY AUDITOR', 
                                    '/CountyName COUNTY AUDITOR''S OFFI', 
                                    '/CountyName COUNTY AUDITORS OFFI', 
                                    '/CountyName COUNTY AUDITORS OFFIC', 
                                    '/CountyName COUNTY AUDITORS OFFICE', 
                                    '/CountyName COUNTY TREASURER', 
                                    '/CountyName County Treasurer', 
                                    '/CountyName COUNTY/ AUDITOR', 
                                    '/CountyName COUNTY/AUDITORS OFFIC', 
                                    '/CountyName COUNTY C/O AUDITORS') 
    AND AMOUNT > 0

You may have your reasons for it, but there's a lot of duplication in your IN statement. Consider whether or not a LIKE...OR...IN type statement would meet your needs. It will perform worse, so test it against your original to see how bad the performance hit will be.

WHERE 
    CaseParticipantTable.FILING_NAME LIKE '/CountyName%COUNT%AUDITOR%'
    OR CaseParticipantTable.FILING_NAME IN(
                                    '/CountyName COUNTY TREASURER', 
                                    '/CountyName County Treasurer')

This is an adhoc query now, but many many adhoc queries make their way into production use. Never should you Select * in a production query. It often forces a table scan where a more efficient query might be found otherwise.

StackExchange Code Review Q#59814, answer score: 6