November 24, 2014 at 3:58 am
Hello
I have been asked to write a query on SBO.
It requires (among other) a reference to a budget, which does not exist anywhere now.
It is a yearly budget that changes quarterly.
I suppose I could add a table and update the no. each quarter - but will the history be kept?
What would you recommend?
Also, they require a quarterly comparison (all in the same report) is it possible for the user to enter a range of date (for sales data, for example) and also receive the data for that range only last year's?
I would appreciate your help.
Carin
November 24, 2014 at 7:12 am
Sorry -
SBO is Sap Business One.
We import and distribute beer and work with Microsoft SQL 2012
It appears it is not a budget but sales goals per item.
The report should get sales by dates, same time frame a year ago, sales goals for that time and some other information that is quite straight forward.
Thanks!
November 24, 2014 at 7:22 am
Without the current design it's hard to provide a solid solution. But I would get to it following this approach:
Create a new table with columns to hold: the date, the quarter (optional or derived), the year (optional or derived) and the amount (target/budget/whatever). The optional or derived columns are there to get your report-query simplified. You can join the data to these derived columns instead of calculating these values inside the query.
Add a new record every time the amount needs to change. This way you can select the most recent row to get the actual value and also keep the history in all other rows.
The actual report query depends on the structure of your tables and the data inside. If you build the query inside a stored procedure, you can add a parameter to allow a specific quarter to be specified. The value of the parameter can be entered thruogh an application or perhaps you could build a SSRS report.
November 24, 2014 at 7:28 am
Hi and welcome to the forums. I can't speak for other but there is nowhere near enough information in your post to offer anything in the way of help. We don't know what the tables are like, we don't know what your data is like and we have no idea what you are trying to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 24, 2014 at 7:32 am
Hi and thank you for your remark.
As I am new to the forum (and relatively new to SQL) - I am not sure how I can demonstrate our database and would appreciate assistance for this and future references.
November 24, 2014 at 7:33 am
See the first link in Sean's signature.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 24, 2014 at 8:04 am
It sounds like you're asking for something involving a date dimension, maybe start looking at that.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply