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 &gt; 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 ActiveJudgments
    WHERE ActiveJudgments.CaseNbr NOT IN (SELECT Table1.CaseNbr 
                                            FROM 
                                                Table1 
                                                INNER JOIN UJSJudgementQuery.dbo.CJ_SC_JUDGMENTS AS Judgments ON Table1.CaseNbr = Judgments.DOCK_NO
                                            WHERE Judgments.JUDGMENT_STATUS = 'I')
)
SELECT DISTINCT 
    NoInactives.CaseNbr
    , CaseParticipantTable.FILING_NAME AS Debtor
    , NoInactives.JudgmentDate
    , NoInactives.JudgmentAmount
    , NoInactives.JUDGMENT_STATUS
FROM 
    NoInactives
    INNER JOIN UJSJudgementQuery.dbo.CJ_SC_CASE_PARTICIPANT AS CaseParticipantTable ON NoInactives.CaseNbr = CaseParticipantTable.DOCK_NO
WHERE (CaseParticipantTable.PARTICIPANT_CODE = 'DBT' OR CaseParticipantTable.PARTICIPANT_CODE = 'DEF')
ORDER BY Debtor ASC


The ORDER BY at the end is just for checking my data, it is either by date or by Debtor Name



I am looking for suggestions on future query writing
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 &gt; 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.