Descrepancy between SalesLT.SalesOrderDetail and SalesLT.SalesOrderHeader?

Question

Descrepancy between SalesLT.SalesOrderDetail and SalesLT.SalesOrderHeader?

Jasper Versteegh 45

I deployed a new AdventureWorks LT. When I query:

select * from SalesLT.SalesOrderDetail where salesorderID = 71774

there's 2 rows: SalesOrderDetailID's 110562 and 110563. They both have a linetotal of 356.898000 so when I query:

select sum(LineTotal) from SalesLT.SalesOrderDetail where salesOrderID = 71774

the result equals 713.796000.

However, on the headers:

select * from SalesLT.SalesOrderHeader where salesorderID = 71774

The subtotal is 880.3484. 880.3484 + TaxAmt 70.4279 + Freight 22.0087 = TotalDue 972.785.

But why is 880.3484 not equal to the 713.796000? There's no Tax on the lines, there's no discount, there's no deductions. How was this designed in the demodb?

Jasper Versteegh 45 Reputation points

2025-12-10T15:00:54.3933333+00:00
This is broken on the Azure version on https://portal.azure.com/#view/SqlAzureExtension/CreateDatabaseBladeV3/ and on https://github.com/microsoft/sql-server-samples/blob/adventureworks2016/samples/databases/adventure-works/2012-oltp-lt/SalesOrderHeader.csv.

Data source Start with a blank database, restore from a backup or select sample data to populate your new database. Use existing data

Sample AdventureWorksLT will be created as the sample database.

However, on https://github.com/microsoft/sql-server-samples/blob/adventureworks2016/samples/databases/adventure-works/2014-oltp/SalesOrderHeader.csv is corrected to 713.796 + 70.4279 + 22.0087 + 806.2326. Also, dates are in 2014 instead of 2008. So is this a discrepancy between the 2014 and 2012 version, or between the lt and oltp version?

Answer accepted by question author

0 additional answers

Your answer

Jasper Versteegh 45 Reputation points

2025-12-10T15:00:54.3933333+00:00

This is broken on the Azure version on https://portal.azure.com/#view/SqlAzureExtension/CreateDatabaseBladeV3/ and on https://github.com/microsoft/sql-server-samples/blob/adventureworks2016/samples/databases/adventure-works/2012-oltp-lt/SalesOrderHeader.csv.

Data source Start with a blank database, restore from a backup or select sample data to populate your new database. Use existing data

Sample AdventureWorksLT will be created as the sample database.

However, on https://github.com/microsoft/sql-server-samples/blob/adventureworks2016/samples/databases/adventure-works/2014-oltp/SalesOrderHeader.csv is corrected to 713.796 + 70.4279 + 22.0087 + 806.2326. Also, dates are in 2014 instead of 2008. So is this a discrepancy between the 2014 and 2012 version, or between the lt and oltp version?

Answer 1

Erland Sommarskog 134.1K MVP Volunteer Moderator

I don't have that particular flavour of AdventureWorks, and the AdventureWorks databases I have, sum of LineTotal appears to match SubTotal for all orders, at least within the currency unit in question.

There is a moral here of database design: The column SubTotal is redundant, and with redundant columns there is always risk that you get inconsistencies due to bugs, because someone forgot to update the header table. (And here I have a production scenario in mind, not a demo database, where the reason for the inconsistency may be sheer sloppiness.)

Lakshmi Narayana Garikapati 1,315 Reputation points Microsoft External Staff Moderator

2025-12-15T11:16:56.55+00:00

Hi**Jasper Versteegh,**

I'm following up to check if the response provided below was helpful.

Please let us know if your issue has been resolved. If you still have any questions or need further assistance, don't hesitate to get in touch we're happy to continue supporting you.

We appreciate your participation and thank you for being an active member of the community.

Best regards,

Lakshmi
Jasper Versteegh 45 Reputation points

2025-12-15T14:27:30.7633333+00:00

@Erland Sommarskog it appears the problem is indeed in AdventureWorksLT. In the full version of AdventureWorks, the header row total is displayed correctly. The TSQLV4 version doesn't contain this redundant column, so the problem doesn't occur there. We can therefore conclude that this is a bug in AdventureWorksLT 2012-oltp-lt, which was fixed in the 2014-oltp version.

Share via

Descrepancy between SalesLT.SalesOrderDetail and SalesLT.SalesOrderHeader?

0 additional answers

Your answer