Dale Massicotte
asked on
Display only acct records with the most recent date
I have a query that produces all unique ACCT rows for each effective date.
But I am having difficulty modifying the query so that it shows only ONE row for each account that contains the most recent effective date.
Maybe somebody has time to take a shot at it?
But I am having difficulty modifying the query so that it shows only ONE row for each account that contains the most recent effective date.
Maybe somebody has time to take a shot at it?
SELECT TOP (999999) dbo.Customers.AcctNo, dbo.CustomersRates.Effective, dbo.Customers.Letter, dbo.Customers.BillingContact, dbo.Customers.Title,
dbo.Customers.ContactPerson, dbo.Customers.BillCompanyName, dbo.Customers.BillStreetAddress, dbo.Customers.BillCity, dbo.Customers.BillState,
dbo.Customers.BillZip, dbo.Customers.CompanyName, dbo.Customers.Active, dbo.Customers.StreetAddress, dbo.Customers.City, dbo.Customers.State,
dbo.Customers.Zip, dbo.Customers.PhoneNo, dbo.Customers.PhoneWaybillAr, dbo.Customers.PhoneWaybillNo, dbo.Customers.PhoneNo2, dbo.Customers.Ext,
dbo.Customers.FaxNo, dbo.Customers.Attention, dbo.Customers.BillingRefName, dbo.Customers.RefMask, dbo.Customers.Rep, dbo.CustomersRates.MinCa,
dbo.CustomersRates.MinCaN, dbo.CustomersRates.MinVa, dbo.CustomersRates.MinVaN, dbo.CustomersRates.Min14, dbo.CustomersRates.Min14N,
dbo.CustomersRates.Min20, dbo.CustomersRates.Min20N, dbo.CustomersRates.BaseCharge, dbo.CustomersRates.BaseChargeN,
dbo.CustomersRates.BaseChargeVa, dbo.CustomersRates.BaseChargeVaN, dbo.CustomersRates.BaseCharge14, dbo.CustomersRates.BaseCharge14N,
dbo.CustomersRates.BaseCharge20, dbo.CustomersRates.BaseCharge20N, dbo.CustomersRates.PerMileRate, dbo.CustomersRates.PerMileRateN,
dbo.CustomersRates.PerMileRateVa, dbo.CustomersRates.PerMileRateVaN, dbo.CustomersRates.PerMileRate14, dbo.CustomersRates.PerMileRate14N,
dbo.CustomersRates.PerMileRate20, dbo.CustomersRates.PerMileRate20N, dbo.CustomersRates.WeightCharge, dbo.CustomersRates.WeightChargeN,
dbo.CustomersRates.WeightChargeVa, dbo.CustomersRates.WeightChargeVaN, dbo.CustomersRates.WeightCharge14, dbo.CustomersRates.WeightCharge14N,
dbo.CustomersRates.WeightCharge20, dbo.CustomersRates.WeightCharge20N, dbo.CustomersRates.WaitingCharge, dbo.CustomersRates.WaitingChargeN,
dbo.CustomersRates.PrivCharge, dbo.CustomersRates.PrivChargeN, dbo.CustomersRates.BadAddressCharge, dbo.CustomersRates.BadAddressChargeN,
dbo.CustomersRates.MultipleR, dbo.CustomersRates.MultipleRN, dbo.CustomersRates.MultipleRVa, dbo.CustomersRates.MultipleRVaN,
dbo.CustomersRates.MultipleR14, dbo.CustomersRates.MultipleR14N, dbo.CustomersRates.MultipleR20, dbo.CustomersRates.MultipleR20N,
dbo.CustomersRates.MultipleD, dbo.CustomersRates.MultipleDN, dbo.CustomersRates.MultipleDVa, dbo.CustomersRates.MultipleDVaN,
dbo.CustomersRates.MultipleD14, dbo.CustomersRates.MultipleD14N, dbo.CustomersRates.MultipleD20, dbo.CustomersRates.MultipleD20N,
dbo.CustomersRates.MultipleS, dbo.CustomersRates.MultipleSN, dbo.CustomersRates.MultipleSVa, dbo.CustomersRates.MultipleSVaN,
dbo.CustomersRates.MultipleS14, dbo.CustomersRates.MultipleS14N, dbo.CustomersRates.MultipleS20, dbo.CustomersRates.MultipleS20N,
dbo.CustomersRates.Floor, dbo.CustomersRates.FloorN, dbo.CustomersRates.FloorVa, dbo.CustomersRates.FloorVaN, dbo.CustomersRates.Floor14,
dbo.CustomersRates.Floor14N, dbo.CustomersRates.Floor20, dbo.CustomersRates.Floor20N, dbo.CustomersRates.InsDelChg1, dbo.CustomersRates.InsDelChg2,
dbo.CustomersRates.Dist, dbo.CustomersRates.DistN, dbo.CustomersRates.DistB, dbo.CustomersRates.DistNB, dbo.CustomersRates.DistC,
dbo.CustomersRates.DistNC, dbo.CustomersRates.DistDrv, dbo.CustomersRates.DistDrvN, dbo.CustomersRates.DistDrvB, dbo.CustomersRates.DistDrvNB,
dbo.CustomersRates.DistDrvC, dbo.CustomersRates.DistDrvNC, dbo.CustomersRates.DistWeight, dbo.CustomersRates.DistWeightDrv, dbo.CustomersRates.ID,
dbo.CustomersRates.PriceLevel, dbo.CustomersRates.MinMi, dbo.CustomersRates.MinMiN, dbo.CustomersRates.BaseChargeMi,
dbo.CustomersRates.BaseChargeMiN, dbo.CustomersRates.PerMileRateMi, dbo.CustomersRates.PerMileRateMiN, dbo.CustomersRates.WeightChargeMi,
dbo.CustomersRates.WeightChargeMiN, dbo.CustomersRates.MultipleRMi, dbo.CustomersRates.MultipleRMiN, dbo.CustomersRates.MultipleDMi,
dbo.CustomersRates.MultipleDMiN, dbo.CustomersRates.MultipleSMi, dbo.CustomersRates.MultipleSMiN, dbo.CustomersRates.FloorMi,
dbo.CustomersRates.FloorMiN, dbo.CustomersRates.DistFreeWeight, dbo.CustomersRates.SkidCharge, dbo.CustomersRates.Dist2A, dbo.CustomersRates.Dist2AN,
dbo.CustomersRates.Dist2B, dbo.CustomersRates.Dist2BN, dbo.CustomersRates.Dist2C, dbo.CustomersRates.Dist2CN, dbo.CustomersRates.Dist2D,
dbo.CustomersRates.Dist2DN, dbo.CustomersRates.Dist2E, dbo.CustomersRates.Dist2EN, dbo.CustomersRates.Dist2DrvA, dbo.CustomersRates.Dist2DrvAN,
dbo.CustomersRates.Dist2DrvB, dbo.CustomersRates.Dist2DrvBN, dbo.CustomersRates.Dist2DrvC, dbo.CustomersRates.Dist2DrvCN,
dbo.CustomersRates.Dist2DrvD, dbo.CustomersRates.Dist2DrvDN, dbo.CustomersRates.Dist2DrvE, dbo.CustomersRates.Dist2DrvEN,
dbo.CustomersRates.Dist2Weight, dbo.CustomersRates.Dist2WeightDrv, dbo.CustomersRates.Dist2FreeWeight, dbo.CustomersRates.FreeWeight,
dbo.CustomersRates.DayStart, dbo.CustomersRates.DayEnd, dbo.CustomersRates.PickHold, dbo.CustomersRates.PickHoldN, dbo.CustomersRates.FreeMiles,
dbo.CustomersRates.chkTollsDisabled, dbo.CustomersRates.chkParkingDisabled, dbo.CustomersRates.chkResDisabled, dbo.CustomersRates.chkForceMileage,
dbo.CustomersRates.chkTollsNC, dbo.CustomersRates.chkParkingNC, dbo.CustomersRates.chkResNC, dbo.CustomersRates.SecondCtr,
dbo.CustomersRates.SecondCtrN, dbo.CustomersRates.SecondAWB, dbo.CustomersRates.SecondAWBN, dbo.CustomersRates.OutboundChargeNEaAdd,
dbo.CustomersRates.OutboundChargeN, dbo.CustomersRates.OutboundChargeEaAdd, dbo.CustomersRates.OutboundCharge,
dbo.CustomersRates.InboundChargeNEaAdd, dbo.CustomersRates.InboundChargeN, dbo.CustomersRates.InboundChargeEaAdd,
dbo.CustomersRates.InboundCharge, dbo.CustomersRates.nfoPerMileRate, dbo.CustomersRates.nfoAfterHours, dbo.CustomersRates.nfoWeightCharge,
dbo.CustomersRates.nfoFreeMiles, dbo.CustomersRates.nfoFreeWeight, dbo.CustomersRates.nfoAdditionalBox
FROM dbo.Customers INNER JOIN
dbo.CustomersRates ON dbo.Customers.AcctNo = dbo.CustomersRates.AcctNo
ORDER BY dbo.Customers.AcctNo, dbo.CustomersRates.Effective DESC
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
A question
I copied the query into SQL Mgt Studio to test and it worked. But will not allow me to save.
Does that make any sense?
ADO error ;WITH CTE
I copied the query into SQL Mgt Studio to test and it worked. But will not allow me to save.
Does that make any sense?
ADO error ;WITH CTE
I suspect you are using the (very flawed) designer. Instead use the query window to save your VIEW
Do you want to create a view with the query?
Also, you can remove the TOP in SELECT clause.
Also, you can remove the TOP in SELECT clause.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I use CTRL + N to open a new query window in Mgt Studio.
I removed the TOP 99999 from select
I also no longer need to sort by EFFECTIVE as there is only one EFFECTIVE per ACCT with this new query. Maybe I do not understand something...
I want to save this as a view in my database.
When I try I get
Incorrect syntax near ';'.
I removed the TOP 99999 from select
I also no longer need to sort by EFFECTIVE as there is only one EFFECTIVE per ACCT with this new query. Maybe I do not understand something...
I want to save this as a view in my database.
When I try I get
Incorrect syntax near ';'.
What is the compatibility level of the database?
I suspect if it were 90 or greater is should be fine.
If it is in fact 90 or greater then post the code in its entirety here.
I suspect if it were 90 or greater is should be fine.
If it is in fact 90 or greater then post the code in its entirety here.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When upgrading from SQL2000 to 2008 my queries did not run the query correctly 'TOP 100 PERCENT' so I found that using a real number made it work - however I know that it is not the best way to do things :)