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 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
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