Share via

Descrepancy between SalesLT.SalesOrderDetail and SalesLT.SalesOrderHeader?

Jasper Versteegh 0 Reputation points
2025-12-10T14:31:19.0566667+00:00

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?

SQL Server | SQL Server Transact-SQL
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jasper Versteegh 0 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?

    0 comments
  2. Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator
    2025-12-10T21:54:48.7833333+00:00

    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.)

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.