OK, everyone. I have hit a wall while trying to accomplish the
following goal. I have a table (tblDocInfo) that stores information
about documents. Among the information being stored, I have three
fields that store information about attorneys (ReviewingAtty,
DraftingAtty, ContactAtty) who are usually three different people. The
information I am storing in those three fields is of type INT. Those
numbers correspond to the Primary Key in another table (tblAtty) where
the attorney data is stored. For example:
tblDocInfo {
ID int
ReviewingAtty int
DraftingAtty int
ContactAtty int
docDate date
Comments varchar
}
I am trying to execute a query where I can pull the attorney names
along with some other document information, but I am not getting the
results that I want as the three attorneys are normally different and
only one name is coming accross.
For example, if in "tblDocInfo" I have this data (see format above):
23, 2, 4, 6, 23/09/05, Will
24, 1, 5, 3, 11/05/05, Contract
30, 2, 2, 6, 22/09/05, Letter
41, 4, 4, 5, 23/09/05, Warning
and in "tblAtty" I have this data:
1, John Doe
2, Jane Doe
3, Peter Somebody
4, Joe Blow
5, John Smith
6, Michael Cooper
I would like my query to display this:
23, Jane Doe, Joe Blow, Michael Cooper, 23/09/05, Will
24, John Doe, John Smith, Peter Somebody, 11/05/05, Contract
30, Jane Doe, Jane Doe, Michael Cooper, 22/09/05, Letter
41, Joe Blow, Joe Blow, John Smith, 23/09/05, Warning
Thank you.
MarcosSomething like this...
select a.id,at1.Name,at2.Name,at3.Name,a.docDate,a.comments
from tblDocInfo a
join tblAtty a1
on a1.id = a. ReviewingAtty
join tblAtty a2
on a2.id = a. DraftingAtty
join tblAtty a3
on a3.id = a. ContactAtty
group by a.id,at1.Name,at2.Name,at3.Name,a.docDate,a.comments
MJKulangara
http://sqladventures.blogspot.com|||I only see two tables here, and I think there is certainly a better and more
relational design waiting for you to discover it. But for now:
USE tempdb;
GO
CREATE TABLE dbo.Attorneys
(
AttorneyID INT PRIMARY KEY,
FullName VARCHAR(64)
);
GO
CREATE TABLE dbo.DocumentInfo
(
DocumentID INT PRIMARY KEY,
ReviewingAttorney INT NULL FOREIGN KEY REFERENCES
dbo.Attorneys(AttorneyID),
DraftingAttorney INT NULL FOREIGN KEY REFERENCES
dbo.Attorneys(AttorneyID),
ContactAttorney INT NULL FOREIGN KEY REFERENCES
dbo.Attorneys(AttorneyID),
docDate SMALLDATETIME,
Comments VARCHAR(255)
);
GO
SET NOCOUNT ON;
INSERT dbo.Attorneys SELECT 1, 'John Doe';
INSERT dbo.Attorneys SELECT 2, 'Jane Doe';
INSERT dbo.Attorneys SELECT 3, 'Peter Somebody';
INSERT dbo.Attorneys SELECT 4, 'Joe Blow';
INSERT dbo.Attorneys SELECT 5, 'John Smith';
INSERT dbo.Attorneys SELECT 6, 'Michael Cooper';
INSERT dbo.DocumentInfo SELECT 23, 2, 4, 6, '20050923', 'Will';
INSERT dbo.DocumentInfo SELECT 24, 1, 5, 3, '20050511', 'Contract';
INSERT dbo.DocumentInfo SELECT 30, 2, 2, 6, '20050922', 'Letter';
INSERT dbo.DocumentInfo SELECT 41, 4, 4, 5, '20050923', 'Warning';
SELECT d.DocumentID, a1.FullName, a2.FullName, a3.FullName, d.docDate,
d.Comments
FROM dbo.documentInfo d
LEFT OUTER JOIN dbo.Attorneys a1
ON d.ReviewingAttorney = a1.AttorneyID
LEFT OUTER JOIN dbo.Attorneys a2
ON d.DraftingAttorney = a2.AttorneyID
LEFT OUTER JOIN dbo.Attorneys a3
ON d.ContactAttorney = a3.AttorneyID
GO
DROP TABLE dbo.DocumentInfo;
DROP TABLE dbo.Attorneys;
GO
"bodhipooh" <marcos.velez@.ipm.com> wrote in message
news:1139244065.032852.306060@.o13g2000cwo.googlegroups.com...
> OK, everyone. I have hit a wall while trying to accomplish the
> following goal. I have a table (tblDocInfo) that stores information
> about documents. Among the information being stored, I have three
> fields that store information about attorneys (ReviewingAtty,
> DraftingAtty, ContactAtty) who are usually three different people. The
> information I am storing in those three fields is of type INT. Those
> numbers correspond to the Primary Key in another table (tblAtty) where
> the attorney data is stored. For example:
> tblDocInfo {
> ID int
> ReviewingAtty int
> DraftingAtty int
> ContactAtty int
> docDate date
> Comments varchar
> }
> I am trying to execute a query where I can pull the attorney names
> along with some other document information, but I am not getting the
> results that I want as the three attorneys are normally different and
> only one name is coming accross.
> For example, if in "tblDocInfo" I have this data (see format above):
> 23, 2, 4, 6, 23/09/05, Will
> 24, 1, 5, 3, 11/05/05, Contract
> 30, 2, 2, 6, 22/09/05, Letter
> 41, 4, 4, 5, 23/09/05, Warning
> and in "tblAtty" I have this data:
> 1, John Doe
> 2, Jane Doe
> 3, Peter Somebody
> 4, Joe Blow
> 5, John Smith
> 6, Michael Cooper
> I would like my query to display this:
> 23, Jane Doe, Joe Blow, Michael Cooper, 23/09/05, Will
> 24, John Doe, John Smith, Peter Somebody, 11/05/05, Contract
> 30, Jane Doe, Jane Doe, Michael Cooper, 22/09/05, Letter
> 41, Joe Blow, Joe Blow, John Smith, 23/09/05, Warning
>
> Thank you.
> Marcos
>|||Thank you all.
First of all, I got the problem solved. I was trying to do the JOIN
operation, but had a mistake in the order (syntax will always get
you!!) Your help is greatly appreciated. Second, I apologize for not
posting my entire table, or much more information. I realize that
information is often necessary to make it possible for others to be
able to help me, but I am working within some confidentiality
constraints and had to minimize the amount of information that was to
be posted. Still your replies have been VERY helpful.
Marcos|||This example may give you some idea on how to go about this:
http://milambda.blogspot.com/2005/0...s-as-array.html
For a better solution please post DDL and sample data.
ML
http://milambda.blogspot.com/|||SELECT t1.[ID], t2.[AttyName], t3.[AttyName], t4.[AttyName], t1.docdate,
t1.comments
FROM tblDocInfo t1
INNER JOIN tblAtty t2 ON t1.ReviewingAtty = t2.AttyID
INNER JOIN tblAtty t3 ON t1.DraftingAtty = t2.AttyID
INNER JOIN tblAtty t4 ON t1.ContactAtty = t2.AttyID
This would work as long as you don't allow any NULLs.
"bodhipooh" wrote:
> OK, everyone. I have hit a wall while trying to accomplish the
> following goal. I have a table (tblDocInfo) that stores information
> about documents. Among the information being stored, I have three
> fields that store information about attorneys (ReviewingAtty,
> DraftingAtty, ContactAtty) who are usually three different people. The
> information I am storing in those three fields is of type INT. Those
> numbers correspond to the Primary Key in another table (tblAtty) where
> the attorney data is stored. For example:
> tblDocInfo {
> ID int
> ReviewingAtty int
> DraftingAtty int
> ContactAtty int
> docDate date
> Comments varchar
> }
> I am trying to execute a query where I can pull the attorney names
> along with some other document information, but I am not getting the
> results that I want as the three attorneys are normally different and
> only one name is coming accross.
> For example, if in "tblDocInfo" I have this data (see format above):
> 23, 2, 4, 6, 23/09/05, Will
> 24, 1, 5, 3, 11/05/05, Contract
> 30, 2, 2, 6, 22/09/05, Letter
> 41, 4, 4, 5, 23/09/05, Warning
> and in "tblAtty" I have this data:
> 1, John Doe
> 2, Jane Doe
> 3, Peter Somebody
> 4, Joe Blow
> 5, John Smith
> 6, Michael Cooper
> I would like my query to display this:
> 23, Jane Doe, Joe Blow, Michael Cooper, 23/09/05, Will
> 24, John Doe, John Smith, Peter Somebody, 11/05/05, Contract
> 30, Jane Doe, Jane Doe, Michael Cooper, 22/09/05, Letter
> 41, Joe Blow, Joe Blow, John Smith, 23/09/05, Warning
>
> Thank you.
> Marcos
>
Showing posts with label goal. Show all posts
Showing posts with label goal. Show all posts
Wednesday, March 7, 2012
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:
>
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:
>
Saturday, February 25, 2012
Complex Query help needed fast!
The goal is to take the low and high values out of each record, then get the
average of the remaining fields. For ex, ID1 has five values, remove 2 and
100 then add (40+20+10)/3...then do this for each record. The number of
values in the five fields can vary as shown below.
I have a table that looks like this:
ID F1 F2 F3 F4 F5 field names
1 100 40 20 2 10 Values
2 4 140 10 42
3 10 189 22
4 20 10 24 332 3
Thanks in advance for this query.One way is to use the unpivot operator. It's tested in MSSQL2005.
select id, (sum(f) - max(f) - min(f))/(count(*) - 2.0) avg
from (select id, f from test unpivot (f for t in (f1,f2,f3,f4,f5)) p) t
group by id
"KT" <ktdev@.hotmail.com> wrote in message
news:%23RBOH3BPGHA.456@.TK2MSFTNGP15.phx.gbl...
> The goal is to take the low and high values out of each record, then get
> the
> average of the remaining fields. For ex, ID1 has five values, remove 2
> and
> 100 then add (40+20+10)/3...then do this for each record. The number of
> values in the five fields can vary as shown below.
>
>
> I have a table that looks like this:
>
> ID F1 F2 F3 F4 F5 field names
> 1 100 40 20 2 10 Values
> 2 4 140 10 42
> 3 10 189 22
>
> 4 20 10 24 332 3
>
>
> Thanks in advance for this query.|||See if this helps. I'm assuming the blank column values contain NULL.
If they contain something else, you'll have to change F1 through F5 to
CASE WHEN F1 = <whatever the blank is> THEN NULL ELSE F1 END,
and so on.
select
ID, (SUM(Fval)-MAX(Fval)-MIN(Fval))/(COUNT(Fval)-2) as trimMean
from (
select
ID,
ColNum,
case ColNum
when 1 then F1
when 2 then F2
when 3 then F3
when 4 then F4
when 5 then F5
end*1.0
from yourTable
cross join (
select 1 union all select 2 union all
select 3 union all select 4 union all select 5
) as F(ColNum)
) as T(ID,ColNum,Fval)
group by ID
go
Steve Kass
Drew University
KT wrote:
>The goal is to take the low and high values out of each record, then get th
e
>average of the remaining fields. For ex, ID1 has five values, remove 2 and
>100 then add (40+20+10)/3...then do this for each record. The number of
>values in the five fields can vary as shown below.
>
>
>I have a table that looks like this:
>
>ID F1 F2 F3 F4 F5 field names
>1 100 40 20 2 10 Values
>2 4 140 10 42
>3 10 189 22
>
>4 20 10 24 332 3
>
>
>Thanks in advance for this query.
>
>|||My solutions :) For the 2005 version, I like my solution for readability as
it is pretty straightforward. If you aren't using 2005, then Kass's is
really slick. Don't get me wrong, the person named mason's solution is
slicker than mine (and shorter) but I think that my solution is probably
more understandable later in the process. Either way, one of these'll do
you :)
Note that I use integer math, while Steve's uses floats. So my answers are
rounded off , while his arent
--create the table
create table looksLikeThis
(
id int primary key,
f1 int,
f2 int,
f3 int,
f4 int,
f5 int
)
insert into looksLikeThis
select 1, 100, 40, 20, 2, 10
union all
select 2, 4, 140, 10, 42, null
union all
select 3, 10, 189, 22,null, null
union all
select 4, 20, 10, 24, 332, 3
go
---
-- In 2005
---
--so much easier to do with the partition statement and the CTE. Allows the
first two
--views to be rolled up into one query pretty easy:
with shouldLookLikeThis as
(select *, row_number() over (partition by id order by value,uniqueifier )
as ordering
from (
select id, f1 as value, 1 as uniqueifier
from looksLikeThis
union all
select id, f2, 2
from looksLikeThis
union all
select id, f3, 3
from looksLikeThis
union all
select id, f4, 4
from looksLikeThis
union all
select id, f5, 5
from looksLikeThis ) as denorm
where value is not null)
select id, avg(value) as averageValue
from shouldLookLikeThis
where ordering not in (select max(ordering)
from shouldLookLikeThis s2
where s2.id = shouldLookLikeThis.id)
and ordering not in (select min(ordering)
from shouldLookLikeThis s2
where s2.id = shouldLookLikeThis.id)
group by id
-- Using 2000 and recent versions
--
--normalize the table, including some value to make sure of uniqueness (very
important to the query
--so you don't lose rows if the min or max have multiple values
create view shouldLookLikeThis
as
select *
from (
select id, f1 as value, 1 as uniqueifier
from looksLikeThis
union all
select id, f2, 2
from looksLikeThis
union all
select id, f3, 3
from looksLikeThis
union all
select id, f4, 4
from looksLikeThis
union all
select id, f5, 5
from looksLikeThis ) as denorm
where value is not null
go
--this view adds an ordering to the output (this is what the uniqueifier was
about
create view includeOrder
as
select id, value, (select count(*)
from shouldLookLikeThis s2
where shouldLookLikeThis.id = s2.id
and (shouldLookLikeThis.value <= s2.value
or (shouldLookLikeThis.value = s2.value
and shouldLookLikeThis.uniqueifier <=
s2.uniqueifier))
) as ordering
from shouldLookLikeThis
GO
--then just exclude the min and max orderwise
select id, avg(value) as averageValue
from includeOrder
where ordering not in (select max(ordering)
from includeOrder s2
where s2.id = includeOrder.id)
and ordering not in (select min(ordering)
from includeOrder s2
where s2.id = includeOrder.id)
group by id
go
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"KT" <ktdev@.hotmail.com> wrote in message
news:%23RBOH3BPGHA.456@.TK2MSFTNGP15.phx.gbl...
> The goal is to take the low and high values out of each record, then get
> the
> average of the remaining fields. For ex, ID1 has five values, remove 2
> and
> 100 then add (40+20+10)/3...then do this for each record. The number of
> values in the five fields can vary as shown below.
>
>
> I have a table that looks like this:
>
> ID F1 F2 F3 F4 F5 field names
> 1 100 40 20 2 10 Values
> 2 4 140 10 42
> 3 10 189 22
>
> 4 20 10 24 332 3
>
>
> Thanks in advance for this query.
>|||I'm still learning MSSQL2005 features and the pivot/unpivot operators are an
interesting implementation. The idea is to transpose the rows into a column
so that we can use aggregate functions to calculate the average. If
"unpivot" is too unconventional, we can always use a case-based or a "union
all" derived table to achieve the same effect. I should add HAVING
COUNT(*)>2 to take care of the devide-by-0 situation.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:eLIh84CPGHA.420@.tk2msftngp13.phx.gbl...
> My solutions :) For the 2005 version, I like my solution for readability
> as it is pretty straightforward. If you aren't using 2005, then Kass's is
> really slick. Don't get me wrong, the person named mason's solution is
> slicker than mine (and shorter) but I think that my solution is probably
> more understandable later in the process. Either way, one of these'll do
> you :)
> Note that I use integer math, while Steve's uses floats. So my answers
> are rounded off , while his arent
> --create the table
> create table looksLikeThis
> (
> id int primary key,
> f1 int,
> f2 int,
> f3 int,
> f4 int,
> f5 int
> )
> insert into looksLikeThis
> select 1, 100, 40, 20, 2, 10
> union all
> select 2, 4, 140, 10, 42, null
> union all
> select 3, 10, 189, 22,null, null
> union all
> select 4, 20, 10, 24, 332, 3
> go
> ---
> -- In 2005
> ---
> --so much easier to do with the partition statement and the CTE. Allows
> the first two
> --views to be rolled up into one query pretty easy:
> with shouldLookLikeThis as
> (select *, row_number() over (partition by id order by value,uniqueifier )
> as ordering
> from (
> select id, f1 as value, 1 as uniqueifier
> from looksLikeThis
> union all
> select id, f2, 2
> from looksLikeThis
> union all
> select id, f3, 3
> from looksLikeThis
> union all
> select id, f4, 4
> from looksLikeThis
> union all
> select id, f5, 5
> from looksLikeThis ) as denorm
> where value is not null)
> select id, avg(value) as averageValue
> from shouldLookLikeThis
> where ordering not in (select max(ordering)
> from shouldLookLikeThis s2
> where s2.id = shouldLookLikeThis.id)
> and ordering not in (select min(ordering)
> from shouldLookLikeThis s2
> where s2.id = shouldLookLikeThis.id)
> group by id
> --
> -- Using 2000 and recent versions
> --
> --normalize the table, including some value to make sure of uniqueness
> (very important to the query
> --so you don't lose rows if the min or max have multiple values
> create view shouldLookLikeThis
> as
> select *
> from (
> select id, f1 as value, 1 as uniqueifier
> from looksLikeThis
> union all
> select id, f2, 2
> from looksLikeThis
> union all
> select id, f3, 3
> from looksLikeThis
> union all
> select id, f4, 4
> from looksLikeThis
> union all
> select id, f5, 5
> from looksLikeThis ) as denorm
> where value is not null
> go
> --this view adds an ordering to the output (this is what the uniqueifier
> was about
> create view includeOrder
> as
> select id, value, (select count(*)
> from shouldLookLikeThis s2
> where shouldLookLikeThis.id = s2.id
> and (shouldLookLikeThis.value <= s2.value
> or (shouldLookLikeThis.value = s2.value
> and shouldLookLikeThis.uniqueifier <=
> s2.uniqueifier))
> ) as ordering
> from shouldLookLikeThis
> GO
> --then just exclude the min and max orderwise
> select id, avg(value) as averageValue
> from includeOrder
> where ordering not in (select max(ordering)
> from includeOrder s2
> where s2.id = includeOrder.id)
> and ordering not in (select min(ordering)
> from includeOrder s2
> where s2.id = includeOrder.id)
> group by id
> go
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> "KT" <ktdev@.hotmail.com> wrote in message
> news:%23RBOH3BPGHA.456@.TK2MSFTNGP15.phx.gbl...|||No, the unpivot thing isn't what is unconventional. It is the:
sum(f) - max(f) - min(f))/(count(*) - 2.0)
part that makes the brain work harder than I cared to think about last
night. This is actually a more elagant solution too because it handles
duplicates easier.
> The idea is to transpose the rows into a column so that we can use
> aggregate functions to calculate the average
This is because SQL works well with rows, not vectors, particularly not of
variable length like this. This is part of what the Basically all we are
doing is rotating the set to be a SQL table in first normal form which then
makes it natural.
I know might go with mason's solution over mine, but that is so not easy to
admit :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"mason" <masonliu@.msn.com> wrote in message
news:et31s%23GPGHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm still learning MSSQL2005 features and the pivot/unpivot operators are
> an interesting implementation. The idea is to transpose the rows into a
> column so that we can use aggregate functions to calculate the average. If
> "unpivot" is too unconventional, we can always use a case-based or a
> "union all" derived table to achieve the same effect. I should add HAVING
> COUNT(*)>2 to take care of the devide-by-0 situation.
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:eLIh84CPGHA.420@.tk2msftngp13.phx.gbl...
>|||And 2.0 rather than 2 floats the whole thing. :p
In reality, it's often quantity over quality in people's eyes, and you are
right that readability may go with quantity in many cases. :p
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23$CSpDJPGHA.3360@.TK2MSFTNGP09.phx.gbl...
> No, the unpivot thing isn't what is unconventional. It is the:
> sum(f) - max(f) - min(f))/(count(*) - 2.0)
> part that makes the brain work harder than I cared to think about last
> night. This is actually a more elagant solution too because it handles
> duplicates easier.
>
> This is because SQL works well with rows, not vectors, particularly not of
> variable length like this. This is part of what the Basically all we are
> doing is rotating the set to be a SQL table in first normal form which
> then makes it natural.
> I know might go with mason's solution over mine, but that is so not easy
> to admit :)
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> "mason" <masonliu@.msn.com> wrote in message
> news:et31s%23GPGHA.3460@.TK2MSFTNGP15.phx.gbl...|||Until performance gets involved, it is almost always the case :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"mason" <masonliu@.msn.com> wrote in message
news:uJEVfUJPGHA.3144@.TK2MSFTNGP11.phx.gbl...
> And 2.0 rather than 2 floats the whole thing. :p
> In reality, it's often quantity over quality in people's eyes, and you are
> right that readability may go with quantity in many cases. :p
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:%23$CSpDJPGHA.3360@.TK2MSFTNGP09.phx.gbl...
>|||This one worked perfectly...could you elaborate on what it is actually
doing.
Thanks Steve.
"Steve Kass" <skass@.drew.edu> wrote in message
news:Oz3EseCPGHA.2704@.TK2MSFTNGP15.phx.gbl...
> See if this helps. I'm assuming the blank column values contain NULL.
> If they contain something else, you'll have to change F1 through F5 to
> CASE WHEN F1 = <whatever the blank is> THEN NULL ELSE F1 END,
> and so on.
> select
> ID, (SUM(Fval)-MAX(Fval)-MIN(Fval))/(COUNT(Fval)-2) as trimMean
> from (
> select
> ID,
> ColNum,
> case ColNum
> when 1 then F1
> when 2 then F2
> when 3 then F3
> when 4 then F4
> when 5 then F5
> end*1.0
> from yourTable
> cross join (
> select 1 union all select 2 union all
> select 3 union all select 4 union all select 5
> ) as F(ColNum)
> ) as T(ID,ColNum,Fval)
> group by ID
> go
> Steve Kass
> Drew University
> KT wrote:
>
the
and|||The cross join turns each single row such as
ID F1 F2 F3 F4 F5
--
101 13 24 35 46 NULL
into five separate rows like this:
ID, ColNum, Fval
--
101 1 13
101 2 24
101 3 35
101 4 46
101 5 NULL
Then it groups over ID values, finding the sum of
the Fval values, the number of those values that are
not blank (count(Fval)), and the largest and smallest
of the non-blank values. It gets the average you
need by adding the non-blank values, subtracting the
largest and smallest, and dividing by two less than
the number of non-blank values, all of this for each ID.
To understand it better, evaluate these queries separately
(there may be typos, but the idea is to look at it step by
step)
-- 1
select * from (
select 1 union all select 2 union all
select 3 union all select 4 union all select 5
) as F(ColNum)
-- 2
select * from (
select
ID,
ColNum,
case ColNum
when 1 then F1
when 2 then F2
when 3 then F3
when 4 then F4
when 5 then F5
end*1.0
from yourTable
cross join (
select 1 union all select 2 union all
select 3 union all select 4 union all select 5
) as F(ColNum)
) as T(ID,ColNum,Fval)
order by ID, ColNum, Fval
-- 3
select
ID
COUNT(Fval) as count_fval,
SUM(Fval) as sum_fval,
MAX(Fval) as max_fval,
MIN(Fval) as min_fval
from (
select
ID,
ColNum,
case ColNum
when 1 then F1
when 2 then F2
when 3 then F3
when 4 then F4
when 5 then F5
end*1.0
from yourTable
cross join (
select 1 union all select 2 union all
select 3 union all select 4 union all select 5
) as F(ColNum)
) as T(ID,ColNum,Fval)
group by ID
-SK
KT wrote:
>This one worked perfectly...could you elaborate on what it is actually
>doing.
>Thanks Steve.
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:Oz3EseCPGHA.2704@.TK2MSFTNGP15.phx.gbl...
>
>the
>
>and
>
>
>
average of the remaining fields. For ex, ID1 has five values, remove 2 and
100 then add (40+20+10)/3...then do this for each record. The number of
values in the five fields can vary as shown below.
I have a table that looks like this:
ID F1 F2 F3 F4 F5 field names
1 100 40 20 2 10 Values
2 4 140 10 42
3 10 189 22
4 20 10 24 332 3
Thanks in advance for this query.One way is to use the unpivot operator. It's tested in MSSQL2005.
select id, (sum(f) - max(f) - min(f))/(count(*) - 2.0) avg
from (select id, f from test unpivot (f for t in (f1,f2,f3,f4,f5)) p) t
group by id
"KT" <ktdev@.hotmail.com> wrote in message
news:%23RBOH3BPGHA.456@.TK2MSFTNGP15.phx.gbl...
> The goal is to take the low and high values out of each record, then get
> the
> average of the remaining fields. For ex, ID1 has five values, remove 2
> and
> 100 then add (40+20+10)/3...then do this for each record. The number of
> values in the five fields can vary as shown below.
>
>
> I have a table that looks like this:
>
> ID F1 F2 F3 F4 F5 field names
> 1 100 40 20 2 10 Values
> 2 4 140 10 42
> 3 10 189 22
>
> 4 20 10 24 332 3
>
>
> Thanks in advance for this query.|||See if this helps. I'm assuming the blank column values contain NULL.
If they contain something else, you'll have to change F1 through F5 to
CASE WHEN F1 = <whatever the blank is> THEN NULL ELSE F1 END,
and so on.
select
ID, (SUM(Fval)-MAX(Fval)-MIN(Fval))/(COUNT(Fval)-2) as trimMean
from (
select
ID,
ColNum,
case ColNum
when 1 then F1
when 2 then F2
when 3 then F3
when 4 then F4
when 5 then F5
end*1.0
from yourTable
cross join (
select 1 union all select 2 union all
select 3 union all select 4 union all select 5
) as F(ColNum)
) as T(ID,ColNum,Fval)
group by ID
go
Steve Kass
Drew University
KT wrote:
>The goal is to take the low and high values out of each record, then get th
e
>average of the remaining fields. For ex, ID1 has five values, remove 2 and
>100 then add (40+20+10)/3...then do this for each record. The number of
>values in the five fields can vary as shown below.
>
>
>I have a table that looks like this:
>
>ID F1 F2 F3 F4 F5 field names
>1 100 40 20 2 10 Values
>2 4 140 10 42
>3 10 189 22
>
>4 20 10 24 332 3
>
>
>Thanks in advance for this query.
>
>|||My solutions :) For the 2005 version, I like my solution for readability as
it is pretty straightforward. If you aren't using 2005, then Kass's is
really slick. Don't get me wrong, the person named mason's solution is
slicker than mine (and shorter) but I think that my solution is probably
more understandable later in the process. Either way, one of these'll do
you :)
Note that I use integer math, while Steve's uses floats. So my answers are
rounded off , while his arent
--create the table
create table looksLikeThis
(
id int primary key,
f1 int,
f2 int,
f3 int,
f4 int,
f5 int
)
insert into looksLikeThis
select 1, 100, 40, 20, 2, 10
union all
select 2, 4, 140, 10, 42, null
union all
select 3, 10, 189, 22,null, null
union all
select 4, 20, 10, 24, 332, 3
go
---
-- In 2005
---
--so much easier to do with the partition statement and the CTE. Allows the
first two
--views to be rolled up into one query pretty easy:
with shouldLookLikeThis as
(select *, row_number() over (partition by id order by value,uniqueifier )
as ordering
from (
select id, f1 as value, 1 as uniqueifier
from looksLikeThis
union all
select id, f2, 2
from looksLikeThis
union all
select id, f3, 3
from looksLikeThis
union all
select id, f4, 4
from looksLikeThis
union all
select id, f5, 5
from looksLikeThis ) as denorm
where value is not null)
select id, avg(value) as averageValue
from shouldLookLikeThis
where ordering not in (select max(ordering)
from shouldLookLikeThis s2
where s2.id = shouldLookLikeThis.id)
and ordering not in (select min(ordering)
from shouldLookLikeThis s2
where s2.id = shouldLookLikeThis.id)
group by id
-- Using 2000 and recent versions
--
--normalize the table, including some value to make sure of uniqueness (very
important to the query
--so you don't lose rows if the min or max have multiple values
create view shouldLookLikeThis
as
select *
from (
select id, f1 as value, 1 as uniqueifier
from looksLikeThis
union all
select id, f2, 2
from looksLikeThis
union all
select id, f3, 3
from looksLikeThis
union all
select id, f4, 4
from looksLikeThis
union all
select id, f5, 5
from looksLikeThis ) as denorm
where value is not null
go
--this view adds an ordering to the output (this is what the uniqueifier was
about
create view includeOrder
as
select id, value, (select count(*)
from shouldLookLikeThis s2
where shouldLookLikeThis.id = s2.id
and (shouldLookLikeThis.value <= s2.value
or (shouldLookLikeThis.value = s2.value
and shouldLookLikeThis.uniqueifier <=
s2.uniqueifier))
) as ordering
from shouldLookLikeThis
GO
--then just exclude the min and max orderwise
select id, avg(value) as averageValue
from includeOrder
where ordering not in (select max(ordering)
from includeOrder s2
where s2.id = includeOrder.id)
and ordering not in (select min(ordering)
from includeOrder s2
where s2.id = includeOrder.id)
group by id
go
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"KT" <ktdev@.hotmail.com> wrote in message
news:%23RBOH3BPGHA.456@.TK2MSFTNGP15.phx.gbl...
> The goal is to take the low and high values out of each record, then get
> the
> average of the remaining fields. For ex, ID1 has five values, remove 2
> and
> 100 then add (40+20+10)/3...then do this for each record. The number of
> values in the five fields can vary as shown below.
>
>
> I have a table that looks like this:
>
> ID F1 F2 F3 F4 F5 field names
> 1 100 40 20 2 10 Values
> 2 4 140 10 42
> 3 10 189 22
>
> 4 20 10 24 332 3
>
>
> Thanks in advance for this query.
>|||I'm still learning MSSQL2005 features and the pivot/unpivot operators are an
interesting implementation. The idea is to transpose the rows into a column
so that we can use aggregate functions to calculate the average. If
"unpivot" is too unconventional, we can always use a case-based or a "union
all" derived table to achieve the same effect. I should add HAVING
COUNT(*)>2 to take care of the devide-by-0 situation.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:eLIh84CPGHA.420@.tk2msftngp13.phx.gbl...
> My solutions :) For the 2005 version, I like my solution for readability
> as it is pretty straightforward. If you aren't using 2005, then Kass's is
> really slick. Don't get me wrong, the person named mason's solution is
> slicker than mine (and shorter) but I think that my solution is probably
> more understandable later in the process. Either way, one of these'll do
> you :)
> Note that I use integer math, while Steve's uses floats. So my answers
> are rounded off , while his arent
> --create the table
> create table looksLikeThis
> (
> id int primary key,
> f1 int,
> f2 int,
> f3 int,
> f4 int,
> f5 int
> )
> insert into looksLikeThis
> select 1, 100, 40, 20, 2, 10
> union all
> select 2, 4, 140, 10, 42, null
> union all
> select 3, 10, 189, 22,null, null
> union all
> select 4, 20, 10, 24, 332, 3
> go
> ---
> -- In 2005
> ---
> --so much easier to do with the partition statement and the CTE. Allows
> the first two
> --views to be rolled up into one query pretty easy:
> with shouldLookLikeThis as
> (select *, row_number() over (partition by id order by value,uniqueifier )
> as ordering
> from (
> select id, f1 as value, 1 as uniqueifier
> from looksLikeThis
> union all
> select id, f2, 2
> from looksLikeThis
> union all
> select id, f3, 3
> from looksLikeThis
> union all
> select id, f4, 4
> from looksLikeThis
> union all
> select id, f5, 5
> from looksLikeThis ) as denorm
> where value is not null)
> select id, avg(value) as averageValue
> from shouldLookLikeThis
> where ordering not in (select max(ordering)
> from shouldLookLikeThis s2
> where s2.id = shouldLookLikeThis.id)
> and ordering not in (select min(ordering)
> from shouldLookLikeThis s2
> where s2.id = shouldLookLikeThis.id)
> group by id
> --
> -- Using 2000 and recent versions
> --
> --normalize the table, including some value to make sure of uniqueness
> (very important to the query
> --so you don't lose rows if the min or max have multiple values
> create view shouldLookLikeThis
> as
> select *
> from (
> select id, f1 as value, 1 as uniqueifier
> from looksLikeThis
> union all
> select id, f2, 2
> from looksLikeThis
> union all
> select id, f3, 3
> from looksLikeThis
> union all
> select id, f4, 4
> from looksLikeThis
> union all
> select id, f5, 5
> from looksLikeThis ) as denorm
> where value is not null
> go
> --this view adds an ordering to the output (this is what the uniqueifier
> was about
> create view includeOrder
> as
> select id, value, (select count(*)
> from shouldLookLikeThis s2
> where shouldLookLikeThis.id = s2.id
> and (shouldLookLikeThis.value <= s2.value
> or (shouldLookLikeThis.value = s2.value
> and shouldLookLikeThis.uniqueifier <=
> s2.uniqueifier))
> ) as ordering
> from shouldLookLikeThis
> GO
> --then just exclude the min and max orderwise
> select id, avg(value) as averageValue
> from includeOrder
> where ordering not in (select max(ordering)
> from includeOrder s2
> where s2.id = includeOrder.id)
> and ordering not in (select min(ordering)
> from includeOrder s2
> where s2.id = includeOrder.id)
> group by id
> go
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> "KT" <ktdev@.hotmail.com> wrote in message
> news:%23RBOH3BPGHA.456@.TK2MSFTNGP15.phx.gbl...|||No, the unpivot thing isn't what is unconventional. It is the:
sum(f) - max(f) - min(f))/(count(*) - 2.0)
part that makes the brain work harder than I cared to think about last
night. This is actually a more elagant solution too because it handles
duplicates easier.
> The idea is to transpose the rows into a column so that we can use
> aggregate functions to calculate the average
This is because SQL works well with rows, not vectors, particularly not of
variable length like this. This is part of what the Basically all we are
doing is rotating the set to be a SQL table in first normal form which then
makes it natural.
I know might go with mason's solution over mine, but that is so not easy to
admit :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"mason" <masonliu@.msn.com> wrote in message
news:et31s%23GPGHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm still learning MSSQL2005 features and the pivot/unpivot operators are
> an interesting implementation. The idea is to transpose the rows into a
> column so that we can use aggregate functions to calculate the average. If
> "unpivot" is too unconventional, we can always use a case-based or a
> "union all" derived table to achieve the same effect. I should add HAVING
> COUNT(*)>2 to take care of the devide-by-0 situation.
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:eLIh84CPGHA.420@.tk2msftngp13.phx.gbl...
>|||And 2.0 rather than 2 floats the whole thing. :p
In reality, it's often quantity over quality in people's eyes, and you are
right that readability may go with quantity in many cases. :p
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23$CSpDJPGHA.3360@.TK2MSFTNGP09.phx.gbl...
> No, the unpivot thing isn't what is unconventional. It is the:
> sum(f) - max(f) - min(f))/(count(*) - 2.0)
> part that makes the brain work harder than I cared to think about last
> night. This is actually a more elagant solution too because it handles
> duplicates easier.
>
> This is because SQL works well with rows, not vectors, particularly not of
> variable length like this. This is part of what the Basically all we are
> doing is rotating the set to be a SQL table in first normal form which
> then makes it natural.
> I know might go with mason's solution over mine, but that is so not easy
> to admit :)
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> "mason" <masonliu@.msn.com> wrote in message
> news:et31s%23GPGHA.3460@.TK2MSFTNGP15.phx.gbl...|||Until performance gets involved, it is almost always the case :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"mason" <masonliu@.msn.com> wrote in message
news:uJEVfUJPGHA.3144@.TK2MSFTNGP11.phx.gbl...
> And 2.0 rather than 2 floats the whole thing. :p
> In reality, it's often quantity over quality in people's eyes, and you are
> right that readability may go with quantity in many cases. :p
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:%23$CSpDJPGHA.3360@.TK2MSFTNGP09.phx.gbl...
>|||This one worked perfectly...could you elaborate on what it is actually
doing.
Thanks Steve.
"Steve Kass" <skass@.drew.edu> wrote in message
news:Oz3EseCPGHA.2704@.TK2MSFTNGP15.phx.gbl...
> See if this helps. I'm assuming the blank column values contain NULL.
> If they contain something else, you'll have to change F1 through F5 to
> CASE WHEN F1 = <whatever the blank is> THEN NULL ELSE F1 END,
> and so on.
> select
> ID, (SUM(Fval)-MAX(Fval)-MIN(Fval))/(COUNT(Fval)-2) as trimMean
> from (
> select
> ID,
> ColNum,
> case ColNum
> when 1 then F1
> when 2 then F2
> when 3 then F3
> when 4 then F4
> when 5 then F5
> end*1.0
> from yourTable
> cross join (
> select 1 union all select 2 union all
> select 3 union all select 4 union all select 5
> ) as F(ColNum)
> ) as T(ID,ColNum,Fval)
> group by ID
> go
> Steve Kass
> Drew University
> KT wrote:
>
the
and|||The cross join turns each single row such as
ID F1 F2 F3 F4 F5
--
101 13 24 35 46 NULL
into five separate rows like this:
ID, ColNum, Fval
--
101 1 13
101 2 24
101 3 35
101 4 46
101 5 NULL
Then it groups over ID values, finding the sum of
the Fval values, the number of those values that are
not blank (count(Fval)), and the largest and smallest
of the non-blank values. It gets the average you
need by adding the non-blank values, subtracting the
largest and smallest, and dividing by two less than
the number of non-blank values, all of this for each ID.
To understand it better, evaluate these queries separately
(there may be typos, but the idea is to look at it step by
step)
-- 1
select * from (
select 1 union all select 2 union all
select 3 union all select 4 union all select 5
) as F(ColNum)
-- 2
select * from (
select
ID,
ColNum,
case ColNum
when 1 then F1
when 2 then F2
when 3 then F3
when 4 then F4
when 5 then F5
end*1.0
from yourTable
cross join (
select 1 union all select 2 union all
select 3 union all select 4 union all select 5
) as F(ColNum)
) as T(ID,ColNum,Fval)
order by ID, ColNum, Fval
-- 3
select
ID
COUNT(Fval) as count_fval,
SUM(Fval) as sum_fval,
MAX(Fval) as max_fval,
MIN(Fval) as min_fval
from (
select
ID,
ColNum,
case ColNum
when 1 then F1
when 2 then F2
when 3 then F3
when 4 then F4
when 5 then F5
end*1.0
from yourTable
cross join (
select 1 union all select 2 union all
select 3 union all select 4 union all select 5
) as F(ColNum)
) as T(ID,ColNum,Fval)
group by ID
-SK
KT wrote:
>This one worked perfectly...could you elaborate on what it is actually
>doing.
>Thanks Steve.
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:Oz3EseCPGHA.2704@.TK2MSFTNGP15.phx.gbl...
>
>the
>
>and
>
>
>
Subscribe to:
Posts (Atom)