-1

I have a pretty long query which includes subqueries and joins that I need to run for a report. It runs without error, but the results show 11 rows for each value, when I need it to display 1 row per value. I'm using 'select distinct', which is what I thought I needed to use to accomplish this goal, but it doesn't seem to be working. What am I doing wrong?

The value that I need to display only 1 row per each distinct occurrence is "cases.casenum".

Here's the query:

SELECT distinct
(
SELECT count(distinct cases.casenum)
FROM (cases INNER JOIN user_case_data ON cases.casenum=user_case_data.casenum)
WHERE (user_case_data.discharged_date >= '##START##'
AND user_case_data.discharged_date <= '##END##')
)
AS TOTAL_LOST_FILES, cases.casenum AS 'Case Number', user_case_data.discharged_date AS 'Discharged Date',

(case when case_notes.topic like 'LOS Case Status Update' THEN case_notes.note_date else null end)
AS GENDOC_31_Mailed,

(case when case_checklist.code='101' then case_checklist.due_date else null end)
AS ADVISED_ATTORNEY,

(case when case_notes.topic like 'LOS Updated Lein Ltr' THEN case_notes.note_date else null end)
AS "Sent Updated Lien Ltr",

(case when case_checklist.code='109' then case_checklist.due_date else null end)
AS "Time Allocation Completed",

(case when case_checklist.code='110' then case_checklist.due_date else null end)
AS "Attorney Signed Affidavit",

(case when case_checklist.code='111' then case_checklist.due_date else null end)
AS "Lien Letters Sent",

(case when case_checklist.code='112' then case_checklist.due_date else null end)
AS "Sent Lien to Counsel",

(case when case_checklist.code='113' then case_checklist.due_date else null end)
AS "Received Costs and Transferred"

FROM (cases LEFT JOIN case_checklist ON cases.casenum=case_checklist.case_id)
LEFT JOIN user_case_data ON case_checklist.case_id=user_case_data.casenum
LEFT JOIN case_notes ON user_case_data.casenum=case_notes.case_num
WHERE (user_case_data.discharged_date >= '##START##'
AND user_case_data.discharged_date <= '##END##')
ORDER BY user_case_data.discharged_date ASC;

And here's an example of the output... which makes it easy to see what we're going for- no white space, just one row for each specific value in the "case number" column. output

  • It's pretty obvious that a case will have more than 1 entries in case_checklist, and most likely will have more than 1 entries in case_notes, thus the multiple rows. It's the nature of 1-* relationship. If you only want distinct case, then don't include those tables. – Eric Jan 10 at 17:33
  • What this does is take user input (time range) and output to an excel spreadsheet a list of "cases" (law firm) that meet a given criteria... 81 different cases for our example. I only want each of the 81 case to show in the spreadsheet once, one row per case (cases.casenum column). The other tables are required to pull the dates for the other columns. So the desired result is a single row per case#. As it is now, each case shows 11 times, with a bunch of blank space in each of the columns. I understand WHY it's happening, but how do I get desired output? – boog Jan 10 at 17:39
  • the results show 11 rows for each value 11 identical rows? use DISTINCT. They differs in at least one field value? Use GROUP BY and select values for each field using proper aggregate function, or select one row by its ROW_NUMBER while sorting by some criteria. – Akina Jan 10 at 18:01
  • What's the relationship between case and user_case_data? 1-1 or 1-*? – Eric Jan 10 at 18:27
  • Relationship between cases.casenum (each case) and user_case_data is 1-*. user_case_data is a table which stores info pertaining to the case, among that data is discharge date, which is the date range the users are inputting. – boog Jan 10 at 18:44
0
SELECT 
(
SELECT count(distinct cases.casenum)
FROM (cases INNER JOIN user_case_data ON cases.casenum=user_case_data.casenum)
WHERE (user_case_data.discharged_date >= '##START##'
AND user_case_data.discharged_date <= '##END##')
)
AS "Total Lost Files", cases.casenum AS "Case Number", user_case_data.discharged_date AS "Discharged Date",

MAX(case when case_notes.topic like 'LOS Case Status Update' THEN case_notes.note_date else null end)
AS GENDOC_31_Mailed,

MAX(case when case_checklist.code='101' then case_checklist.due_date else null end)
AS ADVISED_ATTORNEY,

MAX(case when case_notes.topic like 'LOS Updated Lein Ltr' THEN case_notes.note_date else null end)
AS "Sent Updated Lien Ltr",

MAX(case when case_checklist.code='109' then case_checklist.due_date else null end)
AS "Time Allocation Completed",

MAX(case when case_checklist.code='110' then case_checklist.due_date else null end)
AS "Attorney Signed Affidavit",

MAX(case when case_checklist.code='111' then case_checklist.due_date else null end)
AS "Lien Letters Sent",

MAX(case when case_checklist.code='112' then case_checklist.due_date else null end)
AS "Sent Lien to Counsel",

MAX(case when case_checklist.code='113' then case_checklist.due_date else null end)
AS "Received Costs and Transferred"

FROM cases 
LEFT JOIN case_checklist ON cases.casenum = case_checklist.case_id
LEFT JOIN user_case_data ON case_checklist.case_id=user_case_data.casenum
LEFT JOIN case_notes ON user_case_data.casenum=case_notes.case_num AND case_notes.topic LIKE 'LOS Case Status Update'
WHERE (user_case_data.discharged_date >= '##START##'
AND user_case_data.discharged_date <= '##END##')
GROUP BY cases.casenum, user_case_data.discharged_date
ORDER BY user_case_data.discharged_date ASC;
|improve this answer|||||
0

You could probably PIVOT your data to turn the rows into columns

SELECT a.casenum AS [Case Number]
    , a.TotalLostFiles
    , a.discharge_date AS [Discharged Date]
    , [101] AS [Advised Attorney]
    , [LOS Case Status Update] AS [GENDOC_31_Mailed]
    , [LOS Updated Lein Ltr] AS [Sent Updated Lien Ltr]
    , [109] AS [Time Allocation Completed]
    , [110] AS [Attorney Signed Affidavit]
    , [111] AS [Lien Letters Sent]
    , [112] AS [Sent Lien to Counsel]
    , [113] AS [Received Costs and Transferred]
FROM (
    SELECT DISTINCT c.casenum
        , (
            SELECT COUNT(DISTINCT casenum)
            FROM user_case_data
            WHERE casenum = c.casenum 
                AND discharge_date >= '##START##'
                AND discharge_date <= '##END##'
        ) AS TotalLostFiles
        , cn.topic
        , cn.note_date
        , cc.code
        , cc.due_date
        , ucd.discharge_date
    FROM case c
    LEFT JOIN case_checklist cc ON cc.case_id = c.casenum
        AND cc.code IN ('101', '109', '110', '111', '112', '113')
    LEFT JOIN user_case_data ucd ON usd.casenum = c.casenum
        AND ucd.discharge_date BETWEEN '##START##' AND '##END##'
    LEFT JOIN case_notes cn ON cn.case_num = c.casenum 
        AND cn.topic IN ('LOS Case Status Update', 'LOS Updated Lein Ltr')
) a
PIVOT (
    MAX(due_date) FOR cc.code IN ([101], [109], [110], [111], [112], [113])
) b
PIVOT (
    MAX(note_date) FOR cn.topic IN ([LOS Case Status Update], [LOS Updated Lein Ltr])
) c
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.