I am currently storing:
ReportMonth As tinyint
ReportDay As tinyint
ReportYear As tinyint
in my table. Client software requires that I have a "smalldatetime" column, which contains the date. I'd like to base it off the current 3 columns I have (seeing as how I have months worth of data)
I tried the following formula, as well as variations thereof:
(convert(smalldatetime, ReportYear + "-" + ReportMonth + "-" + ReportYear))
What is the correct formula for performing the calculation? I can't seem to get it.
Thanks for your help!You'd rather try:
convert(smalldatetime, convert(nvarchar,@.ReportMonth) + N"-" + convert(nvarchar, @.ReportDay + N"-" + convert(nvarchar,@.ReportYear))
Originally posted by odinsdream
I am currently storing:
ReportMonth As tinyint
ReportDay As tinyint
ReportYear As tinyint
in my table. Client software requires that I have a "smalldatetime" column, which contains the date. I'd like to base it off the current 3 columns I have (seeing as how I have months worth of data)
I tried the following formula, as well as variations thereof:
(convert(smalldatetime, ReportYear + "-" + ReportMonth + "-" + ReportYear))
What is the correct formula for performing the calculation? I can't seem to get it.
Thanks for your help!
Showing posts with label client. Show all posts
Showing posts with label client. Show all posts
Thursday, March 22, 2012
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:
>
Friday, February 24, 2012
Complex Duplicate Row Issue (Need To Keep Shorter Value)
a client of mine caused a bit of an issue with one of our SQL Server
databases. They modified the database to create duplicate SKU values.
Each SKU value has a different Name column value.
What I need to do is delete the shorter value.
SKU Name
111 Glass Vase
111 Glass
333 Apple
so I need to get rid of SKU 111 w/ the Name value of Glass.
What I have done is created a view that give me a listing of all the
row values with duplicate SKUs, but I am not sure how to do the Len
comparison and return the shorter value.
Any help is appreciated.
Thanks!a query like:
select p.SKU, p.Name
from Products P
inner join (Select SKU, len(Name) as L from Products group by SKU) P2
on P.sku = p1.sku and len(p.Name) = p2.L
but this query keep duplicated values or names with the same length.
so you can do this:
select p.SKU, min(p.Name) as Name
from Products P
inner join (Select SKU, len(Name) as L from Products group by SKU) P2
on P.sku = p1.sku and len(p.Name) = p2.L
group by p.SKU
<carpeaqua@.gmail.com> wrote in message
news:1153932460.568433.293330@.s13g2000cwa.googlegroups.com...
>a client of mine caused a bit of an issue with one of our SQL Server
> databases. They modified the database to create duplicate SKU values.
> Each SKU value has a different Name column value.
> What I need to do is delete the shorter value.
> SKU Name
> 111 Glass Vase
> 111 Glass
> 333 Apple
> so I need to get rid of SKU 111 w/ the Name value of Glass.
> What I have done is created a view that give me a listing of all the
> row values with duplicate SKUs, but I am not sure how to do the Len
> comparison and return the shorter value.
> Any help is appreciated.
> Thanks!
>|||J=E9j=E9 wrote:
> select p.SKU, min(p.Name) as Name
> from Products P
> inner join (Select SKU, len(Name) as L from Products group by SKU) P2
> on P.sku =3D p1.sku and len(p.Name) =3D p2.L
> group by p.SKU
>
I had to modify it to be like this (it was giving a group by error)
select p.SKU, min(p.Name) as Name
from dbo.Product P
inner join (Select SKU, len(Name) as L from dbo.Product group by SKU,
Name) P2
on P.sku =3D p.sku and len(p.Name) =3D p2.L
group by p.SKU
It have let it run for about 7 minutes and its not outputting anything
as of yet. There are almost 7000 records in the table, so is that to
be expected? Apologies for my ignorance, but I am not used to working
with SQL Server.
Thanks!|||Seven minutes and 7000 rows, there is a problem.
Roy
On 26 Jul 2006 10:31:32 -0700, carpeaqua@.gmail.com wrote:
>Jéjé wrote:
>> select p.SKU, min(p.Name) as Name
>> from Products P
>> inner join (Select SKU, len(Name) as L from Products group by SKU) P2
>> on P.sku = p1.sku and len(p.Name) = p2.L
>> group by p.SKU
>I had to modify it to be like this (it was giving a group by error)
>select p.SKU, min(p.Name) as Name
>from dbo.Product P
>inner join (Select SKU, len(Name) as L from dbo.Product group by SKU,
>Name) P2
>on P.sku = p.sku and len(p.Name) = p2.L
>group by p.SKU
>It have let it run for about 7 minutes and its not outputting anything
>as of yet. There are almost 7000 records in the table, so is that to
>be expected? Apologies for my ignorance, but I am not used to working
>with SQL Server.
>Thanks!|||This is a multi-part message in MIME format.
--=_NextPart_000_0979_01C6B0A2.71C31A40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I believe that this idea will work for the problem as you have =described.
CREATE TABLE #MyTable
( RowID int IDENTITY
, SKU int
, Descript varchar(25)
)
SET NOCOUNT ON
INSERT INTO #MyTable VALUES ( 111, 'Glass Vase' )
INSERT INTO #MyTable VALUES ( 111, 'Glass' )
INSERT INTO #MyTable VALUES ( 333, 'Apple' )
DELETE #MyTable
WHERE Descript =3D ( SELECT min ( Descript )
FROM #MyTable
GROUP BY sku
HAVING count( SKU ) > 1
)
SELECT *
FROM #MyTable
DROP TABLE #MyTable
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
<carpeaqua@.gmail.com> wrote in message =news:1153932460.568433.293330@.s13g2000cwa.googlegroups.com...
>a client of mine caused a bit of an issue with one of our SQL Server
> databases. They modified the database to create duplicate SKU values.
> Each SKU value has a different Name column value.
> > What I need to do is delete the shorter value.
> > SKU Name
> 111 Glass Vase
> 111 Glass
> 333 Apple
> > so I need to get rid of SKU 111 w/ the Name value of Glass.
> > What I have done is created a view that give me a listing of all the
> row values with duplicate SKUs, but I am not sure how to do the Len
> comparison and return the shorter value.
> > Any help is appreciated.
> > Thanks!
>
--=_NextPart_000_0979_01C6B0A2.71C31A40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I believe that this idea will work for =the problem as you have described.
CREATE TABLE =#MyTable ( RowID =int IDENTITY , SKU int , Descript varchar(25) )
SET NOCOUNT ON
INSERT INTO #MyTable VALUES ( =111, 'Glass Vase' )INSERT INTO #MyTable VALUES ( 111, 'Glass' )INSERT INTO =#MyTable VALUES ( 333, 'Apple' ) DELETE #MyTableWHERE =Descript =3D ( SELECT min ( Descript ) &n=bsp; FROM #MyTable &=nbsp; GROUP BY sku = HAVING count( SKU ) > 1 &n=bsp; )
SELECT *FROM #MyTable
DROP TABLE =#MyTable
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
=wrote in message news:1153932460.568433.293330@.s13g2000cwa.googlegroups.com...>a =client of mine caused a bit of an issue with one of our SQL Server> =databases. They modified the database to create duplicate SKU values.> Each =SKU value has a different Name column value.> > What I need to =do is delete the shorter value.> > SKU Name> 111 Glass Vase> =111 Glass> 333 Apple> > so I =need to get rid of SKU 111 w/ the Name value of Glass.> > What I =have done is created a view that give me a listing of all the> row values =with duplicate SKUs, but I am not sure how to do the Len> comparison =and return the shorter value.> > Any help is =appreciated.> > Thanks!>
--=_NextPart_000_0979_01C6B0A2.71C31A40--|||Arnie, if two SKUs have the same description that is going to erase
some good data.
Roy
On Wed, 26 Jul 2006 10:58:36 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>I believe that this idea will work for the problem as you have described.
>CREATE TABLE #MyTable
> ( RowID int IDENTITY
> , SKU int
> , Descript varchar(25)
> )
>SET NOCOUNT ON
>INSERT INTO #MyTable VALUES ( 111, 'Glass Vase' )
>INSERT INTO #MyTable VALUES ( 111, 'Glass' )
>INSERT INTO #MyTable VALUES ( 333, 'Apple' )
>DELETE #MyTable
>WHERE Descript = ( SELECT min ( Descript )
> FROM #MyTable
> GROUP BY sku
> HAVING count( SKU ) > 1
> )
>SELECT *
>FROM #MyTable
>DROP TABLE #MyTable|||Yep, you are right. Drat, back to the drawing board...
(That wouldn't ever happen, would it?) ;-)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:7ebfc25dgml1a5dstv0a1retue37n3dcdu@.4ax.com...
> Arnie, if two SKUs have the same description that is going to erase
> some good data.
> Roy
> On Wed, 26 Jul 2006 10:58:36 -0700, "Arnie Rowland" <arnie@.1568.com>
> wrote:
>>I believe that this idea will work for the problem as you have described.
>>CREATE TABLE #MyTable
>> ( RowID int IDENTITY
>> , SKU int
>> , Descript varchar(25)
>> )
>>SET NOCOUNT ON
>>INSERT INTO #MyTable VALUES ( 111, 'Glass Vase' )
>>INSERT INTO #MyTable VALUES ( 111, 'Glass' )
>>INSERT INTO #MyTable VALUES ( 333, 'Apple' )
>>DELETE #MyTable
>>WHERE Descript = ( SELECT min ( Descript )
>> FROM #MyTable
>> GROUP BY sku
>> HAVING count( SKU ) > 1
>> )
>>SELECT *
>>FROM #MyTable
>>DROP TABLE #MyTable|||Here is my version of finding the rows you want to keep.
SELECT *
FROM Product as P
WHERE P.Name = (select min(S.Name) from Product as S
where P.SKU = S.SKU
and LEN(S.Name) = (select max(len(name))
from Product as X
where P.SKU = X.SKU))
Untested, of course, as is my version of the DELETE:
DELETE Product
WHERE EXISTS
(select * from Product as Z
where Product.SKU = Z.SKU
and Product.Name <> Z.Name
and LEN(Product.Name) < LEN(Z.Name))
Roy Harvey
Beacon Falls, CT|||Roy Harvey wrote:
> Here is my version of finding the rows you want to keep.
> SELECT *
> FROM Product as P
> WHERE P.Name => (select min(S.Name) from Product as S
> where P.SKU = S.SKU
> and LEN(S.Name) => (select max(len(name))
> from Product as X
> where P.SKU = X.SKU))
> Untested, of course, as is my version of the DELETE:
> DELETE Product
> WHERE EXISTS
> (select * from Product as Z
> where Product.SKU = Z.SKU
> and Product.Name <> Z.Name
> and LEN(Product.Name) < LEN(Z.Name))
This worked wonderfully. Thank you, my friend.|||Just to note that my two queries could involve different results. The
first one returned ONE row, even if the two rows had the same
(longest) length description. The DELETE only deleted rows with
shorter descriptions.
It would be worth double checking:
SELECT SKU, max(Name), min(Name), count(*)
FROM Product
GROUP BY SKU
HAVING COUNT(*) > 1
ORDER BY SKU, 2, 3
Roy
On 26 Jul 2006 11:28:48 -0700, carpeaqua@.gmail.com wrote:
>Roy Harvey wrote:
>> Here is my version of finding the rows you want to keep.
>> SELECT *
>> FROM Product as P
>> WHERE P.Name =>> (select min(S.Name) from Product as S
>> where P.SKU = S.SKU
>> and LEN(S.Name) =>> (select max(len(name))
>> from Product as X
>> where P.SKU = X.SKU))
>> Untested, of course, as is my version of the DELETE:
>> DELETE Product
>> WHERE EXISTS
>> (select * from Product as Z
>> where Product.SKU = Z.SKU
>> and Product.Name <> Z.Name
>> and LEN(Product.Name) < LEN(Z.Name))
>
>This worked wonderfully. Thank you, my friend.|||What would happen if there were 2 rows with the same SKU and the same length
[Name]?
Would they BOTH be left -or deleted, and without indication of an incomplete
update?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:qkdfc2t451agl5stchnp57po3gur2vj7bk@.4ax.com...
> Just to note that my two queries could involve different results. The
> first one returned ONE row, even if the two rows had the same
> (longest) length description. The DELETE only deleted rows with
> shorter descriptions.
> It would be worth double checking:
> SELECT SKU, max(Name), min(Name), count(*)
> FROM Product
> GROUP BY SKU
> HAVING COUNT(*) > 1
> ORDER BY SKU, 2, 3
> Roy
> On 26 Jul 2006 11:28:48 -0700, carpeaqua@.gmail.com wrote:
>>Roy Harvey wrote:
>> Here is my version of finding the rows you want to keep.
>> SELECT *
>> FROM Product as P
>> WHERE P.Name =>> (select min(S.Name) from Product as S
>> where P.SKU = S.SKU
>> and LEN(S.Name) =>> (select max(len(name))
>> from Product as X
>> where P.SKU = X.SKU))
>> Untested, of course, as is my version of the DELETE:
>> DELETE Product
>> WHERE EXISTS
>> (select * from Product as Z
>> where Product.SKU = Z.SKU
>> and Product.Name <> Z.Name
>> and LEN(Product.Name) < LEN(Z.Name))
>>
>>This worked wonderfully. Thank you, my friend.|||On Wed, 26 Jul 2006 11:47:07 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>What would happen if there were 2 rows with the same SKU and the same length
>[Name]?
>Would they BOTH be left -or deleted, and without indication of an incomplete
>update?
If they were both the longest they would both be left by the DELETE I
wrote, but only one of them would have appeared in the first query.
Roy
databases. They modified the database to create duplicate SKU values.
Each SKU value has a different Name column value.
What I need to do is delete the shorter value.
SKU Name
111 Glass Vase
111 Glass
333 Apple
so I need to get rid of SKU 111 w/ the Name value of Glass.
What I have done is created a view that give me a listing of all the
row values with duplicate SKUs, but I am not sure how to do the Len
comparison and return the shorter value.
Any help is appreciated.
Thanks!a query like:
select p.SKU, p.Name
from Products P
inner join (Select SKU, len(Name) as L from Products group by SKU) P2
on P.sku = p1.sku and len(p.Name) = p2.L
but this query keep duplicated values or names with the same length.
so you can do this:
select p.SKU, min(p.Name) as Name
from Products P
inner join (Select SKU, len(Name) as L from Products group by SKU) P2
on P.sku = p1.sku and len(p.Name) = p2.L
group by p.SKU
<carpeaqua@.gmail.com> wrote in message
news:1153932460.568433.293330@.s13g2000cwa.googlegroups.com...
>a client of mine caused a bit of an issue with one of our SQL Server
> databases. They modified the database to create duplicate SKU values.
> Each SKU value has a different Name column value.
> What I need to do is delete the shorter value.
> SKU Name
> 111 Glass Vase
> 111 Glass
> 333 Apple
> so I need to get rid of SKU 111 w/ the Name value of Glass.
> What I have done is created a view that give me a listing of all the
> row values with duplicate SKUs, but I am not sure how to do the Len
> comparison and return the shorter value.
> Any help is appreciated.
> Thanks!
>|||J=E9j=E9 wrote:
> select p.SKU, min(p.Name) as Name
> from Products P
> inner join (Select SKU, len(Name) as L from Products group by SKU) P2
> on P.sku =3D p1.sku and len(p.Name) =3D p2.L
> group by p.SKU
>
I had to modify it to be like this (it was giving a group by error)
select p.SKU, min(p.Name) as Name
from dbo.Product P
inner join (Select SKU, len(Name) as L from dbo.Product group by SKU,
Name) P2
on P.sku =3D p.sku and len(p.Name) =3D p2.L
group by p.SKU
It have let it run for about 7 minutes and its not outputting anything
as of yet. There are almost 7000 records in the table, so is that to
be expected? Apologies for my ignorance, but I am not used to working
with SQL Server.
Thanks!|||Seven minutes and 7000 rows, there is a problem.
Roy
On 26 Jul 2006 10:31:32 -0700, carpeaqua@.gmail.com wrote:
>Jéjé wrote:
>> select p.SKU, min(p.Name) as Name
>> from Products P
>> inner join (Select SKU, len(Name) as L from Products group by SKU) P2
>> on P.sku = p1.sku and len(p.Name) = p2.L
>> group by p.SKU
>I had to modify it to be like this (it was giving a group by error)
>select p.SKU, min(p.Name) as Name
>from dbo.Product P
>inner join (Select SKU, len(Name) as L from dbo.Product group by SKU,
>Name) P2
>on P.sku = p.sku and len(p.Name) = p2.L
>group by p.SKU
>It have let it run for about 7 minutes and its not outputting anything
>as of yet. There are almost 7000 records in the table, so is that to
>be expected? Apologies for my ignorance, but I am not used to working
>with SQL Server.
>Thanks!|||This is a multi-part message in MIME format.
--=_NextPart_000_0979_01C6B0A2.71C31A40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I believe that this idea will work for the problem as you have =described.
CREATE TABLE #MyTable
( RowID int IDENTITY
, SKU int
, Descript varchar(25)
)
SET NOCOUNT ON
INSERT INTO #MyTable VALUES ( 111, 'Glass Vase' )
INSERT INTO #MyTable VALUES ( 111, 'Glass' )
INSERT INTO #MyTable VALUES ( 333, 'Apple' )
DELETE #MyTable
WHERE Descript =3D ( SELECT min ( Descript )
FROM #MyTable
GROUP BY sku
HAVING count( SKU ) > 1
)
SELECT *
FROM #MyTable
DROP TABLE #MyTable
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
<carpeaqua@.gmail.com> wrote in message =news:1153932460.568433.293330@.s13g2000cwa.googlegroups.com...
>a client of mine caused a bit of an issue with one of our SQL Server
> databases. They modified the database to create duplicate SKU values.
> Each SKU value has a different Name column value.
> > What I need to do is delete the shorter value.
> > SKU Name
> 111 Glass Vase
> 111 Glass
> 333 Apple
> > so I need to get rid of SKU 111 w/ the Name value of Glass.
> > What I have done is created a view that give me a listing of all the
> row values with duplicate SKUs, but I am not sure how to do the Len
> comparison and return the shorter value.
> > Any help is appreciated.
> > Thanks!
>
--=_NextPart_000_0979_01C6B0A2.71C31A40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I believe that this idea will work for =the problem as you have described.
CREATE TABLE =#MyTable ( RowID =int IDENTITY , SKU int , Descript varchar(25) )
SET NOCOUNT ON
INSERT INTO #MyTable VALUES ( =111, 'Glass Vase' )INSERT INTO #MyTable VALUES ( 111, 'Glass' )INSERT INTO =#MyTable VALUES ( 333, 'Apple' ) DELETE #MyTableWHERE =Descript =3D ( SELECT min ( Descript ) &n=bsp; FROM #MyTable &=nbsp; GROUP BY sku = HAVING count( SKU ) > 1 &n=bsp; )
SELECT *FROM #MyTable
DROP TABLE =#MyTable
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
--=_NextPart_000_0979_01C6B0A2.71C31A40--|||Arnie, if two SKUs have the same description that is going to erase
some good data.
Roy
On Wed, 26 Jul 2006 10:58:36 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>I believe that this idea will work for the problem as you have described.
>CREATE TABLE #MyTable
> ( RowID int IDENTITY
> , SKU int
> , Descript varchar(25)
> )
>SET NOCOUNT ON
>INSERT INTO #MyTable VALUES ( 111, 'Glass Vase' )
>INSERT INTO #MyTable VALUES ( 111, 'Glass' )
>INSERT INTO #MyTable VALUES ( 333, 'Apple' )
>DELETE #MyTable
>WHERE Descript = ( SELECT min ( Descript )
> FROM #MyTable
> GROUP BY sku
> HAVING count( SKU ) > 1
> )
>SELECT *
>FROM #MyTable
>DROP TABLE #MyTable|||Yep, you are right. Drat, back to the drawing board...
(That wouldn't ever happen, would it?) ;-)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:7ebfc25dgml1a5dstv0a1retue37n3dcdu@.4ax.com...
> Arnie, if two SKUs have the same description that is going to erase
> some good data.
> Roy
> On Wed, 26 Jul 2006 10:58:36 -0700, "Arnie Rowland" <arnie@.1568.com>
> wrote:
>>I believe that this idea will work for the problem as you have described.
>>CREATE TABLE #MyTable
>> ( RowID int IDENTITY
>> , SKU int
>> , Descript varchar(25)
>> )
>>SET NOCOUNT ON
>>INSERT INTO #MyTable VALUES ( 111, 'Glass Vase' )
>>INSERT INTO #MyTable VALUES ( 111, 'Glass' )
>>INSERT INTO #MyTable VALUES ( 333, 'Apple' )
>>DELETE #MyTable
>>WHERE Descript = ( SELECT min ( Descript )
>> FROM #MyTable
>> GROUP BY sku
>> HAVING count( SKU ) > 1
>> )
>>SELECT *
>>FROM #MyTable
>>DROP TABLE #MyTable|||Here is my version of finding the rows you want to keep.
SELECT *
FROM Product as P
WHERE P.Name = (select min(S.Name) from Product as S
where P.SKU = S.SKU
and LEN(S.Name) = (select max(len(name))
from Product as X
where P.SKU = X.SKU))
Untested, of course, as is my version of the DELETE:
DELETE Product
WHERE EXISTS
(select * from Product as Z
where Product.SKU = Z.SKU
and Product.Name <> Z.Name
and LEN(Product.Name) < LEN(Z.Name))
Roy Harvey
Beacon Falls, CT|||Roy Harvey wrote:
> Here is my version of finding the rows you want to keep.
> SELECT *
> FROM Product as P
> WHERE P.Name => (select min(S.Name) from Product as S
> where P.SKU = S.SKU
> and LEN(S.Name) => (select max(len(name))
> from Product as X
> where P.SKU = X.SKU))
> Untested, of course, as is my version of the DELETE:
> DELETE Product
> WHERE EXISTS
> (select * from Product as Z
> where Product.SKU = Z.SKU
> and Product.Name <> Z.Name
> and LEN(Product.Name) < LEN(Z.Name))
This worked wonderfully. Thank you, my friend.|||Just to note that my two queries could involve different results. The
first one returned ONE row, even if the two rows had the same
(longest) length description. The DELETE only deleted rows with
shorter descriptions.
It would be worth double checking:
SELECT SKU, max(Name), min(Name), count(*)
FROM Product
GROUP BY SKU
HAVING COUNT(*) > 1
ORDER BY SKU, 2, 3
Roy
On 26 Jul 2006 11:28:48 -0700, carpeaqua@.gmail.com wrote:
>Roy Harvey wrote:
>> Here is my version of finding the rows you want to keep.
>> SELECT *
>> FROM Product as P
>> WHERE P.Name =>> (select min(S.Name) from Product as S
>> where P.SKU = S.SKU
>> and LEN(S.Name) =>> (select max(len(name))
>> from Product as X
>> where P.SKU = X.SKU))
>> Untested, of course, as is my version of the DELETE:
>> DELETE Product
>> WHERE EXISTS
>> (select * from Product as Z
>> where Product.SKU = Z.SKU
>> and Product.Name <> Z.Name
>> and LEN(Product.Name) < LEN(Z.Name))
>
>This worked wonderfully. Thank you, my friend.|||What would happen if there were 2 rows with the same SKU and the same length
[Name]?
Would they BOTH be left -or deleted, and without indication of an incomplete
update?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:qkdfc2t451agl5stchnp57po3gur2vj7bk@.4ax.com...
> Just to note that my two queries could involve different results. The
> first one returned ONE row, even if the two rows had the same
> (longest) length description. The DELETE only deleted rows with
> shorter descriptions.
> It would be worth double checking:
> SELECT SKU, max(Name), min(Name), count(*)
> FROM Product
> GROUP BY SKU
> HAVING COUNT(*) > 1
> ORDER BY SKU, 2, 3
> Roy
> On 26 Jul 2006 11:28:48 -0700, carpeaqua@.gmail.com wrote:
>>Roy Harvey wrote:
>> Here is my version of finding the rows you want to keep.
>> SELECT *
>> FROM Product as P
>> WHERE P.Name =>> (select min(S.Name) from Product as S
>> where P.SKU = S.SKU
>> and LEN(S.Name) =>> (select max(len(name))
>> from Product as X
>> where P.SKU = X.SKU))
>> Untested, of course, as is my version of the DELETE:
>> DELETE Product
>> WHERE EXISTS
>> (select * from Product as Z
>> where Product.SKU = Z.SKU
>> and Product.Name <> Z.Name
>> and LEN(Product.Name) < LEN(Z.Name))
>>
>>This worked wonderfully. Thank you, my friend.|||On Wed, 26 Jul 2006 11:47:07 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>What would happen if there were 2 rows with the same SKU and the same length
>[Name]?
>Would they BOTH be left -or deleted, and without indication of an incomplete
>update?
If they were both the longest they would both be left by the DELETE I
wrote, but only one of them would have appeared in the first query.
Roy
Complex Duplicate Row Issue (Need To Keep Shorter Value)
a client of mine caused a bit of an issue with one of our SQL Server
databases. They modified the database to create duplicate SKU values.
Each SKU value has a different Name column value.
What I need to do is delete the shorter value.
SKU Name
111 Glass Vase
111 Glass
333 Apple
so I need to get rid of SKU 111 w/ the Name value of Glass.
What I have done is created a view that give me a listing of all the
row values with duplicate SKUs, but I am not sure how to do the Len
comparison and return the shorter value.
Any help is appreciated.
Thanks!a query like:
select p.SKU, p.Name
from Products P
inner join (Select SKU, len(Name) as L from Products group by SKU) P2
on P.sku = p1.sku and len(p.Name) = p2.L
but this query keep duplicated values or names with the same length.
so you can do this:
select p.SKU, min(p.Name) as Name
from Products P
inner join (Select SKU, len(Name) as L from Products group by SKU) P2
on P.sku = p1.sku and len(p.Name) = p2.L
group by p.SKU
<carpeaqua@.gmail.com> wrote in message
news:1153932460.568433.293330@.s13g2000cwa.googlegroups.com...
>a client of mine caused a bit of an issue with one of our SQL Server
> databases. They modified the database to create duplicate SKU values.
> Each SKU value has a different Name column value.
> What I need to do is delete the shorter value.
> SKU Name
> 111 Glass Vase
> 111 Glass
> 333 Apple
> so I need to get rid of SKU 111 w/ the Name value of Glass.
> What I have done is created a view that give me a listing of all the
> row values with duplicate SKUs, but I am not sure how to do the Len
> comparison and return the shorter value.
> Any help is appreciated.
> Thanks!
>|||J=E9j=E9 wrote:
> select p.SKU, min(p.Name) as Name
> from Products P
> inner join (Select SKU, len(Name) as L from Products group by SKU) P2
> on P.sku =3D p1.sku and len(p.Name) =3D p2.L
> group by p.SKU
>
I had to modify it to be like this (it was giving a group by error)
select p.SKU, min(p.Name) as Name
from dbo.Product P
inner join (Select SKU, len(Name) as L from dbo.Product group by SKU,
Name) P2
on P.sku =3D p.sku and len(p.Name) =3D p2.L
group by p.SKU
It have let it run for about 7 minutes and its not outputting anything
as of yet. There are almost 7000 records in the table, so is that to
be expected? Apologies for my ignorance, but I am not used to working
with SQL Server. =20
Thanks!|||Seven minutes and 7000 rows, there is a problem.
Roy
On 26 Jul 2006 10:31:32 -0700, carpeaqua@.gmail.com wrote:
>Jj wrote:
>
>I had to modify it to be like this (it was giving a group by error)
>select p.SKU, min(p.Name) as Name
>from dbo.Product P
>inner join (Select SKU, len(Name) as L from dbo.Product group by SKU,
>Name) P2
>on P.sku = p.sku and len(p.Name) = p2.L
>group by p.SKU
>It have let it run for about 7 minutes and its not outputting anything
>as of yet. There are almost 7000 records in the table, so is that to
>be expected? Apologies for my ignorance, but I am not used to working
>with SQL Server.
>Thanks!|||I believe that this idea will work for the problem as you have described.
CREATE TABLE #MyTable
( RowID int IDENTITY
, SKU int
, Descript varchar(25)
)
SET NOCOUNT ON
INSERT INTO #MyTable VALUES ( 111, 'Glass Vase' )
INSERT INTO #MyTable VALUES ( 111, 'Glass' )
INSERT INTO #MyTable VALUES ( 333, 'Apple' )
DELETE #MyTable
WHERE Descript = ( SELECT min ( Descript )
FROM #MyTable
GROUP BY sku
HAVING count( SKU ) > 1
)
SELECT *
FROM #MyTable
DROP TABLE #MyTable
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<carpeaqua@.gmail.com> wrote in message news:1153932460.568433.293330@.s13g2000cwa.googlegroup
s.com...
>a client of mine caused a bit of an issue with one of our SQL Server
> databases. They modified the database to create duplicate SKU values.
> Each SKU value has a different Name column value.
>
> What I need to do is delete the shorter value.
>
> SKU Name
> 111 Glass Vase
> 111 Glass
> 333 Apple
>
> so I need to get rid of SKU 111 w/ the Name value of Glass.
>
> What I have done is created a view that give me a listing of all the
> row values with duplicate SKUs, but I am not sure how to do the Len
> comparison and return the shorter value.
>
> Any help is appreciated.
>
> Thanks!
>|||Yep, you are right. Drat, back to the drawing board...
(That wouldn't ever happen, would it?) ;-)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:7ebfc25dgml1a5dstv0a1retue37n3dcdu@.
4ax.com...[vbcol=seagreen]
> Arnie, if two SKUs have the same description that is going to erase
> some good data.
> Roy
> On Wed, 26 Jul 2006 10:58:36 -0700, "Arnie Rowland" <arnie@.1568.com>
> wrote:
>|||Here is my version of finding the rows you want to keep.
SELECT *
FROM Product as P
WHERE P.Name =
(select min(S.Name) from Product as S
where P.SKU = S.SKU
and LEN(S.Name) =
(select max(len(name))
from Product as X
where P.SKU = X.SKU))
Untested, of course, as is my version of the DELETE:
DELETE Product
WHERE EXISTS
(select * from Product as Z
where Product.SKU = Z.SKU
and Product.Name <> Z.Name
and LEN(Product.Name) < LEN(Z.Name))
Roy Harvey
Beacon Falls, CT|||Roy Harvey wrote:
> Here is my version of finding the rows you want to keep.
> SELECT *
> FROM Product as P
> WHERE P.Name =
> (select min(S.Name) from Product as S
> where P.SKU = S.SKU
> and LEN(S.Name) =
> (select max(len(name))
> from Product as X
> where P.SKU = X.SKU))
> Untested, of course, as is my version of the DELETE:
> DELETE Product
> WHERE EXISTS
> (select * from Product as Z
> where Product.SKU = Z.SKU
> and Product.Name <> Z.Name
> and LEN(Product.Name) < LEN(Z.Name))
This worked wonderfully. Thank you, my friend.|||Just to note that my two queries could involve different results. The
first one returned ONE row, even if the two rows had the same
(longest) length description. The DELETE only deleted rows with
shorter descriptions.
It would be worth double checking:
SELECT SKU, max(Name), min(Name), count(*)
FROM Product
GROUP BY SKU
HAVING COUNT(*) > 1
ORDER BY SKU, 2, 3
Roy
On 26 Jul 2006 11:28:48 -0700, carpeaqua@.gmail.com wrote:
>Roy Harvey wrote:
>
>This worked wonderfully. Thank you, my friend.|||What would happen if there were 2 rows with the same SKU and the same length
[Name]?
Would they BOTH be left -or deleted, and without indication of an incomplete
update?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:qkdfc2t451agl5stchnp57po3gur2vj7bk@.
4ax.com...[vbcol=seagreen]
> Just to note that my two queries could involve different results. The
> first one returned ONE row, even if the two rows had the same
> (longest) length description. The DELETE only deleted rows with
> shorter descriptions.
> It would be worth double checking:
> SELECT SKU, max(Name), min(Name), count(*)
> FROM Product
> GROUP BY SKU
> HAVING COUNT(*) > 1
> ORDER BY SKU, 2, 3
> Roy
> On 26 Jul 2006 11:28:48 -0700, carpeaqua@.gmail.com wrote:
>
databases. They modified the database to create duplicate SKU values.
Each SKU value has a different Name column value.
What I need to do is delete the shorter value.
SKU Name
111 Glass Vase
111 Glass
333 Apple
so I need to get rid of SKU 111 w/ the Name value of Glass.
What I have done is created a view that give me a listing of all the
row values with duplicate SKUs, but I am not sure how to do the Len
comparison and return the shorter value.
Any help is appreciated.
Thanks!a query like:
select p.SKU, p.Name
from Products P
inner join (Select SKU, len(Name) as L from Products group by SKU) P2
on P.sku = p1.sku and len(p.Name) = p2.L
but this query keep duplicated values or names with the same length.
so you can do this:
select p.SKU, min(p.Name) as Name
from Products P
inner join (Select SKU, len(Name) as L from Products group by SKU) P2
on P.sku = p1.sku and len(p.Name) = p2.L
group by p.SKU
<carpeaqua@.gmail.com> wrote in message
news:1153932460.568433.293330@.s13g2000cwa.googlegroups.com...
>a client of mine caused a bit of an issue with one of our SQL Server
> databases. They modified the database to create duplicate SKU values.
> Each SKU value has a different Name column value.
> What I need to do is delete the shorter value.
> SKU Name
> 111 Glass Vase
> 111 Glass
> 333 Apple
> so I need to get rid of SKU 111 w/ the Name value of Glass.
> What I have done is created a view that give me a listing of all the
> row values with duplicate SKUs, but I am not sure how to do the Len
> comparison and return the shorter value.
> Any help is appreciated.
> Thanks!
>|||J=E9j=E9 wrote:
> select p.SKU, min(p.Name) as Name
> from Products P
> inner join (Select SKU, len(Name) as L from Products group by SKU) P2
> on P.sku =3D p1.sku and len(p.Name) =3D p2.L
> group by p.SKU
>
I had to modify it to be like this (it was giving a group by error)
select p.SKU, min(p.Name) as Name
from dbo.Product P
inner join (Select SKU, len(Name) as L from dbo.Product group by SKU,
Name) P2
on P.sku =3D p.sku and len(p.Name) =3D p2.L
group by p.SKU
It have let it run for about 7 minutes and its not outputting anything
as of yet. There are almost 7000 records in the table, so is that to
be expected? Apologies for my ignorance, but I am not used to working
with SQL Server. =20
Thanks!|||Seven minutes and 7000 rows, there is a problem.
Roy
On 26 Jul 2006 10:31:32 -0700, carpeaqua@.gmail.com wrote:
>Jj wrote:
>
>I had to modify it to be like this (it was giving a group by error)
>select p.SKU, min(p.Name) as Name
>from dbo.Product P
>inner join (Select SKU, len(Name) as L from dbo.Product group by SKU,
>Name) P2
>on P.sku = p.sku and len(p.Name) = p2.L
>group by p.SKU
>It have let it run for about 7 minutes and its not outputting anything
>as of yet. There are almost 7000 records in the table, so is that to
>be expected? Apologies for my ignorance, but I am not used to working
>with SQL Server.
>Thanks!|||I believe that this idea will work for the problem as you have described.
CREATE TABLE #MyTable
( RowID int IDENTITY
, SKU int
, Descript varchar(25)
)
SET NOCOUNT ON
INSERT INTO #MyTable VALUES ( 111, 'Glass Vase' )
INSERT INTO #MyTable VALUES ( 111, 'Glass' )
INSERT INTO #MyTable VALUES ( 333, 'Apple' )
DELETE #MyTable
WHERE Descript = ( SELECT min ( Descript )
FROM #MyTable
GROUP BY sku
HAVING count( SKU ) > 1
)
SELECT *
FROM #MyTable
DROP TABLE #MyTable
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<carpeaqua@.gmail.com> wrote in message news:1153932460.568433.293330@.s13g2000cwa.googlegroup
s.com...
>a client of mine caused a bit of an issue with one of our SQL Server
> databases. They modified the database to create duplicate SKU values.
> Each SKU value has a different Name column value.
>
> What I need to do is delete the shorter value.
>
> SKU Name
> 111 Glass Vase
> 111 Glass
> 333 Apple
>
> so I need to get rid of SKU 111 w/ the Name value of Glass.
>
> What I have done is created a view that give me a listing of all the
> row values with duplicate SKUs, but I am not sure how to do the Len
> comparison and return the shorter value.
>
> Any help is appreciated.
>
> Thanks!
>|||Yep, you are right. Drat, back to the drawing board...
(That wouldn't ever happen, would it?) ;-)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:7ebfc25dgml1a5dstv0a1retue37n3dcdu@.
4ax.com...[vbcol=seagreen]
> Arnie, if two SKUs have the same description that is going to erase
> some good data.
> Roy
> On Wed, 26 Jul 2006 10:58:36 -0700, "Arnie Rowland" <arnie@.1568.com>
> wrote:
>|||Here is my version of finding the rows you want to keep.
SELECT *
FROM Product as P
WHERE P.Name =
(select min(S.Name) from Product as S
where P.SKU = S.SKU
and LEN(S.Name) =
(select max(len(name))
from Product as X
where P.SKU = X.SKU))
Untested, of course, as is my version of the DELETE:
DELETE Product
WHERE EXISTS
(select * from Product as Z
where Product.SKU = Z.SKU
and Product.Name <> Z.Name
and LEN(Product.Name) < LEN(Z.Name))
Roy Harvey
Beacon Falls, CT|||Roy Harvey wrote:
> Here is my version of finding the rows you want to keep.
> SELECT *
> FROM Product as P
> WHERE P.Name =
> (select min(S.Name) from Product as S
> where P.SKU = S.SKU
> and LEN(S.Name) =
> (select max(len(name))
> from Product as X
> where P.SKU = X.SKU))
> Untested, of course, as is my version of the DELETE:
> DELETE Product
> WHERE EXISTS
> (select * from Product as Z
> where Product.SKU = Z.SKU
> and Product.Name <> Z.Name
> and LEN(Product.Name) < LEN(Z.Name))
This worked wonderfully. Thank you, my friend.|||Just to note that my two queries could involve different results. The
first one returned ONE row, even if the two rows had the same
(longest) length description. The DELETE only deleted rows with
shorter descriptions.
It would be worth double checking:
SELECT SKU, max(Name), min(Name), count(*)
FROM Product
GROUP BY SKU
HAVING COUNT(*) > 1
ORDER BY SKU, 2, 3
Roy
On 26 Jul 2006 11:28:48 -0700, carpeaqua@.gmail.com wrote:
>Roy Harvey wrote:
>
>This worked wonderfully. Thank you, my friend.|||What would happen if there were 2 rows with the same SKU and the same length
[Name]?
Would they BOTH be left -or deleted, and without indication of an incomplete
update?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:qkdfc2t451agl5stchnp57po3gur2vj7bk@.
4ax.com...[vbcol=seagreen]
> Just to note that my two queries could involve different results. The
> first one returned ONE row, even if the two rows had the same
> (longest) length description. The DELETE only deleted rows with
> shorter descriptions.
> It would be worth double checking:
> SELECT SKU, max(Name), min(Name), count(*)
> FROM Product
> GROUP BY SKU
> HAVING COUNT(*) > 1
> ORDER BY SKU, 2, 3
> Roy
> On 26 Jul 2006 11:28:48 -0700, carpeaqua@.gmail.com wrote:
>
Sunday, February 19, 2012
COMPLETE NEWBIE - sorry :-(
I have mysql running on a windows2003 webserver - it runs a
messageboard and oscommerce. Potential new client wants to replicate
information from the website to her server - is this possible with
mysql or do I need to install sql (proper) ... sorry if I sound like I
don't know fully what I am doing - i dont!!
Any advice or pointers given would be greatly appreciated.
thanks
Jeff
Jeff/Andrew,
this is a MS SQL Server Replication newsgroup - surely there are dedicated
MySQL discussion boards?
Rgds,
Paul Ibison
|||On Sun, 24 Apr 2005 19:07:16 +0100, "Paul Ibison"
<Paul.Ibison@.Pygmalion.Com> wrote:
>Jeff/Andrew,
>this is a MS SQL Server Replication newsgroup - surely there are dedicated
>MySQL discussion boards?
>Rgds,
>Paul Ibison
>
I can't find one and I suspect I will end up installing SQL for
replication - can someone please advise?
|||Have a look at http://forums.mysql.com/list.php?26
From the point of view of SQL Server Replication, you can certainly achieve
your aim. For the new client you need to consider latency (time between
change on the publisher and corresponding change arriving at the subscriber)
, autonomy (can they be offline) and if the subscriber is read-only or
read-write. Answering these will lead to the correct type of replication.
Having SQL Server on the webserver would mean this is the publisher.
Heterogenous subscribers are supported to differing extents, but
connectivity to MYSQL is possible and is illustrated in Hilary's book below.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||while I'd love you to buy my book
1) its covered on the free downloadable sample chapter
http://www.nwsu.com/lowres_replication_ch02.pdf
2) Replication to MySQL doesn't work - or works until the distribution agent
stops and then you have to resync to get it working again due to a SQL
Server bug.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23CtbxJQSFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Have a look at http://forums.mysql.com/list.php?26
> From the point of view of SQL Server Replication, you can certainly
> achieve your aim. For the new client you need to consider latency (time
> between change on the publisher and corresponding change arriving at the
> subscriber) , autonomy (can they be offline) and if the subscriber is
> read-only or read-write. Answering these will lead to the correct type of
> replication. Having SQL Server on the webserver would mean this is the
> publisher. Heterogenous subscribers are supported to differing extents,
> but connectivity to MYSQL is possible and is illustrated in Hilary's book
> below.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Interesting - thanks for the clarification. In that case your (Andrew's)
client might want to consider MSDE as a replacement for a MySQL subscriber.
Rgds,
Paul Ibison
messageboard and oscommerce. Potential new client wants to replicate
information from the website to her server - is this possible with
mysql or do I need to install sql (proper) ... sorry if I sound like I
don't know fully what I am doing - i dont!!
Any advice or pointers given would be greatly appreciated.
thanks
Jeff
Jeff/Andrew,
this is a MS SQL Server Replication newsgroup - surely there are dedicated
MySQL discussion boards?
Rgds,
Paul Ibison
|||On Sun, 24 Apr 2005 19:07:16 +0100, "Paul Ibison"
<Paul.Ibison@.Pygmalion.Com> wrote:
>Jeff/Andrew,
>this is a MS SQL Server Replication newsgroup - surely there are dedicated
>MySQL discussion boards?
>Rgds,
>Paul Ibison
>
I can't find one and I suspect I will end up installing SQL for
replication - can someone please advise?
|||Have a look at http://forums.mysql.com/list.php?26
From the point of view of SQL Server Replication, you can certainly achieve
your aim. For the new client you need to consider latency (time between
change on the publisher and corresponding change arriving at the subscriber)
, autonomy (can they be offline) and if the subscriber is read-only or
read-write. Answering these will lead to the correct type of replication.
Having SQL Server on the webserver would mean this is the publisher.
Heterogenous subscribers are supported to differing extents, but
connectivity to MYSQL is possible and is illustrated in Hilary's book below.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||while I'd love you to buy my book
1) its covered on the free downloadable sample chapter
http://www.nwsu.com/lowres_replication_ch02.pdf
2) Replication to MySQL doesn't work - or works until the distribution agent
stops and then you have to resync to get it working again due to a SQL
Server bug.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23CtbxJQSFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Have a look at http://forums.mysql.com/list.php?26
> From the point of view of SQL Server Replication, you can certainly
> achieve your aim. For the new client you need to consider latency (time
> between change on the publisher and corresponding change arriving at the
> subscriber) , autonomy (can they be offline) and if the subscriber is
> read-only or read-write. Answering these will lead to the correct type of
> replication. Having SQL Server on the webserver would mean this is the
> publisher. Heterogenous subscribers are supported to differing extents,
> but connectivity to MYSQL is possible and is illustrated in Hilary's book
> below.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Interesting - thanks for the clarification. In that case your (Andrew's)
client might want to consider MSDE as a replacement for a MySQL subscriber.
Rgds,
Paul Ibison
Tuesday, February 14, 2012
Compatibility of ODBC 2000 vs ODBC 2003
Will there be a compatibility issue between my client ODBC
version 2000.80.194 and the SQL server 2003.I assume you are referring to SQL Server 2005. There is no SQL Server 2003.
The client version you are referring to is the one that shipped with SQL
2000. The SQL 2000 clients will work fine against a SQL 2005 server, but
they wont be able to access all of the new features.
Brannon
"chanjo" <anonymous@.discussions.microsoft.com> wrote in message
news:181f01c48c62$c7433290$a501280a@.phx.gbl...
> Will there be a compatibility issue between my client ODBC
> version 2000.80.194 and the SQL server 2003.
version 2000.80.194 and the SQL server 2003.I assume you are referring to SQL Server 2005. There is no SQL Server 2003.
The client version you are referring to is the one that shipped with SQL
2000. The SQL 2000 clients will work fine against a SQL 2005 server, but
they wont be able to access all of the new features.
Brannon
"chanjo" <anonymous@.discussions.microsoft.com> wrote in message
news:181f01c48c62$c7433290$a501280a@.phx.gbl...
> Will there be a compatibility issue between my client ODBC
> version 2000.80.194 and the SQL server 2003.
Compatibility of ODBC 2000 vs ODBC 2003
Will there be a compatibility issue between my client ODBC
version 2000.80.194 and the SQL server 2003.
I assume you are referring to SQL Server 2005. There is no SQL Server 2003.
The client version you are referring to is the one that shipped with SQL
2000. The SQL 2000 clients will work fine against a SQL 2005 server, but
they wont be able to access all of the new features.
Brannon
"chanjo" <anonymous@.discussions.microsoft.com> wrote in message
news:181f01c48c62$c7433290$a501280a@.phx.gbl...
> Will there be a compatibility issue between my client ODBC
> version 2000.80.194 and the SQL server 2003.
version 2000.80.194 and the SQL server 2003.
I assume you are referring to SQL Server 2005. There is no SQL Server 2003.
The client version you are referring to is the one that shipped with SQL
2000. The SQL 2000 clients will work fine against a SQL 2005 server, but
they wont be able to access all of the new features.
Brannon
"chanjo" <anonymous@.discussions.microsoft.com> wrote in message
news:181f01c48c62$c7433290$a501280a@.phx.gbl...
> Will there be a compatibility issue between my client ODBC
> version 2000.80.194 and the SQL server 2003.
Friday, February 10, 2012
Comparioson Reports
Hi There
My client has asked that there existing SQL reports be used for Comparison Reports.
So for example at the moment - they Select a Company site and a department to create a report.
Now they want to have have a another site on the report - like a multipage report in MS Access reports.
Any ideas folks?Reporting services (currently) does not support a multi-select parameter.. I
suspect that will be coming soon..
In the mean time, you may make a parameter which is a text field, and
require users to type in a (comma) delimited list which you use in a select
statement..
If there is a MAX of 2 sites on a report, you might create 2 parameters and
allow a single select on each... This feels a little kludgy, but maybe your
users would accept it..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ryan Johnson" <RyanJohnson@.discussions.microsoft.com> wrote in message
news:CE707855-1795-42CC-83BA-CB308DD0820C@.microsoft.com...
> Hi There
> My client has asked that there existing SQL reports be used for Comparison
Reports.
> So for example at the moment - they Select a Company site and a department
to create a report.
> Now they want to have have a another site on the report - like a multipage
report in MS Access reports.
> Any ideas folks?
My client has asked that there existing SQL reports be used for Comparison Reports.
So for example at the moment - they Select a Company site and a department to create a report.
Now they want to have have a another site on the report - like a multipage report in MS Access reports.
Any ideas folks?Reporting services (currently) does not support a multi-select parameter.. I
suspect that will be coming soon..
In the mean time, you may make a parameter which is a text field, and
require users to type in a (comma) delimited list which you use in a select
statement..
If there is a MAX of 2 sites on a report, you might create 2 parameters and
allow a single select on each... This feels a little kludgy, but maybe your
users would accept it..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ryan Johnson" <RyanJohnson@.discussions.microsoft.com> wrote in message
news:CE707855-1795-42CC-83BA-CB308DD0820C@.microsoft.com...
> Hi There
> My client has asked that there existing SQL reports be used for Comparison
Reports.
> So for example at the moment - they Select a Company site and a department
to create a report.
> Now they want to have have a another site on the report - like a multipage
report in MS Access reports.
> Any ideas folks?
Subscribe to:
Posts (Atom)