You’re offline. This is a read only version of the page.
I must use SQL coalesce statement, to make sure I detect the non NULL values of each of the common dimensions I join on.
if I am saying that expected sales which is also known as sales amount in the item ledger entry table which contains the budget for an Item in a Month is given by the Sales value in budget ... are we adjusting that budget value downwards, as we achieve real sales? Suppose at the beginning of a month, we haven't achieved any real sales yet, but we have a certain value of sales in the budget - so our actual revenue is 100% from expected. Half way through the month, we have achieved half of these sales in reality. So now our actual revenue is the real sales PLUS the expected sale. Are the expected sales at the same level? How have we allowed for the fact that some budgeted sales have been realized.
What happens as actual sales occur as the month proceeds? Do the expected sales in budget automatically get adjusted down, to reflect the "conversion" to actual? Is this something that is taken care of in the transnational system, I would need to make sure that it is just a straight blend of fields between the Value Entry and Item Budget Entry tables at an item by item level, and not something more complicated
Share
ReportUSE SPIERLIVE
GO
SELECT b.[Budget Name],
MONTH(b.[Date]) AS [Date],
MONTH(a.[Posting Date]),
COALESCE(a.[Item No_], b.[Item No_]) AS [Item No_],
COALESCE(a.[Source No_],b.[Source No_]) AS [Source No_],
b.[Source No_],
b.[Quantity],
a.[Invoiced Quantity],
b.[Cost Amount],
a.[Cost Amount (Actual)],
b.[Sales Amount],
a.[Sales Amount (Actual)],
a.[Sales Amount (Expected)],
a.[Cost Amount (Expected)]
FROM
(SELECT a.[Posting Date], a.[Item No_], a.[Source No_],
SUM(a.[Invoiced Quantity]) AS [Invoiced Quantity],
SUM(a.[Sales Amount (Actual)]) as [Sales Amount (Actual)],
SUM(a.[Cost Amount (Actual)]) AS [Cost Amount (Actual)],
SUM(a.[Sales Amount (Expected)]) AS [Sales Amount (Expected)],
SUM( a.[Cost Amount (Expected)]) AS [Cost Amount (Expected)]
FROM [Spier Live$Value Entry] a
GROUP BY a.[Posting Date], a.[Item No_], a.[Source No_], a.[Invoiced Quantity], a.[Sales Amount (Actual)],
a.[Cost Amount (Actual)], a.[Sales Amount (Expected)], a.[Cost Amount (Expected)]) a
FULL OUTER JOIN
(SELECT b.[Budget Name], b.[Date], b.[Item No_],b.[Source No_],
SUM(b.[Quantity]) AS [Quantity], SUM(b.[Cost Amount]) AS [Cost Amount],
SUM(b.[Sales Amount]) AS [Sales Amount]
FROM [Spier Live$Item Budget Entry] b
GROUP BY b.[Budget Name], b.[Date], b.[Item No_],b.[Source No_], b.[Quantity],b.[Cost Amount],
b.[Sales Amount],b.[Budget Dimension 1 Code]) b
ON a.[Item No_] = b.[Item No_]
Where a.[Source No_] = b.[Source No_]
and month(b.[Date]) = month(a.[Posting Date])
and b.[Budget Name] LIKE '%BUD%'
and b.[Quantity] <> 0
ORDER BY b.[Budget Name]
*This post is locked for comments
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (