October 4, 2012 at 5:52 am
I have a query that I'm trying to get the sum of sales for a rolling 12 months and after doing some validation we noticed that the query isn't doing a rolling 12. How can I tweak this query to get the SUM of sales for a rolling 12 months?
The users can select a starting month and year and a end month and year. I need to get the total for the rolling 12 months.
select x.SalesPeriod, Sum(x.TotSales) as TotSales
from
(
SELECT SalesPeriod,
(SELECT sum(TotSales) as t
FROM fn_SalesBySalesPerson(Salesman, ToMonth, ToYear, FromMonth, FromYear) AS a
WHERE
(DATEADD(month, 0, b.SalesPeriod) = DATEADD(month, 12, SalesPeriod)) OR
(DATEADD(month, 0, b.SalesPeriod) = DATEADD(month, 11, SalesPeriod)) OR
(DATEADD(month, 0, b.SalesPeriod) = DATEADD(month, 10, SalesPeriod)) OR
(DATEADD(month, 0, b.SalesPeriod) = DATEADD(month, 9, SalesPeriod)) OR
(DATEADD(month, 0, b.SalesPeriod) = DATEADD(month, 8, SalesPeriod)) OR
(DATEADD(month, 0, b.SalesPeriod) = DATEADD(month, 7, SalesPeriod)) OR
(DATEADD(month, 0, b.SalesPeriod) = DATEADD(month, 6, SalesPeriod)) OR
(DATEADD(month, 0, b.SalesPeriod) = DATEADD(month, 5, SalesPeriod)) OR
(DATEADD(month, 0, b.SalesPeriod) = DATEADD(month, 4, SalesPeriod)) OR
(DATEADD(month, 0, b.SalesPeriod) = DATEADD(month, 3, SalesPeriod)) OR
(DATEADD(month, 0, b.SalesPeriod) = DATEADD(month, 2, SalesPeriod)) OR
(DATEADD(month, 0, b.SalesPeriod) = DATEADD(month, 1, SalesPeriod)) OR
(DATEADD(month, 0, b.SalesPeriod) = frame)) AS TotSales
from fn_SalesBySalesPerson(Salesman, ToMonth, ToYear, FromMonth, FromYear) AS b
) as x
group by x.SalesPeriod
order by x.SalesPeriod
October 4, 2012 at 6:13 am
You can easily, but you need to read this first:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 4, 2012 at 6:23 am
i'm not able to post data. the query is present.
if its easy to accomplish, how can I accomplish it?
October 4, 2012 at 7:04 am
SQL_NuB (10/4/2012)
i'm not able to post data. the query is present.if its easy to accomplish, how can I accomplish it?
DDL of tables used (at least relevant part of it)?
What is logic inside of your UDF function, what does it return?
No one asking you to provide real data - you can obfuscate it.
Non-working query by itself with some hidden logic in UDF is clearly gives not enough details to help you.
October 4, 2012 at 7:07 am
the function is a select statement that pulls from 2 tables, it should be a view, its just pulling back months, name, and sales figures.
the select shown is pulling the view
October 4, 2012 at 7:28 am
Your query doesn't make any sense!
What are Salesman, ToMonth, ToYear, FromMonth, FromYear you are passing into function?
are they supposed to be variables/input parameters or what?
You better use aliases in the right part of filters in WHERE clause to help your query readability.
Can you provide DDL for the UDF?
You are wasting your own time...
October 4, 2012 at 7:32 am
really?
I have someone else looking at it.
Anyway, salesman, FromMonth, FromYear, ToMonth, ToYear are variables that are passed into it so it returns data.
October 4, 2012 at 7:49 am
To help us help you we do need more information than what you have supplied, while we appreciate that you may not be able to provide us with the data from your database, it does help if you can provide us atleast some made up data and what you expect the outcome to be based on that made up data. With that we also need to CREATE FUNCTION, CREATE TABLE statements to be able to create a test environment in order to see what is actually going on with the objects in question.
Please help us to help you and remember we cannot see what you can see.
October 4, 2012 at 8:43 am
SQL_NuB (10/4/2012)
really?I have someone else looking at it.
Anyway, salesman, FromMonth, FromYear, ToMonth, ToYear are variables that are passed into it so it returns data.
Yes, definitely!
As far as I'm aware, no one on this forum is paid here to help others with their work. We spend our own precious time here. In return, we ask for very little: please post enough details so we can help.
In the way you posted your query there was nothing to suggest that you are passing variables into function, as variables in T-SQL are prefixed with "@".
I do really wish you that "someone else" will help you with your query.
:sick:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply