News and Announcements icon

Dynamics 365 CommunityForums Microsoft Dynamics RMS (Archived) SQL query for sales do...

Microsoft Dynamics RMS (Archived)

SQL query for sales does not match Daily Sales by Register Report

(0) Share

Report

Report

Posted on by Hat Mann

i wrote the following query against my Headquarters database to determine sales:

SELECT     StoreID, RegisterID, Sales
FROM         Batch
WHERE     (OpeningTime > CONVERT(DATETIME, '2014-05-05 12:00:00', 102)) AND (OpeningTime < CONVERT(DATETIME, '2014-05-06 11:59:59', 102))
ORDER BY StoreID,RegisterID

If I run a Daily Sales by Register Report in HQ the sales in the Today column don't match the sales from the Batch table. Does anyone know why these sales figures don't match up?

*This post is locked for comments

All responses Img

All responses (2)

Answers Img

Answers (0)

Suggested answer

Hat Mann on at

Like (0)

Report

RE: SQL query for sales does not match Daily Sales by Register Report

Thanks for the reply CHAZH345. You were correct, I needed to add the SUM of the Returns table as it is a negative number. The sales figures now match.

SELECT(SUM(Sales))as "Sales",(SUM(Returns))as "Returns", (SUM(Sales)+ SUM(Returns)) as "Total Sales"

FROM Batch

WHERE (OpeningTime > CONVERT(DATETIME, '2014-05-05 12:00:00', 102)) AND (OpeningTime < CONVERT(DATETIME, '2014-05-06 11:59:59', 102))
Community Member Microsoft Employee on at

Like (0)

Report

RE: SQL query for sales does not match Daily Sales by Register Report

If you are comparing Transaction and TransactionEntry to Batch Sales total, it will not add up until you subtract Voucher item sales and returns from the SUM of the line items.

Helpful resources

Quick Links

Leaderboard

#1

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2

Martin Dráb 230,214 Most Valuable Professional

#3

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans