same dates

geebee · Apr 23, 2009

hi,

i have the following:

SELECT dbo_vw_cp_rpt_loan_level_rca.ID,
dbo_vw_cp_rpt_loan_level_rca.loan_id, dbo_vw_cp_rpt_loan_level_rca.deal_id,
dbo_vw_cp_rpt_loan_level_rca.vblock, dbo_vw_cp_rpt_loan_level_rca.issue_id,
dbo_vw_cp_rpt_loan_level_rca!issue_code AS issue_code,
dbo_vw_cp_rpt_loan_level_rca.borrw_name_corrected,
dbo_vw_cp_rpt_loan_level_rca.exception_requestor_name,
dbo_vw_cp_rpt_loan_level_rca.bal_orig, dbo_vw_cp_rpt_loan_level_rca.dt_purch,
dbo_vw_cp_rpt_loan_level_rca.business_line,
dbo_vw_cp_rpt_loan_level_rca.code_lien_stat,
dbo_vw_cp_rpt_loan_level_rca.code_origination_desc,
dbo_vw_cp_rpt_loan_level_rca.loan_stat_srvcr_curr,
dbo_vw_cp_rpt_loan_level_rca.correspondent_id,
dbo_vw_cp_rpt_loan_level_rca.seller, dbo_counterparty.code_approval_status,
dbo_vw_cp_rpt_loan_level_rca.res_score,
dbo_vw_cp_rpt_loan_level_rca.prin_bal_curr_rpt,
dbo_vw_cp_rpt_loan_level_rca.category_code,
dbo_vw_cp_rpt_loan_level_rca.referral_dt,
dbo_vw_cp_rpt_loan_level_rca.completion_code,
dbo_vw_cp_rpt_loan_level_rca.completion_dt,
dbo_vw_cp_rpt_loan_level_rca.amt_rcvd, dbo_cp_loan_issues.issue_code,
dbo_cp_loan_issues.notice_dt, dbo_cp_loan_issues.completion_code,
dbo_cp_loan_issues.completion_dt, dbo_cp_loan_issues.amt_rcvd,
IIf(dbo_cp_loan_issues!completion_dt Is Null And dbo_cp_loan_issues!notice_dt
Is Not Null,"Yes","No") AS open_rcf, IIf(dbo_cp_loan_issues!completion_dt Is
Null,dbo_cp_loan_issues!notice_dt) AS rcf_notice_dt,
IIf(dbo_cp_loan_issues!completion_dt Is Null,"No","Yes") AS completed,
IIf(dbo_cp_loan_issues!completion_dt Is Not
Null,dbo_cp_loan_issues!notice_dt) AS completed_notice_dt,
dbo_cp_loan_issues!completion_code AS rcf_completion_code,
dbo_vw_cp_rpt_loan_level_rca.completion_dt AS rcf_completion_dt,
dbo_vw_cp_rpt_loan_level_rca.amt_rcvd AS rcf_amt_recd
FROM (dbo_vw_cp_rpt_loan_level_rca LEFT JOIN dbo_cp_loan_issues ON
dbo_vw_cp_rpt_loan_level_rca.ID = dbo_cp_loan_issues.ID) LEFT JOIN
dbo_counterparty ON dbo_cp_loan_issues.counterparty_id =
dbo_counterparty.counterparty_id
WHERE (((dbo_vw_cp_rpt_loan_level_rca.ID)="20") AND
((dbo_vw_cp_rpt_loan_level_rca.category_code)="CA") AND
((dbo_vw_cp_rpt_loan_level_rca.referral_dt) In (SELECT DISTINCT [referral_dt]
FROM dbo_vw_cp_rpt_loan_level_rca as TEST WHERE TEST.ID =
dbo_vw_cp_rpt_loan_level_rca.ID)));

the query result is showing the same [referral_dt] for all of the retrieved
records. why is this? there are 3 distinct referral_dt, and each of the
retrieved records has a referral_dt. but for some reason the same
referral_dt is being assigned to all of the records.

thanks in advance,
geebee

KARL DEWEY · Apr 23, 2009

Try dropping the last part of the WHERE statement in that you want DISTINCT
[referral_dt].

geebee · Apr 23, 2009

hi,

that seems to have worked, but i am noticing that there may be cases in
which there are records retrieved in which there are the exact 2 same dates.
how do i get rid of one of them...keep in mind that all the other fields are
somewhat the same...some different.

thanks in advance,
geebee

KARL DEWEY said:
Try dropping the last part of the WHERE statement in that you want DISTINCT
[referral_dt].

geebee said:

hi,

i have the following:

SELECT dbo_vw_cp_rpt_loan_level_rca.ID,
dbo_vw_cp_rpt_loan_level_rca.loan_id, dbo_vw_cp_rpt_loan_level_rca.deal_id,
dbo_vw_cp_rpt_loan_level_rca.vblock, dbo_vw_cp_rpt_loan_level_rca.issue_id,
dbo_vw_cp_rpt_loan_level_rca!issue_code AS issue_code,
dbo_vw_cp_rpt_loan_level_rca.borrw_name_corrected,
dbo_vw_cp_rpt_loan_level_rca.exception_requestor_name,
dbo_vw_cp_rpt_loan_level_rca.bal_orig, dbo_vw_cp_rpt_loan_level_rca.dt_purch,
dbo_vw_cp_rpt_loan_level_rca.business_line,
dbo_vw_cp_rpt_loan_level_rca.code_lien_stat,
dbo_vw_cp_rpt_loan_level_rca.code_origination_desc,
dbo_vw_cp_rpt_loan_level_rca.loan_stat_srvcr_curr,
dbo_vw_cp_rpt_loan_level_rca.correspondent_id,
dbo_vw_cp_rpt_loan_level_rca.seller, dbo_counterparty.code_approval_status,
dbo_vw_cp_rpt_loan_level_rca.res_score,
dbo_vw_cp_rpt_loan_level_rca.prin_bal_curr_rpt,
dbo_vw_cp_rpt_loan_level_rca.category_code,
dbo_vw_cp_rpt_loan_level_rca.referral_dt,
dbo_vw_cp_rpt_loan_level_rca.completion_code,
dbo_vw_cp_rpt_loan_level_rca.completion_dt,
dbo_vw_cp_rpt_loan_level_rca.amt_rcvd, dbo_cp_loan_issues.issue_code,
dbo_cp_loan_issues.notice_dt, dbo_cp_loan_issues.completion_code,
dbo_cp_loan_issues.completion_dt, dbo_cp_loan_issues.amt_rcvd,
IIf(dbo_cp_loan_issues!completion_dt Is Null And dbo_cp_loan_issues!notice_dt
Is Not Null,"Yes","No") AS open_rcf, IIf(dbo_cp_loan_issues!completion_dt Is
Null,dbo_cp_loan_issues!notice_dt) AS rcf_notice_dt,
IIf(dbo_cp_loan_issues!completion_dt Is Null,"No","Yes") AS completed,
IIf(dbo_cp_loan_issues!completion_dt Is Not
Null,dbo_cp_loan_issues!notice_dt) AS completed_notice_dt,
dbo_cp_loan_issues!completion_code AS rcf_completion_code,
dbo_vw_cp_rpt_loan_level_rca.completion_dt AS rcf_completion_dt,
dbo_vw_cp_rpt_loan_level_rca.amt_rcvd AS rcf_amt_recd
FROM (dbo_vw_cp_rpt_loan_level_rca LEFT JOIN dbo_cp_loan_issues ON
dbo_vw_cp_rpt_loan_level_rca.ID = dbo_cp_loan_issues.ID) LEFT JOIN
dbo_counterparty ON dbo_cp_loan_issues.counterparty_id =
dbo_counterparty.counterparty_id
WHERE (((dbo_vw_cp_rpt_loan_level_rca.ID)="20") AND
((dbo_vw_cp_rpt_loan_level_rca.category_code)="CA") AND
((dbo_vw_cp_rpt_loan_level_rca.referral_dt) In (SELECT DISTINCT [referral_dt]
FROM dbo_vw_cp_rpt_loan_level_rca as TEST WHERE TEST.ID =
dbo_vw_cp_rpt_loan_level_rca.ID)));

the query result is showing the same [referral_dt] for all of the retrieved
records. why is this? there are 3 distinct referral_dt, and each of the
retrieved records has a referral_dt. but for some reason the same
referral_dt is being assigned to all of the records.

thanks in advance,
geebee

Click to expand...

KARL DEWEY · Apr 23, 2009

How will you decide which is not wanted?

geebee said:
hi,

that seems to have worked, but i am noticing that there may be cases in
which there are records retrieved in which there are the exact 2 same dates.
how do i get rid of one of them...keep in mind that all the other fields are
somewhat the same...some different.

thanks in advance,
geebee

KARL DEWEY said:

Try dropping the last part of the WHERE statement in that you want DISTINCT
[referral_dt].

geebee said:

hi,

i have the following:

SELECT dbo_vw_cp_rpt_loan_level_rca.ID,
dbo_vw_cp_rpt_loan_level_rca.loan_id, dbo_vw_cp_rpt_loan_level_rca.deal_id,
dbo_vw_cp_rpt_loan_level_rca.vblock, dbo_vw_cp_rpt_loan_level_rca.issue_id,
dbo_vw_cp_rpt_loan_level_rca!issue_code AS issue_code,
dbo_vw_cp_rpt_loan_level_rca.borrw_name_corrected,
dbo_vw_cp_rpt_loan_level_rca.exception_requestor_name,
dbo_vw_cp_rpt_loan_level_rca.bal_orig, dbo_vw_cp_rpt_loan_level_rca.dt_purch,
dbo_vw_cp_rpt_loan_level_rca.business_line,
dbo_vw_cp_rpt_loan_level_rca.code_lien_stat,
dbo_vw_cp_rpt_loan_level_rca.code_origination_desc,
dbo_vw_cp_rpt_loan_level_rca.loan_stat_srvcr_curr,
dbo_vw_cp_rpt_loan_level_rca.correspondent_id,
dbo_vw_cp_rpt_loan_level_rca.seller, dbo_counterparty.code_approval_status,
dbo_vw_cp_rpt_loan_level_rca.res_score,
dbo_vw_cp_rpt_loan_level_rca.prin_bal_curr_rpt,
dbo_vw_cp_rpt_loan_level_rca.category_code,
dbo_vw_cp_rpt_loan_level_rca.referral_dt,
dbo_vw_cp_rpt_loan_level_rca.completion_code,
dbo_vw_cp_rpt_loan_level_rca.completion_dt,
dbo_vw_cp_rpt_loan_level_rca.amt_rcvd, dbo_cp_loan_issues.issue_code,
dbo_cp_loan_issues.notice_dt, dbo_cp_loan_issues.completion_code,
dbo_cp_loan_issues.completion_dt, dbo_cp_loan_issues.amt_rcvd,
IIf(dbo_cp_loan_issues!completion_dt Is Null And dbo_cp_loan_issues!notice_dt
Is Not Null,"Yes","No") AS open_rcf, IIf(dbo_cp_loan_issues!completion_dt Is
Null,dbo_cp_loan_issues!notice_dt) AS rcf_notice_dt,
IIf(dbo_cp_loan_issues!completion_dt Is Null,"No","Yes") AS completed,
IIf(dbo_cp_loan_issues!completion_dt Is Not
Null,dbo_cp_loan_issues!notice_dt) AS completed_notice_dt,
dbo_cp_loan_issues!completion_code AS rcf_completion_code,
dbo_vw_cp_rpt_loan_level_rca.completion_dt AS rcf_completion_dt,
dbo_vw_cp_rpt_loan_level_rca.amt_rcvd AS rcf_amt_recd
FROM (dbo_vw_cp_rpt_loan_level_rca LEFT JOIN dbo_cp_loan_issues ON
dbo_vw_cp_rpt_loan_level_rca.ID = dbo_cp_loan_issues.ID) LEFT JOIN
dbo_counterparty ON dbo_cp_loan_issues.counterparty_id =
dbo_counterparty.counterparty_id
WHERE (((dbo_vw_cp_rpt_loan_level_rca.ID)="20") AND
((dbo_vw_cp_rpt_loan_level_rca.category_code)="CA") AND
((dbo_vw_cp_rpt_loan_level_rca.referral_dt) In (SELECT DISTINCT [referral_dt]
FROM dbo_vw_cp_rpt_loan_level_rca as TEST WHERE TEST.ID =
dbo_vw_cp_rpt_loan_level_rca.ID)));

the query result is showing the same [referral_dt] for all of the retrieved
records. why is this? there are 3 distinct referral_dt, and each of the
retrieved records has a referral_dt. but for some reason the same
referral_dt is being assigned to all of the records.

thanks in advance,
geebee

Click to expand...

Click to expand...

geebee · Apr 23, 2009

i suppose i could look at another column or something if there are more than
1 same date for an ID in the query results. like maybe i could look at the
count of date and ID to see if that count is >1. just not sure how to put
that in the query i now have.

thanks in advance,
geebee

KARL DEWEY said:
How will you decide which is not wanted?

geebee said:

hi,

that seems to have worked, but i am noticing that there may be cases in
which there are records retrieved in which there are the exact 2 same dates.
how do i get rid of one of them...keep in mind that all the other fields are
somewhat the same...some different.

thanks in advance,
geebee

KARL DEWEY said:

Try dropping the last part of the WHERE statement in that you want DISTINCT
[referral_dt].

:

hi,

i have the following:

SELECT dbo_vw_cp_rpt_loan_level_rca.ID,
dbo_vw_cp_rpt_loan_level_rca.loan_id, dbo_vw_cp_rpt_loan_level_rca.deal_id,
dbo_vw_cp_rpt_loan_level_rca.vblock, dbo_vw_cp_rpt_loan_level_rca.issue_id,
dbo_vw_cp_rpt_loan_level_rca!issue_code AS issue_code,
dbo_vw_cp_rpt_loan_level_rca.borrw_name_corrected,
dbo_vw_cp_rpt_loan_level_rca.exception_requestor_name,
dbo_vw_cp_rpt_loan_level_rca.bal_orig, dbo_vw_cp_rpt_loan_level_rca.dt_purch,
dbo_vw_cp_rpt_loan_level_rca.business_line,
dbo_vw_cp_rpt_loan_level_rca.code_lien_stat,
dbo_vw_cp_rpt_loan_level_rca.code_origination_desc,
dbo_vw_cp_rpt_loan_level_rca.loan_stat_srvcr_curr,
dbo_vw_cp_rpt_loan_level_rca.correspondent_id,
dbo_vw_cp_rpt_loan_level_rca.seller, dbo_counterparty.code_approval_status,
dbo_vw_cp_rpt_loan_level_rca.res_score,
dbo_vw_cp_rpt_loan_level_rca.prin_bal_curr_rpt,
dbo_vw_cp_rpt_loan_level_rca.category_code,
dbo_vw_cp_rpt_loan_level_rca.referral_dt,
dbo_vw_cp_rpt_loan_level_rca.completion_code,
dbo_vw_cp_rpt_loan_level_rca.completion_dt,
dbo_vw_cp_rpt_loan_level_rca.amt_rcvd, dbo_cp_loan_issues.issue_code,
dbo_cp_loan_issues.notice_dt, dbo_cp_loan_issues.completion_code,
dbo_cp_loan_issues.completion_dt, dbo_cp_loan_issues.amt_rcvd,
IIf(dbo_cp_loan_issues!completion_dt Is Null And dbo_cp_loan_issues!notice_dt
Is Not Null,"Yes","No") AS open_rcf, IIf(dbo_cp_loan_issues!completion_dt Is
Null,dbo_cp_loan_issues!notice_dt) AS rcf_notice_dt,
IIf(dbo_cp_loan_issues!completion_dt Is Null,"No","Yes") AS completed,
IIf(dbo_cp_loan_issues!completion_dt Is Not
Null,dbo_cp_loan_issues!notice_dt) AS completed_notice_dt,
dbo_cp_loan_issues!completion_code AS rcf_completion_code,
dbo_vw_cp_rpt_loan_level_rca.completion_dt AS rcf_completion_dt,
dbo_vw_cp_rpt_loan_level_rca.amt_rcvd AS rcf_amt_recd
FROM (dbo_vw_cp_rpt_loan_level_rca LEFT JOIN dbo_cp_loan_issues ON
dbo_vw_cp_rpt_loan_level_rca.ID = dbo_cp_loan_issues.ID) LEFT JOIN
dbo_counterparty ON dbo_cp_loan_issues.counterparty_id =
dbo_counterparty.counterparty_id
WHERE (((dbo_vw_cp_rpt_loan_level_rca.ID)="20") AND
((dbo_vw_cp_rpt_loan_level_rca.category_code)="CA") AND
((dbo_vw_cp_rpt_loan_level_rca.referral_dt) In (SELECT DISTINCT [referral_dt]
FROM dbo_vw_cp_rpt_loan_level_rca as TEST WHERE TEST.ID =
dbo_vw_cp_rpt_loan_level_rca.ID)));

the query result is showing the same [referral_dt] for all of the retrieved
records. why is this? there are 3 distinct referral_dt, and each of the
retrieved records has a referral_dt. but for some reason the same
referral_dt is being assigned to all of the records.

thanks in advance,
geebee

Click to expand...

Click to expand...

KARL DEWEY · Apr 23, 2009

You would need to build a totals query to count dates like this --
SELECT dbo_vw_cp_rpt_loan_level_rca.ID,
dbo_vw_cp_rpt_loan_level_rca.referral_dt,
Count(dbo_vw_cp_rpt_loan_level_rca.referral_dt) AS CountOfDate
FROM dbo_vw_cp_rpt_loan_level_rca
WHERE Count(dbo_vw_cp_rpt_loan_level_rca.referral_dt) >1
GROUP BY dbo_vw_cp_rpt_loan_level_rca.ID,
dbo_vw_cp_rpt_loan_level_rca.referral_dt;

Then left join to this query in your present query on
dbo_vw_cp_rpt_loan_level_rca.ID and dbo_vw_cp_rpt_loan_level_rca.referral_dt

But you still have to decide which record to display.

geebee said:
i suppose i could look at another column or something if there are more than
1 same date for an ID in the query results. like maybe i could look at the
count of date and ID to see if that count is >1. just not sure how to put
that in the query i now have.

thanks in advance,
geebee

KARL DEWEY said:

How will you decide which is not wanted?

geebee said:

hi,

that seems to have worked, but i am noticing that there may be cases in
which there are records retrieved in which there are the exact 2 same dates.
how do i get rid of one of them...keep in mind that all the other fields are
somewhat the same...some different.

thanks in advance,
geebee

:

Try dropping the last part of the WHERE statement in that you want DISTINCT
[referral_dt].

:

hi,

i have the following:

SELECT dbo_vw_cp_rpt_loan_level_rca.ID,
dbo_vw_cp_rpt_loan_level_rca.loan_id, dbo_vw_cp_rpt_loan_level_rca.deal_id,
dbo_vw_cp_rpt_loan_level_rca.vblock, dbo_vw_cp_rpt_loan_level_rca.issue_id,
dbo_vw_cp_rpt_loan_level_rca!issue_code AS issue_code,
dbo_vw_cp_rpt_loan_level_rca.borrw_name_corrected,
dbo_vw_cp_rpt_loan_level_rca.exception_requestor_name,
dbo_vw_cp_rpt_loan_level_rca.bal_orig, dbo_vw_cp_rpt_loan_level_rca.dt_purch,
dbo_vw_cp_rpt_loan_level_rca.business_line,
dbo_vw_cp_rpt_loan_level_rca.code_lien_stat,
dbo_vw_cp_rpt_loan_level_rca.code_origination_desc,
dbo_vw_cp_rpt_loan_level_rca.loan_stat_srvcr_curr,
dbo_vw_cp_rpt_loan_level_rca.correspondent_id,
dbo_vw_cp_rpt_loan_level_rca.seller, dbo_counterparty.code_approval_status,
dbo_vw_cp_rpt_loan_level_rca.res_score,
dbo_vw_cp_rpt_loan_level_rca.prin_bal_curr_rpt,
dbo_vw_cp_rpt_loan_level_rca.category_code,
dbo_vw_cp_rpt_loan_level_rca.referral_dt,
dbo_vw_cp_rpt_loan_level_rca.completion_code,
dbo_vw_cp_rpt_loan_level_rca.completion_dt,
dbo_vw_cp_rpt_loan_level_rca.amt_rcvd, dbo_cp_loan_issues.issue_code,
dbo_cp_loan_issues.notice_dt, dbo_cp_loan_issues.completion_code,
dbo_cp_loan_issues.completion_dt, dbo_cp_loan_issues.amt_rcvd,
IIf(dbo_cp_loan_issues!completion_dt Is Null And dbo_cp_loan_issues!notice_dt
Is Not Null,"Yes","No") AS open_rcf, IIf(dbo_cp_loan_issues!completion_dt Is
Null,dbo_cp_loan_issues!notice_dt) AS rcf_notice_dt,
IIf(dbo_cp_loan_issues!completion_dt Is Null,"No","Yes") AS completed,
IIf(dbo_cp_loan_issues!completion_dt Is Not
Null,dbo_cp_loan_issues!notice_dt) AS completed_notice_dt,
dbo_cp_loan_issues!completion_code AS rcf_completion_code,
dbo_vw_cp_rpt_loan_level_rca.completion_dt AS rcf_completion_dt,
dbo_vw_cp_rpt_loan_level_rca.amt_rcvd AS rcf_amt_recd
FROM (dbo_vw_cp_rpt_loan_level_rca LEFT JOIN dbo_cp_loan_issues ON
dbo_vw_cp_rpt_loan_level_rca.ID = dbo_cp_loan_issues.ID) LEFT JOIN
dbo_counterparty ON dbo_cp_loan_issues.counterparty_id =
dbo_counterparty.counterparty_id
WHERE (((dbo_vw_cp_rpt_loan_level_rca.ID)="20") AND
((dbo_vw_cp_rpt_loan_level_rca.category_code)="CA") AND
((dbo_vw_cp_rpt_loan_level_rca.referral_dt) In (SELECT DISTINCT [referral_dt]
FROM dbo_vw_cp_rpt_loan_level_rca as TEST WHERE TEST.ID =
dbo_vw_cp_rpt_loan_level_rca.ID)));

the query result is showing the same [referral_dt] for all of the retrieved
records. why is this? there are 3 distinct referral_dt, and each of the
retrieved records has a referral_dt. but for some reason the same
referral_dt is being assigned to all of the records.

thanks in advance,
geebee

Click to expand...

Click to expand...

geebee · Apr 23, 2009

i suppose i could look at the amt_rcvd column to determine which one to show
if there are more than 1 for the same date in the results. so how would i
amend the following:

SELECT dbo_vw_cp_rpt_loan_level_rca.ID,
dbo_vw_cp_rpt_loan_level_rca.loan_id, dbo_vw_cp_rpt_loan_level_rca.deal_id,
dbo_vw_cp_rpt_loan_level_rca.vblock, dbo_vw_cp_rpt_loan_level_rca.issue_id,
dbo_vw_cp_rpt_loan_level_rca!issue_code AS issue_code,
dbo_vw_cp_rpt_loan_level_rca.borrw_name_corrected,
dbo_vw_cp_rpt_loan_level_rca.exception_requestor_name,
dbo_vw_cp_rpt_loan_level_rca.bal_orig, dbo_vw_cp_rpt_loan_level_rca.dt_purch,
dbo_vw_cp_rpt_loan_level_rca.business_line,
dbo_vw_cp_rpt_loan_level_rca.code_lien_stat,
dbo_vw_cp_rpt_loan_level_rca.code_origination_desc,
dbo_vw_cp_rpt_loan_level_rca.loan_stat_srvcr_curr,
dbo_vw_cp_rpt_loan_level_rca.correspondent_id,
dbo_vw_cp_rpt_loan_level_rca.seller, dbo_counterparty.code_approval_status,
dbo_vw_cp_rpt_loan_level_rca.res_score,
dbo_vw_cp_rpt_loan_level_rca.prin_bal_curr_rpt,
dbo_vw_cp_rpt_loan_level_rca.category_code,
dbo_vw_cp_rpt_loan_level_rca.referral_dt,
dbo_vw_cp_rpt_loan_level_rca.completion_code,
dbo_vw_cp_rpt_loan_level_rca.completion_dt,
dbo_vw_cp_rpt_loan_level_rca.amt_rcvd, dbo_cp_loan_issues.issue_code,
dbo_cp_loan_issues.notice_dt, dbo_cp_loan_issues.completion_code,
dbo_cp_loan_issues.completion_dt, dbo_cp_loan_issues.amt_rcvd,
IIf(dbo_cp_loan_issues!completion_dt Is Null And dbo_cp_loan_issues!notice_dt
Is Not Null,"Yes","No") AS open_rcf, IIf(dbo_cp_loan_issues!completion_dt Is
Null,dbo_cp_loan_issues!notice_dt) AS rcf_notice_dt,
IIf(dbo_cp_loan_issues!completion_dt Is Null,"No","Yes") AS completed,
IIf(dbo_cp_loan_issues!completion_dt Is Not
Null,dbo_cp_loan_issues!notice_dt) AS completed_notice_dt,
dbo_cp_loan_issues!completion_code AS rcf_completion_code,
dbo_vw_cp_rpt_loan_level_rca.completion_dt AS rcf_completion_dt,
dbo_vw_cp_rpt_loan_level_rca.amt_rcvd AS rcf_amt_recd
FROM (dbo_vw_cp_rpt_loan_level_rca LEFT JOIN dbo_cp_loan_issues ON
dbo_vw_cp_rpt_loan_level_rca.ID = dbo_cp_loan_issues.ID) LEFT JOIN
dbo_counterparty ON dbo_cp_loan_issues.counterparty_id =
dbo_counterparty.counterparty_id
WHERE (((dbo_vw_cp_rpt_loan_level_rca.ID)="20") AND
((dbo_vw_cp_rpt_loan_level_rca.category_code)="CA") AND
((dbo_vw_cp_rpt_loan_level_rca.referral_dt) In (SELECT DISTINCT [referral_dt]
FROM dbo_vw_cp_rpt_loan_level_rca as TEST)));

thanks in advance,
geebee

KARL DEWEY said:
You would need to build a totals query to count dates like this --
SELECT dbo_vw_cp_rpt_loan_level_rca.ID,
dbo_vw_cp_rpt_loan_level_rca.referral_dt,
Count(dbo_vw_cp_rpt_loan_level_rca.referral_dt) AS CountOfDate
FROM dbo_vw_cp_rpt_loan_level_rca
WHERE Count(dbo_vw_cp_rpt_loan_level_rca.referral_dt) >1
GROUP BY dbo_vw_cp_rpt_loan_level_rca.ID,
dbo_vw_cp_rpt_loan_level_rca.referral_dt;

Then left join to this query in your present query on
dbo_vw_cp_rpt_loan_level_rca.ID and dbo_vw_cp_rpt_loan_level_rca.referral_dt

But you still have to decide which record to display.

geebee said:

i suppose i could look at another column or something if there are more than
1 same date for an ID in the query results. like maybe i could look at the
count of date and ID to see if that count is >1. just not sure how to put
that in the query i now have.

thanks in advance,
geebee

KARL DEWEY said:

How will you decide which is not wanted?

:

hi,

that seems to have worked, but i am noticing that there may be cases in
which there are records retrieved in which there are the exact 2 same dates.
how do i get rid of one of them...keep in mind that all the other fields are
somewhat the same...some different.

thanks in advance,
geebee

:

Try dropping the last part of the WHERE statement in that you want DISTINCT
[referral_dt].

:

hi,

i have the following:

SELECT dbo_vw_cp_rpt_loan_level_rca.ID,
dbo_vw_cp_rpt_loan_level_rca.loan_id, dbo_vw_cp_rpt_loan_level_rca.deal_id,
dbo_vw_cp_rpt_loan_level_rca.vblock, dbo_vw_cp_rpt_loan_level_rca.issue_id,
dbo_vw_cp_rpt_loan_level_rca!issue_code AS issue_code,
dbo_vw_cp_rpt_loan_level_rca.borrw_name_corrected,
dbo_vw_cp_rpt_loan_level_rca.exception_requestor_name,
dbo_vw_cp_rpt_loan_level_rca.bal_orig, dbo_vw_cp_rpt_loan_level_rca.dt_purch,
dbo_vw_cp_rpt_loan_level_rca.business_line,
dbo_vw_cp_rpt_loan_level_rca.code_lien_stat,
dbo_vw_cp_rpt_loan_level_rca.code_origination_desc,
dbo_vw_cp_rpt_loan_level_rca.loan_stat_srvcr_curr,
dbo_vw_cp_rpt_loan_level_rca.correspondent_id,
dbo_vw_cp_rpt_loan_level_rca.seller, dbo_counterparty.code_approval_status,
dbo_vw_cp_rpt_loan_level_rca.res_score,
dbo_vw_cp_rpt_loan_level_rca.prin_bal_curr_rpt,
dbo_vw_cp_rpt_loan_level_rca.category_code,
dbo_vw_cp_rpt_loan_level_rca.referral_dt,
dbo_vw_cp_rpt_loan_level_rca.completion_code,
dbo_vw_cp_rpt_loan_level_rca.completion_dt,
dbo_vw_cp_rpt_loan_level_rca.amt_rcvd, dbo_cp_loan_issues.issue_code,
dbo_cp_loan_issues.notice_dt, dbo_cp_loan_issues.completion_code,
dbo_cp_loan_issues.completion_dt, dbo_cp_loan_issues.amt_rcvd,
IIf(dbo_cp_loan_issues!completion_dt Is Null And dbo_cp_loan_issues!notice_dt
Is Not Null,"Yes","No") AS open_rcf, IIf(dbo_cp_loan_issues!completion_dt Is
Null,dbo_cp_loan_issues!notice_dt) AS rcf_notice_dt,
IIf(dbo_cp_loan_issues!completion_dt Is Null,"No","Yes") AS completed,
IIf(dbo_cp_loan_issues!completion_dt Is Not
Null,dbo_cp_loan_issues!notice_dt) AS completed_notice_dt,
dbo_cp_loan_issues!completion_code AS rcf_completion_code,
dbo_vw_cp_rpt_loan_level_rca.completion_dt AS rcf_completion_dt,
dbo_vw_cp_rpt_loan_level_rca.amt_rcvd AS rcf_amt_recd
FROM (dbo_vw_cp_rpt_loan_level_rca LEFT JOIN dbo_cp_loan_issues ON
dbo_vw_cp_rpt_loan_level_rca.ID = dbo_cp_loan_issues.ID) LEFT JOIN
dbo_counterparty ON dbo_cp_loan_issues.counterparty_id =
dbo_counterparty.counterparty_id
WHERE (((dbo_vw_cp_rpt_loan_level_rca.ID)="20") AND
((dbo_vw_cp_rpt_loan_level_rca.category_code)="CA") AND
((dbo_vw_cp_rpt_loan_level_rca.referral_dt) In (SELECT DISTINCT [referral_dt]
FROM dbo_vw_cp_rpt_loan_level_rca as TEST WHERE TEST.ID =
dbo_vw_cp_rpt_loan_level_rca.ID)));

the query result is showing the same [referral_dt] for all of the retrieved
records. why is this? there are 3 distinct referral_dt, and each of the
retrieved records has a referral_dt. but for some reason the same
referral_dt is being assigned to all of the records.

thanks in advance,
geebee

Click to expand...

Click to expand...

KARL DEWEY · Apr 23, 2009

Try this --
SELECT dbo_vw_cp_rpt_loan_level_rca.ID,
dbo_vw_cp_rpt_loan_level_rca.loan_id, dbo_vw_cp_rpt_loan_level_rca.deal_id,
dbo_vw_cp_rpt_loan_level_rca.vblock, dbo_vw_cp_rpt_loan_level_rca.issue_id,
dbo_vw_cp_rpt_loan_level_rca!issue_code AS issue_code,
dbo_vw_cp_rpt_loan_level_rca.borrw_name_corrected,
dbo_vw_cp_rpt_loan_level_rca.exception_requestor_name,
dbo_vw_cp_rpt_loan_level_rca.bal_orig, dbo_vw_cp_rpt_loan_level_rca.dt_purch,
dbo_vw_cp_rpt_loan_level_rca.business_line,
dbo_vw_cp_rpt_loan_level_rca.code_lien_stat,
dbo_vw_cp_rpt_loan_level_rca.code_origination_desc,
dbo_vw_cp_rpt_loan_level_rca.loan_stat_srvcr_curr,
dbo_vw_cp_rpt_loan_level_rca.correspondent_id,
dbo_vw_cp_rpt_loan_level_rca.seller, dbo_counterparty.code_approval_status,
dbo_vw_cp_rpt_loan_level_rca.res_score,
dbo_vw_cp_rpt_loan_level_rca.prin_bal_curr_rpt,
dbo_vw_cp_rpt_loan_level_rca.category_code,
dbo_vw_cp_rpt_loan_level_rca.referral_dt,
dbo_vw_cp_rpt_loan_level_rca.completion_code,
dbo_vw_cp_rpt_loan_level_rca.completion_dt,
dbo_vw_cp_rpt_loan_level_rca.amt_rcvd, dbo_cp_loan_issues.issue_code,
dbo_cp_loan_issues.notice_dt, dbo_cp_loan_issues.completion_code,
dbo_cp_loan_issues.completion_dt, dbo_cp_loan_issues.amt_rcvd,
IIf(dbo_cp_loan_issues!completion_dt Is Null And dbo_cp_loan_issues!notice_dt
Is Not Null,"Yes","No") AS open_rcf, IIf(dbo_cp_loan_issues!completion_dt Is
Null,dbo_cp_loan_issues!notice_dt) AS rcf_notice_dt,
IIf(dbo_cp_loan_issues!completion_dt Is Null,"No","Yes") AS completed,
IIf(dbo_cp_loan_issues!completion_dt Is Not
Null,dbo_cp_loan_issues!notice_dt) AS completed_notice_dt,
dbo_cp_loan_issues!completion_code AS rcf_completion_code,
dbo_vw_cp_rpt_loan_level_rca.completion_dt AS rcf_completion_dt,
dbo_vw_cp_rpt_loan_level_rca.amt_rcvd AS rcf_amt_recd
FROM (dbo_vw_cp_rpt_loan_level_rca LEFT JOIN dbo_cp_loan_issues ON
dbo_vw_cp_rpt_loan_level_rca.ID = dbo_cp_loan_issues.ID) LEFT JOIN
(dbo_counterparty ON dbo_cp_loan_issues.counterparty_id =
dbo_counterparty.counterparty_id) LEFT JOIN MultiDate ON
(dbo_vw_cp_rpt_loan_level_rca.ID = MultiDate.ID) AND
(dbo_vw_cp_rpt_loan_level_rca. referral_dt = MultiDate. referral_dt)
WHERE (dbo_vw_cp_rpt_loan_level_rca.ID)="20") AND
(dbo_vw_cp_rpt_loan_level_rca.category_code)="CA");

geebee · Apr 23, 2009

what is Multidate? I never saw that declared in the query. Getting a syntax
error in JOIN.

thanks in advance,
geebee

KARL DEWEY · Apr 23, 2009

That is the name I gave to the totals query I posted back a ways.
SELECT dbo_vw_cp_rpt_loan_level_rca.ID,
dbo_vw_cp_rpt_loan_level_rca.referral_dt,
Count(dbo_vw_cp_rpt_loan_level_rca.referral_dt) AS CountOfDate
FROM dbo_vw_cp_rpt_loan_level_rca
WHERE Count(dbo_vw_cp_rpt_loan_level_rca.referral_dt) >1
GROUP BY dbo_vw_cp_rpt_loan_level_rca.ID,
dbo_vw_cp_rpt_loan_level_rca.referral_dt;

geebee · Apr 24, 2009

hi,

i created and saved a separate "Multidate" query, but I am now getting a
JOIN error.

KARL DEWEY · Apr 24, 2009

Can you move your head a little to the left as it is hard for me to see your
screen.

Or maybe you can post what the error message reads.

geebee · Apr 24, 2009

hi,

"syntax error in join operation" pointing to WHERE.

thanks

KARL DEWEY · Apr 24, 2009

Ok, I took a guess on how to join the Multidate query.

Remove the join - like this now --
dbo_counterparty.counterparty_id) LEFT JOIN MultiDate ON
(dbo_vw_cp_rpt_loan_level_rca.ID = MultiDate.ID) AND
(dbo_vw_cp_rpt_loan_level_rca. referral_dt = MultiDate. referral_dt)
WHERE (dbo_vw_cp_rpt_loan_level_rca.ID)="20")

To look like this --
dbo_counterparty.counterparty_id), MultiDate
WHERE (dbo_vw_cp_rpt_loan_level_rca.ID)="20")

Then open in design view and try different joins of MultiDate fields ID and
referral_dt to different tables.