April 29, 2013 at 8:48 pm
Maybe I'm just trying to get confirmation that this is possible, but essentially I'm trying to run the main query for a month or more date range and join to another dataset (basically the same data) but with rolled up sales for a Total Sales # by Customer (no parameters). I know, you're thinking why not eliminate the join and just start with the wider dataset?? Well, yes, and I might still need to do that, but because of the way several queries have already been built in SSRS, and management is asking after the fact to add a Customer Penetration field to the report, I'm trying to add this join to what is already built.
So, I will try to summarize how I think the select should be built (please be gentle, I'm still fairly new at SSIS). In order to get customer penetration the formula is customer sales on one or more product group(s)*100 / total customer sales (same customers). So this is where I have to join these specific customers.
DECLARE and variables...
select
branch
,customer
,product group
,Monthly Sales
,Monthly Total Sales
,Customer Penetration calc
from
(select
branch, customer, product group, etc....
from salestrans
inner join to date
...
inner join to customer
...
left join to product
...
left outer join to billbacks
...
full outer join (to total sales for customer penetration)
(select branch, customer, total sales, etc
) as TotalCustSales
on (TotalCustSales.branch = salestrans.branch
and TotalCustSales.CustID = salestrans.CustID
and TotalCustSales.Branch = customer.CompanyID
and TotalCustSales.CustID = customer.CustomerID)
where salestrans conditions, etc...
group by...
) as CustProdGrpSales
where Variable/parameter conditions...
Keep in mind that the main query and the customer penetration query run fine separately, but when joined I'm getting a divisor=0 error, which tells me that the total sales is not coming out correctly. Again, the total sales select is essentially the same as the main query, but just stripped down to get customer sales totals.
But I'm also getting an error at the end of the main select outside of the parentheses where I called it ) as CustProdGrpSales. It says something about "No column name was specified for column 29 of 'CustVendProdGrpSales' ". Haven't seen this one before, but it just means my syntax is off somewhere. This is the only line that has red squiggles, so I can't tell where the error lies. However, maybe someone has a better suggestion on how to build this? Thanks in advance!
April 29, 2013 at 9:02 pm
From your very vague post I am not even sure what the question is. Is it divide by 0? Is it the syntax error? Is it something else? We can't see your screen and we don't know what your tables are like. From what you posted we only what a portion of your select statement is. Please try to clarify what the issue is. If you have specific syntax issues we need to at the very least see the entire select statement. Very likely we might need to have ddl and sample data too. That somewhat depends on what your actual question I though.
_______________________________________________________________
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/
April 29, 2013 at 9:04 pm
Right, and I was trying to be brief although that typically turns out to be vague. Nevertheless, I found the syntax error... when I said there was only one squiggly at the end of the parentheses for CustProdGrpSales, there was another one in the inner select that I didn't see. I've corrected that, and now I'm seeing if it will run.
My apologies, I will post shortly if this actually works now. ;o)
April 29, 2013 at 9:11 pm
The correction I made works now, and it appears that the data is valid. However, the problem I have now that I should have also foreseen is that I now have dupe total sales values for each customer that has more than one product group, which happens often. I suppose I can write a partition by to alleviate this, but I wonder if there's a better option...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply