November 22, 2007 at 10:05 am
I'm having trouble getting some summary cost details from joined tables in a linked server. I have a vehicle stock table, a vehicle specification table with associated estimated and actual costs for each vehicle option, and an option type table which lets me know if each option is a recoverable or a non-recoverable cost. I want to query my vehicle stock table to retrieve some stock information such as the registration plate, the retail price, etc. and a summary of the estimated and actual costs. This query works fine for just the non-recoverable costs (option type is 'N'):
SELECT UVS.FullChassis,
UVS.SellingPrice,
UVS.RegistrationNumber,
Sum(VSn.ActualCost) AS 'NR Sum of ActualCost',
Sum(VSn.EstimatedCost) AS 'NR Sum of EstimatedCost'
FROM DD_L1_OptionTypes OTn,
VS_LC_UsedVehicleStock UVS,
VS_LC_VehicleSpecification VSn
WHERE VSn.OptionType = OTn.OptionType
AND UVS.StockbookNumber = VSn.VehicleQuoteNumber
AND OTn.RecoverableNRC='N'
AND UVS.StockbookNumber=500291
GROUP BY UVS.StockbookNumber
How can I also include the recoverable costs (option type is 'R')? It's at this point that I must confess that this is not strictly speaking T-SQL. I'm posting it here, in the hope that some of you guys know more than T-SQL and can give me a way to do this. Otherwise, if you can control your anger, point me to a more suitable forum. I couldn't find one anywhere out there. At the end of the day, I *am* actually running this as part of a T-SQL stored procedure in SQL Server 2005. It's just that through an ODBC linked server, it's handing the above SQL off to another less compliant data source (an ISAM one if you must know).
Anyway enough grovelling; here is my closest attempt:
SELECT UVS.FullChassis,
UVS.SellingPrice,
UVS.RegistrationNumber,
Sum(VSn.ActualCost) AS 'NR Sum of ActualCost',
Sum(VSn.EstimatedCost) AS 'NR Sum of EstimatedCost',
Sum(VSr.ActualCost) AS 'R Sum of ActualCost',
Sum(VSr.EstimatedCost) AS 'R Sum of EstimatedCost'
FROM VS_LC_UsedVehicleStock UVS,
VS_LC_VehicleSpecification VSn,
VS_LC_VehicleSpecification VSr,
DD_L1_OptionTypes OTn,
DD_L1_OptionTypes OTr
WHERE VSn.VehicleQuoteNumber = UVS.StockbookNumber
AND VSr.VehicleQuoteNumber = UVS.StockbookNumber
AND OTn.OptionType = VSn.OptionType
AND OTr.OptionType = VSr.OptionType
AND (OTn.RecoverableNRC = 'N'
OR OTn.RecoverableNRC IS NULL)
AND (OTr.RecoverableNRC = 'R'
OR OTR.RecoverableNRC IS NULL)
GROUP BY UVS.StockbookNumber
But this causes duplication of rows so that each VSn record is repeated for every VSr record and vice versa, so the sums don't add up properly. Any ideas?
November 27, 2007 at 3:47 pm
Sample data and expected results would allow me to get a better understanding of what you are trying to accomplish :D. I would start with joining the tables in the from clause with inner joins and not in the where clause.
SELECT UVS.FullChassis,
UVS.SellingPrice,
UVS.RegistrationNumber,
Sum(VSn.ActualCost) AS 'NR Sum of ActualCost',
Sum(VSn.EstimatedCost) AS 'NR Sum of EstimatedCost',
Sum(VSr.ActualCost) AS 'R Sum of ActualCost',
Sum(VSr.EstimatedCost) AS 'R Sum of EstimatedCost'
FROM VS_LC_UsedVehicleStock UVS
INNER JOIN VS_LC_VehicleSpecification VSn ON
VSn.VehicleQuoteNumber = UVS.StockbookNumber
INNER JOIN VS_LC_VehicleSpecification VSr ON
VSr.VehicleQuoteNumber = UVS.StockbookNumber
INNER JOIN DD_L1_OptionTypes OTn ON
OTn.OptionType = VSn.OptionType
INNER JOIN DD_L1_OptionTypes OTr ON
OTr.OptionType = VSr.OptionType
WHERE
(OTn.RecoverableNRC = 'N' OR OTn.RecoverableNRC IS NULL) AND
(OTr.RecoverableNRC = 'R' OR OTR.RecoverableNRC IS NULL)
GROUP BY UVS.StockbookNumber
November 28, 2007 at 3:05 am
Adam is spot on, but you only need to join your recoverable costs table once:
[font="Courier New"]SELECT UVS.StockbookNumber,
UVS.FullChassis,
UVS.SellingPrice,
UVS.RegistrationNumber,
Sum(CASE OTn.RecoverableNRC WHEN 'N' THEN VSn.ActualCost ELSE 0 END) AS 'NR Sum of ActualCost',
Sum(CASE OTn.RecoverableNRC WHEN 'N' THEN VSn.EstimatedCost ELSE 0 END) AS 'NR Sum of EstimatedCost',
Sum(CASE OTn.RecoverableNRC WHEN 'R' THEN VSn.ActualCost ELSE 0 END) AS 'R Sum of ActualCost',
Sum(CASE OTn.RecoverableNRC WHEN 'R' THEN VSn.EstimatedCost ELSE 0 END) AS 'R Sum of EstimatedCost'
FROM VS_LC_UsedVehicleStock UVS
INNER JOIN VS_LC_VehicleSpecification VSn
ON VSn.VehicleQuoteNumber = UVS.StockbookNumber
INNER JOIN DD_L1_OptionTypes OTn
ON OTn.OptionType = VSn.OptionType
WHERE OTn.RecoverableNRC IN ('N', 'R')
GROUP BY UVS.StockbookNumber, UVS.FullChassis, UVS.SellingPrice, UVS.RegistrationNumber[/font]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 28, 2007 at 3:31 am
Thanks Adam and Chris for your replies. Both look very helpful and I have been trying to get them to work. I especially like the CASE statements suggested by Chris. Unfortunately, this bit of SQL is sent off through an ODBC linked server to an external datasource which doesn't do T-SQL, so it complains about the use of "CASE".
The steering group for this project, have now further complicated the matter by asking that in cases where there is an estimated *and* an actual cost, they want the larger of the two to be used towards calculating the relevant non-recoverable or recoverable cost total. As a result of this further complication and the above poor SQL compatibility, I am now considering fetching a load of costs into a local temp table. From here, I can manipulate the calculations more easily and update relevant records in the vehicle detail tables. Unless anyone can come up with a clever solution that does it in one. I am writing some scripts to create some test data at the moment and will post these when done.
November 28, 2007 at 4:46 am
OK here are some scripts that create tables similar to those I am using:
CREATE TABLE OptionTypes (OptionType char(1), Description nvarchar(50), RecoverableNRC char(1))
INSERT INTO OptionTypes VALUES('B', 'Bonus schemes', 'N')
INSERT INTO OptionTypes VALUES('C', 'Colours', 'R')
INSERT INTO OptionTypes VALUES('D', 'Dealer fitted options', 'R')
INSERT INTO OptionTypes VALUES('F', 'Factory fitted options', 'R')
INSERT INTO OptionTypes VALUES('I', 'Insurance', 'R')
INSERT INTO OptionTypes VALUES('N', 'Non-recoverable costs', 'N')
INSERT INTO OptionTypes VALUES('P', 'Packs', 'R')
INSERT INTO OptionTypes VALUES('T', 'Trims', 'R')
INSERT INTO OptionTypes VALUES('W', 'Extended warranty options', 'R')
CREATE TABLE VehicleSpecification (Sequence int, VehicleQuoteNumber int, EstimatedCost money, ActualCost money, OptionType char(1))
INSERT INTO VehicleSpecification VALUES(1, 500250, 0.00, 0.00, '')
INSERT INTO VehicleSpecification VALUES(2, 500250, 26307.57, 26307.57, '')
INSERT INTO VehicleSpecification VALUES(3, 500250, -336.17, -336.17, 'B')
INSERT INTO VehicleSpecification VALUES(4, 500250, 396.16, 396.16, 'C')
INSERT INTO VehicleSpecification VALUES(5, 500250, 57.30, 57.30, 'D')
INSERT INTO VehicleSpecification VALUES(6, 500250, 350.00, 300.00, 'D')
INSERT INTO VehicleSpecification VALUES(7, 500250, 487.17, 0.00, 'D')
INSERT INTO VehicleSpecification VALUES(8, 500250, 2800.00, 300.00, 'D')
INSERT INTO VehicleSpecification VALUES(9, 500250, 0.00, 0.00, 'D')
INSERT INTO VehicleSpecification VALUES(10, 500250, 80.00, 80.00, 'I')
INSERT INTO VehicleSpecification VALUES(11, 500250, 85.11, 81.00, 'N')
INSERT INTO VehicleSpecification VALUES(12, 500250, 25.00, 0.00, 'N')
INSERT INTO VehicleSpecification VALUES(1, 500260, 0.00, 0.00, '')
INSERT INTO VehicleSpecification VALUES(2, 500260, 24312.50, 24312.50, '')
INSERT INTO VehicleSpecification VALUES(3, 500260, -1500.00, -1500.00, 'B')
INSERT INTO VehicleSpecification VALUES(4, 500260, 123.23, 234.56, 'C')
INSERT INTO VehicleSpecification VALUES(5, 500260, 48.00, 37.30, 'D')
INSERT INTO VehicleSpecification VALUES(6, 500260, 400.00, 450.00, 'D')
INSERT INTO VehicleSpecification VALUES(7, 500260, 517.17, 0.00, 'D')
INSERT INTO VehicleSpecification VALUES(8, 500260, 200.00, 300.00, 'D')
INSERT INTO VehicleSpecification VALUES(9, 500260, 0.00, 0.00, 'D')
INSERT INTO VehicleSpecification VALUES(10, 500260, 85.00, 81.00, 'I')
INSERT INTO VehicleSpecification VALUES(11, 500260, 85.00, 85.00, 'N')
INSERT INTO VehicleSpecification VALUES(12, 500260, 20.00, 10.00, 'N')
CREATE TABLE UsedVehicleStock (StockbookNumber int, FullChassis char(17), SellingPrice money, RegistrationNumber nvarchar(15))
INSERT INTO UsedVehicleStock VALUES(500250, 'JTHBK262005025439', 20400.00, 'RE56LXT')
INSERT INTO UsedVehicleStock VALUES(500260, 'SALFA27BX7H008475', 43313.61, 'RF57NWK')
When using Chris's example above I get the correct results for the problem as originally stated, but only because I am working within SQL Server 2005 and so able to use T-SQL. So "nice one" Chris. Before getting hung up on making this work on my linked server, however, I need to change the specification due to a "goal-post move" from management. As stated above, now they want costs summed so that the larger cost (estimated vs. actual) is used for each item. If I run a query to get the costs for a particular vehicle like this:
SELECT vs.EstimatedCost, vs.ActualCost
FROM VehicleSpecification vs
JOIN OptionTypes ot
ON vs.OptionType = ot.OptionType
WHERE vs.VehicleQuoteNumber = 500260
AND ot.RecoverableNRC = 'N'
I get the following results:
Vehicle 500250 Recoverable:
EstimatedCost ActualCost
~~~~~~~~~~~~~~~~
396.16 396.16
57.30 57.30
350.00 300.00
487.17 0.00
2800.00 300.00
0.00 0.00
80.00 80.00
Vehicle 500250 Non-Recoverable:
EstimatedCost ActualCost
~~~~~~~~~~~~~~~~
-336.17 -336.17
85.11 81.00
25.00 0.00
Vehicle 500260 Recoverable:
EstimatedCost ActualCost
~~~~~~~~~~~~~~~~
123.23 234.56
48.00 37.30
400.00 450.00
517.17 0.00
200.00 300.00
0.00 0.00
85.00 81.00
Vehicle 500260 Non-Recoverable:
EstimatedCost ActualCost
~~~~~~~~~~~~~~~~
-1500.00 -1500.00
85.00 85.00
20.00 10.00
So using the new requirements, I am hoping to get the following results:
Vehicle 500250 recoverable costs = 4170.63
Vehicle 500250 non-recoverable costs = -226.06
Vehicle 500260 recoverable costs = 1634.73
Vehicle 500260 non-recoverable costs = -1395
My current line of attack is to create a stored procedure which gets the vehicle details (chassis no, reg plate, selling price) and populates a local table Table1. Then within this stored proc I call another stored proc to get the cost details for all the vehicles within a dealership. I then put the result of this stored procedure in a temp table #tempTable, which I use to calculate the costs from and then update the records of my local table Table1. I repeat this for each dealership, dropping and creating the temp table #tempTable for each one. Can anyone suggest a more direct method? Or any tips to keep the performance up?
November 28, 2007 at 5:33 am
What's your linked server, Edmund?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 28, 2007 at 5:38 am
It's an ODBC link to an ISAM database using a 3rd party driver.
November 28, 2007 at 5:43 am
Do you know any of the details? Some older db's e.g. xbase clones had implementations of SQL which worked perfectly well using IIF() instead of CASE;
SUM(IIF(today = 'wednesday', 1, 0))
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 28, 2007 at 5:59 am
It's a proprietal database orginally built by a company called Kerridge, now owned by a company called ADP. They wrote the ODBC driver to allow very basic queries to be made. They don't seem inclined to update this at the moment and it's beyond my knowledge and our licence agreement to write such a thing. I've tried a few variations of "IF" clauses and now, thanks to your suggestion, "IIF" clauses, but just keep getting syntax errors, which are inconclusive. But thanks for trying.
November 28, 2007 at 8:22 am
Edmund,
I have modified your query to meet your expected results. You should note that one of your expected results is incorrect. For 500260 Recoverable you are expecting 1634.73; however, in the sample data you provided the summed results are: Estimated: 1373.40 and Actual: 1102.86 which would make 1373.40 the returned record.
SELECT vs.VehicleQuoteNumber, ot.RecoverableNRC,
CASE WHEN SUM(vs.EstimatedCost) > SUM(vs.ActualCost) THEN
SUM(vs.EstimatedCost)
ELSE SUM(vs.ActualCost)
END
FROM VehicleSpecification vs
INNER JOIN OptionTypes ot ON vs.OptionType = ot.OptionType
WHERE ot.RecoverableNRC = 'R' OR ot.RecoverableNRC = 'N'
GROUP BY vs.VehicleQuoteNumber, ot.RecoverableNRC
ORDER BY vs.VehicleQuoteNumber ASC, ot.RecoverableNRC DESC
November 29, 2007 at 7:48 am
Thanks for more time on this, Adam. I think the reason your method is getting the wrong answer is that you are choosing the larger value of the summed actual versus the summed estimated costs for each vehicle, whereas what I am looking for is the larger value for each item. Sorry if I didn't make this clear enough. Anyway this code *does* produce the correct answers (it's very similar). It just won't work through my ODBC link, so I'll have to get the data into a temp table within SQL Server first before doing this:
SELECT
vs.VehicleQuoteNumber,
ot.RecoverableNRC,
SUM(
CASE WHEN vs.ActualCost > vs.EstimatedCost THEN
vs.ActualCost
ELSE
vs.EstimatedCost
END) AS Cost
FROM VehicleSpecification vs
INNER JOIN OptionTypes ot ON vs.OptionType = ot.OptionType
GROUP BY vs.VehicleQuoteNumber, ot.RecoverableNRC
ORDER BY vs.VehicleQuoteNumber, ot.RecoverableNRC
November 29, 2007 at 10:09 pm
Can you put this into a stored procedure and do a remote procedure call? Or can the ODBC not process CASE TSQL statements within stored procedures?
November 30, 2007 at 1:42 am
Unfortunately, I can't create remote stored procedures and the brand of SQL that the ODBC driver supports doesn't recognise CASE statements 🙁
November 30, 2007 at 1:53 pm
Does it support any other form of "if then logic", just curious? You could do this with a temp table, but then we wouldn't learn anything 🙂
December 3, 2007 at 1:58 am
I've tried various attempts at "if then" or "IIF" clauses but the ODBC driver doesn't seem to like any of them. Unfortunately, it's a bit like stumbling around in the dark, because there's no documentation or authoritative source of information to consult. You just have to plug away with different attempts to work out what it will and will not accept.
In the meantime, I've got what I want from a temp table populated by the following dynamic SQL statement for a passed @dealerid:
SELECT @sql = 'SELECT ' + cast(@dealerid as varchar(20)) + ' AS DealershipID,
dms.StockbookNumber,
SUM(
CASE WHEN dms.ActualCost > dms.EstimatedCost AND dms.RecoverableNRC = ''N'' THEN
dms.ActualCost
WHEN dms.ActualCost <= dms.EstimatedCost AND dms.RecoverableNRC = ''N'' THEN
dms.EstimatedCost
END) AS PreparationCosts,
SUM(
CASE WHEN dms.ActualCost > dms.EstimatedCost AND dms.RecoverableNRC = ''R'' THEN
dms.ActualCost
WHEN dms.ActualCost <= dms.EstimatedCost AND dms.RecoverableNRC = ''R'' THEN
dms.EstimatedCost
END) AS RecoverableCosts
FROM
OPENQUERY(' + @dmsDb + ', ''SELECT
UVS.StockbookNumber,
VS.ActualCost,
VS.EstimatedCost,
OT.RecoverableNRC
FROM
' + @dmsOTTable + ' OT,
' + @dmsUVSTable + ' UVS,
' + @dmsVSTable + ' VS
WHERE
VS.OptionType = OT.OptionType
AND
UVS.StockbookNumber = VS.VehicleQuoteNumber
AND
(OT.RecoverableNRC = ''''N'''' OR OT.RecoverableNRC = ''''R'''')
'') dms
GROUP BY dms.StockbookNumber
ORDER BY dms.StockbookNumber'
The value of @dealerid allows me to select the correct values for @dmsDb, @dmsOTTable, @dmsUVSTable and @dmsVSTable from some lookup tables. The two problems with this method are:
1. It's slow to execute
2. As Adam points out, we haven't learnt anything 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply