Complex query, please help

I think this is any easy one, and hopefully full DDL is not required as ther
e
is A LOT.
Goal: Create a report(using RS) that shows client name, id, total amt
loaned, total amt paid. Data will be displayed in a grid and the balance
must be displayed in the pertinent age category..so if Tom is 10 days late
his balance would be displayed in a 30 Days or Less column.
There are a total of 4 tables (2 for most of the data, 2 to display cust id
and name)
ISSUE: The probelm I am running into is that I have select the last date
where there a payment has not been posted where the date is > the due date
but and < the current date, but I receive mulitple records for each customer
.
Cust table A contains (Name, CustID)
Cust table B contains (CustID, LoanId)
Data table C contains (LoanId, LoanAmt)
Data table D contains (CustID, AmtPaid, Duedate,#of DaysLate) Contains
records up until end of loan schedule (could be 2008) I didn't create this
PSUEDO Code:
Select CustId, Name, LoanAmt, Balance (LoanAmt - AmtPaid )
From
Table A, Table B, Table, C, Table E (all joined)
Where the last Duedate < today and AmtPaid is null
SELECT DISTINCT
loan_payments.pmt_loan_id,
loan_payments.pmt_received_date, SUM(loan_payments.pmt_received_total) AS
PAID, loans.loan_cust_id,
loans.loan_total_amount, loans.loan_pending,
loans.loan_closed, Party.LName, Party.FName, loan_payments.pmt_due_date,
loan_payments.pmt_days_late
FROM Party RIGHT OUTER JOIN
Customer LEFT OUTER JOIN
loans LEFT OUTER JOIN
loan_payments ON loans.loan_id =
loan_payments.pmt_loan_id ON Customer.ID = loans.loan_cust_id ON Party.ID =
Customer.PartyID
WHERE (loans.loan_closed = 0) AND (loans.loan_pending = 0) AND
pmt_received_total is null AND pmt_days_late IN
(Select pmt_days_late
FROM loan_payments
WHERE pmt_days_late > 0
)
GROUP BY loan_payments.pmt_loan_id, loan_payments.pmt_received_date,
loans.loan_cust_id, loans.loan_total_amount, loans.loan_pending,
loans.loan_closed, Party.LName, Party.FName,
loan_payments.pmt_due_date,loan_payments.pmt_days_late
Order by loans.loan_cust_id
Thanks for any direction, advice, sites...Hi
You are wrong DDL is always the clearest and most useful thing to post.
In your query you do not need DISTINCT as you have an aggregate. If there
have multiple values then you are grouping on the wrong set of columns. You
may need to do your calculations in a derived table and then rejoin to the
load table to get the rows as you require.
I am not sure why you are using OUTER JOINS everywhere, I would have thought
that with load_payments being projected you can use an INNER JOIN for all
the JOINS.
It is usually clearer if you have your ON clause next to each JOIN it
relates to.
To get all those that have not paid in the date range try replacing:
AND pmt_days_late IN (Select pmt_days_late
FROM loan_payments
WHERE pmt_days_late > 0 )
with
AND EXISTS ( SELECT * FROM loan_payments d
WHERE d.due_date < getdate()
AND d.pmt_received_total is null
AND d.pmt_loan_id = loan_payments.pmt_loan_id )
If you wish to pivot this information you can use:
SELECT
p.pmt_loan_id,
SUM(p.pmt_received_total) AS PAID,
L.loan_cust_id,
L.loan_total_amount,
L.loan_pending,
L.loan_closed,
R.LName,
R.FName,
D.OverDue10,
D.OverDue20,
D.OverDue30
FROM Customer C
JOIN Party R ON R.ID = C.PartyID
JOIN loans L C.ID = L.loan_cust_id
JOIN loan_payments p ON L.loan_id = p.pmt_loan_id
JOIN
( SELECT pmt_load_id,
SUM(CASE WHEN DATEDIFF(dd,GetDate(),Duedate) <= 10 THEN AmtDue ELSE 0 END)
AS OverDue10,
SUM(CASE WHEN DATEDIFF(dd,GetDate(),Duedate) > 10 AND
DATEDIFF(dd,GetDate(),Duedate) <= 20 THEN AmtDue ELSE 0 END) AS OverDue20
SUM(CASE WHEN DATEDIFF(dd,GetDate(),Duedate) > 20 AND
DATEDIFF(dd,GetDate(),Duedate) <= 30 THEN AmtDue ELSE 0 END) AS OverDue30
FROM loan_payments
WHERE due_date < getdate()
AND pmt_received_total is null
GROUP BY pmt_load_id ) D ON D.pmt_load_id = L.pmt_load_id
WHERE L.loan_closed = 0
AND L.loan_pending = 0
AND EXISTS ( SELECT * FROM loan_payments y
WHERE y.due_date < getdate()
AND y.pmt_received_total is null
AND y.pmt_loan_id = p.pmt_loan_id )
John
"DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
news:6D4E75E5-9A8C-4E20-A372-63E0724967B4@.microsoft.com...
>I think this is any easy one, and hopefully full DDL is not required as
>there
> is A LOT.
> Goal: Create a report(using RS) that shows client name, id, total amt
> loaned, total amt paid. Data will be displayed in a grid and the balance
> must be displayed in the pertinent age category..so if Tom is 10 days late
> his balance would be displayed in a 30 Days or Less column.
> There are a total of 4 tables (2 for most of the data, 2 to display cust
> id
> and name)
> ISSUE: The probelm I am running into is that I have select the last date
> where there a payment has not been posted where the date is > the due date
> but and < the current date, but I receive mulitple records for each
> customer.
> Cust table A contains (Name, CustID)
> Cust table B contains (CustID, LoanId)
> Data table C contains (LoanId, LoanAmt)
> Data table D contains (CustID, AmtPaid, Duedate,#of DaysLate) Contains
> records up until end of loan schedule (could be 2008) I didn't create this
> PSUEDO Code:
> Select CustId, Name, LoanAmt, Balance (LoanAmt - AmtPaid )
> From
> Table A, Table B, Table, C, Table E (all joined)
> Where the last Duedate < today and AmtPaid is null
> SELECT DISTINCT
> loan_payments.pmt_loan_id,
> loan_payments.pmt_received_date, SUM(loan_payments.pmt_received_total) AS
> PAID, loans.loan_cust_id,
> loans.loan_total_amount, loans.loan_pending,
> loans.loan_closed, Party.LName, Party.FName, loan_payments.pmt_due_date,
> loan_payments.pmt_days_late
> FROM Party RIGHT OUTER JOIN
> Customer LEFT OUTER JOIN
> loans LEFT OUTER JOIN
> loan_payments ON loans.loan_id =
> loan_payments.pmt_loan_id ON Customer.ID = loans.loan_cust_id ON Party.ID
> =
> Customer.PartyID
> WHERE (loans.loan_closed = 0) AND (loans.loan_pending = 0) AND
> pmt_received_total is null AND pmt_days_late IN
> (Select pmt_days_late
> FROM loan_payments
> WHERE pmt_days_late > 0
> )
> GROUP BY loan_payments.pmt_loan_id, loan_payments.pmt_received_date,
> loans.loan_cust_id, loans.loan_total_amount, loans.loan_pending,
> loans.loan_closed, Party.LName, Party.FName,
> loan_payments.pmt_due_date,loan_payments.pmt_days_late
> Order by loans.loan_cust_id
> Thanks for any direction, advice, sites...
>|||I am wrong in that this is not an easy one or with regards to the DDL? I
thank you for your reply. I apologize as I do not know how to derive the
query, hence my asking for help. I guess if I would have posted the DDL the
n
I wouldn't be receiving errors. (Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'On'.
Server: Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near 'D'.)
Please see DDL below.
Once again thank you
CREATE TABLE [loans] (
[loan_id] [int] IDENTITY (1000, 1) NOT NULL ,
[loan_typ_id] [int] NOT NULL ,
[loan_cust_id] [int] NOT NULL ,
[loan_emp_id] [int] NOT NULL ,
[loan_cnt_id] [int] NULL ,
[loan_tax_total_amt] [decimal](9, 4) NOT NULL ,
[loan_total_amount] [decimal](9, 2) NOT NULL ,
[loan_amount_down] [decimal](9, 2) NOT NULL ,
[loan_payment_amount] [decimal](9, 2) NOT NULL ,
[loan_interest_rate] [decimal](9, 2) NOT NULL ,
[loan_first_due_date] [datetime] NOT NULL ,
[loan_pending] [tinyint] NOT NULL ,
[loan_closed] [tinyint] NOT NULL ,
[loan_date_created] [datetime] NOT NULL ,
[loan_date_closed] [datetime] NULL ,
[loan_clarion_total] [decimal](9, 2) NULL ,
[loan_clarion_amtfin] [decimal](9, 2) NULL ,
[loan_clarion_adj_total] [decimal](9, 2) NULL ,
[loan_amtfin_chk] [int] NULL ,
[loan_contract_chk] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [loan_payments] (
[pmt_loan_id] [int] NOT NULL ,
[pmt_number] [int] NOT NULL ,
[pmt_type] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pmt_due_date] [datetime] NOT NULL ,
[pmt_due_total] [decimal](9, 2) NOT NULL ,
[pmt_due_taxes_total] [decimal](9, 4) NULL ,
[pmt_due_principal] [decimal](9, 2) NOT NULL ,
[pmt_due_interest] [decimal](9, 2) NOT NULL ,
[pmt_late_date] [datetime] NOT NULL ,
[pmt_late_fee] [decimal](9, 2) NOT NULL ,
[pmt_received_date] [datetime] NULL ,
[pmt_received_total] [decimal](9, 2) NULL ,
[pmt_received_taxes_total] [decimal](9, 4) NULL ,
[pmt_received_principal] [decimal](9, 2) NULL ,
[pmt_received_interest] [decimal](9, 2) NULL ,
[pmt_received_late_fee] [decimal](9, 2) NULL ,
[pmt_received_mtd_id] [int] NULL ,
[pmt_received_emp_id] [int] NULL ,
[pmt_recorded_date] [datetime] NULL ,
[pmt_trk_pmt_num] [int] NULL ,
[pmt_source] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pmt_days_late] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [Party] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[LName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MaritalStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Age] [int] NULL ,
[Attention] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Department] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone1] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax1] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EMail] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Orig_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[County] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LengthAtAddress] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OwnRent] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [Customer] (
[ID] [int] IDENTITY (1000, 1) NOT NULL ,
[PartyID] [int] NULL ,
[ShipPartyID] [int] NULL ,
[Pending] [tinyint] NOT NULL ,
[BirthDate] [datetime] NULL ,
[SSNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmployeeID] [int] NOT NULL ,
[License] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LicenseVerified] [int] NULL ,
[LicenseExp] [datetime] NULL ,
[ScrubSize] [varchar] (400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReferralID] [int] NULL ,
[DoNotContact] [bit] NOT NULL ,
[BestDay] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BestTime] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TimeZone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranscriptionLocation] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Bank] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RoutingNumber] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CheckingNumber] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SavingsNumber] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BankCity] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BankAddress] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BankState] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BankZip] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BankPhone] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CCType] [int] NULL ,
[CCNumber] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CCName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CCExpMM] [int] NULL ,
[CCExpYYYY] [int] NULL ,
[CCVerificationNumber] [int] NULL ,
[CCBillingFrequency] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[EmployerPartyID] [int] NULL ,
[PrevEmpID] [int] NULL ,
[RelativePartyID] [int] NULL ,
[DriverLicense] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DriverLicenseState] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[NursingLicState] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NursingLicenseNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ListOfNurses] [bit] NOT NULL ,
[StudyBuddy] [bit] NOT NULL ,
[WebFormAcct] [bit] NOT NULL ,
[LONBegin] [datetime] NULL ,
[LONEnd] [datetime] NULL ,
[SBBegin] [datetime] NULL ,
[SBEnd] [datetime] NULL ,
[WFBegin] [datetime] NULL ,
[WFEnd] [datetime] NULL ,
[ClinicalExamDate] [datetime] NULL ,
[ClinicalPassed] [bit] NOT NULL ,
[EnrollDate] [datetime] NULL ,
[Enroll] [bit] NULL ,
[GraduationExamDate] [datetime] NOT NULL ,
[GraduationPassed] [bit] NOT NULL ,
[StateBoardDate] [datetime] NULL ,
[StateBoardPassed] [bit] NOT NULL ,
[DegreeGoal] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateCreated] [datetime] NOT NULL ,
[GroupID] [int] NULL ,
[Orig_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Import_Status] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pending_RecNo] [int] NULL ,
[DOB] [datetime] NULL ,
[MID] [int] NULL ,
[LastFUDate] [datetime] NOT NULL ,
[ReferralName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StudentLoan] [bit] NULL ,
[NumberStudentLoan] [int] NULL ,
[Scholarship] [int] NULL ,
[ScholarshipName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LPNTimeLength] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
"John Bell" wrote:

> Hi
> You are wrong DDL is always the clearest and most useful thing to post.
> In your query you do not need DISTINCT as you have an aggregate. If there
> have multiple values then you are grouping on the wrong set of columns. Yo
u
> may need to do your calculations in a derived table and then rejoin to the
> load table to get the rows as you require.
> I am not sure why you are using OUTER JOINS everywhere, I would have thoug
ht
> that with load_payments being projected you can use an INNER JOIN for all
> the JOINS.
> It is usually clearer if you have your ON clause next to each JOIN it
> relates to.
> To get all those that have not paid in the date range try replacing:
> AND pmt_days_late IN (Select pmt_days_late
> FROM loan_payments
> WHERE pmt_days_late > 0 )
> with
> AND EXISTS ( SELECT * FROM loan_payments d
> WHERE d.due_date < getdate()
> AND d.pmt_received_total is null
> AND d.pmt_loan_id = loan_payments.pmt_loan_id )
> If you wish to pivot this information you can use:
> SELECT
> p.pmt_loan_id,
> SUM(p.pmt_received_total) AS PAID,
> L.loan_cust_id,
> L.loan_total_amount,
> L.loan_pending,
> L.loan_closed,
> R.LName,
> R.FName,
> D.OverDue10,
> D.OverDue20,
> D.OverDue30
> FROM Customer C
> JOIN Party R ON R.ID = C.PartyID
> JOIN loans L C.ID = L.loan_cust_id
> JOIN loan_payments p ON L.loan_id = p.pmt_loan_id
> JOIN
> ( SELECT pmt_load_id,
> SUM(CASE WHEN DATEDIFF(dd,GetDate(),Duedate) <= 10 THEN AmtDue ELSE 0 END)
> AS OverDue10,
> SUM(CASE WHEN DATEDIFF(dd,GetDate(),Duedate) > 10 AND
> DATEDIFF(dd,GetDate(),Duedate) <= 20 THEN AmtDue ELSE 0 END) AS OverDue20
> SUM(CASE WHEN DATEDIFF(dd,GetDate(),Duedate) > 20 AND
> DATEDIFF(dd,GetDate(),Duedate) <= 30 THEN AmtDue ELSE 0 END) AS OverDue30
> FROM loan_payments
> WHERE due_date < getdate()
> AND pmt_received_total is null
> GROUP BY pmt_load_id ) D ON D.pmt_load_id = L.pmt_load_id
> WHERE L.loan_closed = 0
> AND L.loan_pending = 0
> AND EXISTS ( SELECT * FROM loan_payments y
> WHERE y.due_date < getdate()
> AND y.pmt_received_total is null
> AND y.pmt_loan_id = p.pmt_loan_id )
> John
> "DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
> news:6D4E75E5-9A8C-4E20-A372-63E0724967B4@.microsoft.com...
>
>|||Although your syntax is technically ok, it is a difficult to read and/or to
maintain. I recommend that you consistently use table Aliases, and separate
teh major clauses of the query, use indenting, and cosistently use one type
of Outer Join (Left or Right), but do not mix them. Also, unless absilutel
y
necessary, keep join conditions next to join - If you can;t then use
parenmtheses to make clear what is happeningLook at the below to see how muc
h
easier it is to erad and comprehend then what you posted...
SELECT P.pmt_loan_id, P.pmt_received_date,
SUM(P.pmt_received_total) PAID,
L.loan_cust_id, L.loan_total_amount,
L.loan_pending, L.loan_closed, R.LName,
R.FName, P.pmt_due_date,
P.pmt_days_late
-- --
From Loans L
Left Join Loan_Payments P On P.pmt_loan_id = L.loan_id
Left Join Customer C On C.ID = L.loan_cust_id
Left Join Party R On R.ID = C.PartyID
-- --
Where L.loan_closed = 0
And (L.loan_pending = 0)
And pmt_received_total Is Null
And pmt_days_late IN
(Select pmt_days_late
From loan_payments
From pmt_days_late > 0)
-- --
GROUP BY P.pmt_loan_id, P.pmt_received_date,
L.loan_cust_id, L.loan_total_amount,
L.loan_pending, L.loan_closed, R.LName,
R.FName, P.pmt_due_date,
P.pmt_days_late
-- --
Order by L.loan_cust_id
-- --
-- --
"DigitalVixen" wrote:

> I think this is any easy one, and hopefully full DDL is not required as th
ere
> is A LOT.
> Goal: Create a report(using RS) that shows client name, id, total amt
> loaned, total amt paid. Data will be displayed in a grid and the balance
> must be displayed in the pertinent age category..so if Tom is 10 days late
> his balance would be displayed in a 30 Days or Less column.
> There are a total of 4 tables (2 for most of the data, 2 to display cust i
d
> and name)
> ISSUE: The probelm I am running into is that I have select the last date
> where there a payment has not been posted where the date is > the due date
> but and < the current date, but I receive mulitple records for each custom
er.
> Cust table A contains (Name, CustID)
> Cust table B contains (CustID, LoanId)
> Data table C contains (LoanId, LoanAmt)
> Data table D contains (CustID, AmtPaid, Duedate,#of DaysLate) Contains
> records up until end of loan schedule (could be 2008) I didn't create this
> PSUEDO Code:
> Select CustId, Name, LoanAmt, Balance (LoanAmt - AmtPaid )
> From
> Table A, Table B, Table, C, Table E (all joined)
> Where the last Duedate < today and AmtPaid is null
> SELECT DISTINCT
> loan_payments.pmt_loan_id,
> loan_payments.pmt_received_date, SUM(loan_payments.pmt_received_total) AS
> PAID, loans.loan_cust_id,
> loans.loan_total_amount, loans.loan_pending,
> loans.loan_closed, Party.LName, Party.FName, loan_payments.pmt_due_date,
> loan_payments.pmt_days_late
> FROM Party RIGHT OUTER JOIN
> Customer LEFT OUTER JOIN
> loans LEFT OUTER JOIN
> loan_payments ON loans.loan_id =
> loan_payments.pmt_loan_id ON Customer.ID = loans.loan_cust_id ON Party.ID
=
> Customer.PartyID
> WHERE (loans.loan_closed = 0) AND (loans.loan_pending = 0) AND
> pmt_received_total is null AND pmt_days_late IN
> (Select pmt_days_late
> FROM loan_payments
> WHERE pmt_days_late > 0
> )
> GROUP BY loan_payments.pmt_loan_id, loan_payments.pmt_received_date,
> loans.loan_cust_id, loans.loan_total_amount, loans.loan_pending,
> loans.loan_closed, Party.LName, Party.FName,
> loan_payments.pmt_due_date,loan_payments.pmt_days_late
> Order by loans.loan_cust_id
> Thanks for any direction, advice, sites...
>|||And the error you describe is because the Join COndiditions were not properl
y
nested - caused by poor formatting.
"CBretana" wrote:
> Although your syntax is technically ok, it is a difficult to read and/or t
o
> maintain. I recommend that you consistently use table Aliases, and separa
te
> teh major clauses of the query, use indenting, and cosistently use one typ
e
> of Outer Join (Left or Right), but do not mix them. Also, unless absilut
ely
> necessary, keep join conditions next to join - If you can;t then use
> parenmtheses to make clear what is happeningLook at the below to see how m
uch
> easier it is to erad and comprehend then what you posted...
>
> SELECT P.pmt_loan_id, P.pmt_received_date,
> SUM(P.pmt_received_total) PAID,
> L.loan_cust_id, L.loan_total_amount,
> L.loan_pending, L.loan_closed, R.LName,
> R.FName, P.pmt_due_date,
> P.pmt_days_late
> -- --
> From Loans L
> Left Join Loan_Payments P On P.pmt_loan_id = L.loan_id
> Left Join Customer C On C.ID = L.loan_cust_id
> Left Join Party R On R.ID = C.PartyID
> -- --
> Where L.loan_closed = 0
> And (L.loan_pending = 0)
> And pmt_received_total Is Null
> And pmt_days_late IN
> (Select pmt_days_late
> From loan_payments
> From pmt_days_late > 0)
> -- --
> GROUP BY P.pmt_loan_id, P.pmt_received_date,
> L.loan_cust_id, L.loan_total_amount,
> L.loan_pending, L.loan_closed, R.LName,
> R.FName, P.pmt_due_date,
> P.pmt_days_late
> -- --
> Order by L.loan_cust_id
> -- --
> -- --
>
> "DigitalVixen" wrote:
>|||Thanks but I still get a record for each due date where the
pmt_recieved_total is null. I need to display 1 record (being the oldest du
e
due date where pmt_received_total is null) for each cust_id.
"CBretana" wrote:
> Although your syntax is technically ok, it is a difficult to read and/or t
o
> maintain. I recommend that you consistently use table Aliases, and separa
te
> teh major clauses of the query, use indenting, and cosistently use one typ
e
> of Outer Join (Left or Right), but do not mix them. Also, unless absilut
ely
> necessary, keep join conditions next to join - If you can;t then use
> parenmtheses to make clear what is happeningLook at the below to see how m
uch
> easier it is to erad and comprehend then what you posted...
>
> SELECT P.pmt_loan_id, P.pmt_received_date,
> SUM(P.pmt_received_total) PAID,
> L.loan_cust_id, L.loan_total_amount,
> L.loan_pending, L.loan_closed, R.LName,
> R.FName, P.pmt_due_date,
> P.pmt_days_late
> -- --
> From Loans L
> Left Join Loan_Payments P On P.pmt_loan_id = L.loan_id
> Left Join Customer C On C.ID = L.loan_cust_id
> Left Join Party R On R.ID = C.PartyID
> -- --
> Where L.loan_closed = 0
> And (L.loan_pending = 0)
> And pmt_received_total Is Null
> And pmt_days_late IN
> (Select pmt_days_late
> From loan_payments
> From pmt_days_late > 0)
> -- --
> GROUP BY P.pmt_loan_id, P.pmt_received_date,
> L.loan_cust_id, L.loan_total_amount,
> L.loan_pending, L.loan_closed, R.LName,
> R.FName, P.pmt_due_date,
> P.pmt_days_late
> -- --
> Order by L.loan_cust_id
> -- --
> -- --
>
> "DigitalVixen" wrote:
>|||Then you cannot have the Due Date in the Group BY Clause, Doing so tells th
e
Query Processor to output one row per Due Date...
Also, since you want the SUM(pmt_received_total) to include ALL Payments,
you cannot restrict the query to only those payment records where payment is
Null...
So, one way to do this is t ojoin to the payments table twice, once with all
the records, so we can so the Sum, and once to only get the last record than
SELECT P.pmt_loan_id,
SUM(P.pmt_received_total) PAID,
L.loan_cust_id, L.loan_total_amount,
L.loan_pending, L.loan_closed,
R.LName, R.FName,
LP.pmt_received_date, LP.pmt_due_date,
LP.pmt_days_late
-- --
From Loans L
Left Join Customer C On C.ID = L.loan_cust_id
Left Join Party R On R.ID = C.PartyID
Left Join Loan_Payments P On P.pmt_loan_id = L.loan_id
Left Join Loan_Payments LP
On LP.pmt_loan_id = L.loan_id
And LP.pmt_due_date =
(Select Max(pmt_due_date)
From Loan_Payments
Where pmt_loan_id = L.loan_id
And pmt_received_total Is Null
And pmt_days_late > 0)
-- --
Where L.loan_closed = 0
And (L.loan_pending = 0)
-- --
GROUP BY L.loan_cust_id,
P.pmt_loan_id,
L.loan_total_amount,
L.loan_pending, L.loan_closed,
R.LName, R.FName, LP.pmt_due_date,
LP.pmt_received_date, LP.pmt_days_late
-- --
Order by L.loan_cust_id
"DigitalVixen" wrote:
> Thanks but I still get a record for each due date where the
> pmt_recieved_total is null. I need to display 1 record (being the oldest
due
> due date where pmt_received_total is null) for each cust_id.
> "CBretana" wrote:
>|||>> Cust table A contains (Name, CustID)
Cust table B contains (CustID, LoanId)
Data table C contains (LoanId, LoanAmt)
Data table D contains (CustID, AmtPaid, Duedate,#of DaysLate) Contains
records [sic] up until end of loan schedule (could be 2008) I didn't
create this <<
Can you kill the guy that did? You need tables like:
LoanCoupons (loan_id, cust_id, loan_amt, due_date)
LoanPayments (loan_id, cust_id, payment_amt, payment_date)
Now you resolve these together to get the current status of each loan.
The normalization is awful in what you posted.
For the temporal stuff, use a SUM(CASE..) to get the ranges.
And why are you using all those OUTER JOINs? Don' t you have any DRI
in the schema?|||LOL, if only I knew. I am a newbie, please explain what is DRI?
"--CELKO--" wrote:

> Cust table B contains (CustID, LoanId)
> Data table C contains (LoanId, LoanAmt)
> Data table D contains (CustID, AmtPaid, Duedate,#of DaysLate) Contains
> records [sic] up until end of loan schedule (could be 2008) I didn't
> create this <<
> Can you kill the guy that did? You need tables like:
> LoanCoupons (loan_id, cust_id, loan_amt, due_date)
> LoanPayments (loan_id, cust_id, payment_amt, payment_date)
> Now you resolve these together to get the current status of each loan.
> The normalization is awful in what you posted.
> For the temporal stuff, use a SUM(CASE..) to get the ranges.
> And why are you using all those OUTER JOINs? Don' t you have any DRI
> in the schema?
>|||DRI = Declarative Referential Integrity. It is a mechanism that prevents you
from orphaning rows in a table. For example, if you have the following:
Create Table Customers
(
CustomerId Int
, FirstName VarChar(25)
, LastName VarChar(25)
)
Create Table Orders
(
CustomerId Int
, OrderDate DateTime
, OrderNumber Int
)
Without DRI, there is nothing to prevent you from accidently putting a
CustomerId value in the Orders table that does not exist in the Customers ta
ble.
The problem is that we then do not have any idea to whom the order belongs.
Further, without DRI mechanims, if you deleted a customer from the Customers
table, those customers might have orders in the Orders table. Those Orders w
ould
now be orphaned (the child table Orders would no longer have any parent
Customers) and again we would have no idea who made those Orders.
There are a handful of ways to enable DRI in SQL Server:
1. The "References" statement in the Create Table statement like so:
Create Table Orders
(
CustomerId Int References Customers(CustomerId)
, OrderDate DateTime
, OrderNumber Int
)
Note that you use the References statement on the child portion of the equat
ion.
2. Create a data diagram in the Enterprise Manager. Add both tables to the
display and drag from the column of one table to the matching column of the
other table. There are tutorials online with pretty pictures that better
illustrate this process.
There are other options that go with enabling DRI between two tables, but fo
r
now, that should get you started.
Thomas
"DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
news:73919415-C2A2-42E8-A058-FD3AF57E12F2@.microsoft.com...
> LOL, if only I knew. I am a newbie, please explain what is DRI?
> "--CELKO--" wrote:
>