Percent to Total Customer Sales - Penetration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Analysis Server and Reporting Services (2012)

Percent to Total Customer Sales - Penetration

Author

Topic

rarascon
Starting Member

6 Posts

Posted - 2013-04-30 : 22:45:06

I have a somewhat simple query (bear with me) that management has asked for me to add a Customer Penetration field in their report. My problem is not the formula, but how to work with the query that I have now. I don't know if I can paste the entire sql, but I'm thinking there must be some sort of partition/group by function to get the total sales into the main query. I can do the formula in the report once I have the data. I can run the two queries separately, but my other problem is that I need to be able to join the two to be sure it's counting the same customers, not just total sales. One query gives customer and product sales based on several parameters entered. The other query is essentially the same and provides just total customer sales by Branch and Business Unit. There are too many rows to work with in the main query, so that may be part of the problem as well. Any help would be appreciated.

This is the main query that I started with:
--DECLARE @TransDate date = '3/1/2013'
--DECLARE @CompanyID nvarchar(4) = '003'
--DECLARE @BU nvarchar(20) = 'BROADLINE'
--DECLARE @PC nvarchar(20) = 'ALL'
--DECLARE @Family nvarchar(40) = '06 Fzn Groceries And Other'
--DECLARE @ProdLine nvarchar(40) = '59 Fzn Breads & Doughs'
--DECLARE @ProdGrp nvarchar(40) = 'ALL'

SELECT
InvoiceDate as MonthDate
,Branch
,BusinessUnit
,CustID as CustNbr
,CustName
,Fam_ID as ProdFamily
,Lin_ID as ProdLine
,Grp_ID as ProdGroup
,Prod_ID as ProdNbr
,NetSales

FROM
( SELECT
CONVERT(DATETIME,DATEADD(mm, DATEDIFF(mm,0,salestrans.LDayDay), 0),101) AS InvoiceDate
,salestrans.Branch
,salestrans.BusinessUnit
,salestrans.CustID
,customer.CustName
,Product.Fam_ID
,Product.Lin_ID
,Product.Grp_ID
,Product.Prod_ID
,SUM (ISNULL (CASE WHEN salestrans.pctype IN ('S','A') THEN isnull(salestrans.extprice,0) ELSE 0 END, 0)) as NetSales

FROM bi.salestrans salestrans
left outer join
bi.star2sales star2sales
on (salestrans.Branch = star2sales.Branch
and salestrans.InvNo = star2sales.Invno and salestrans.InvDtl = star2sales.Invdtl)
inner join
abcp.Customer customer
on (salestrans.Branch = customer.CompanyID
and salestrans.CustID = customer.CustomerID)

left join
bi.Product
on (salestrans.ProdWhs = product.Prod_Whs
and salestrans.ProdID = Product.Prod_ID)

WHERE
salestrans.LDayDay BETWEEN dateadd(month,datediff(month,0,@TransDate)-0,0)
AND dateadd(ms,-3,DATEADD(mm, DATEDIFF(month,0, @TransDate)+1, 0))
AND Customer.[Effective Date] <= @TransDate
AND (Customer.EndDate > @TransDate OR Customer.EndDate IS NULL)
AND Customer.[Excluded?] = 'NO'
AND (Customer.CompanyID IN (@CompanyID) --, @CompanyID2)
OR 'ALL' IN (@CompanyID) ) --, @CompanyID2))

and Product.Prod_Whs in ('008','003','009','020')
and salestrans.PcID not in ('65','95')

GROUP BY
CONVERT(DATETIME,DATEADD(mm, DATEDIFF(mm,0,salestrans.LDayDay), 0),101)
,salestrans.Branch
,salestrans.BusinessUnit
,salestrans.CustID
,customer.CustName
,Product.Fam_ID
,Product.Lin_ID
,Product.Grp_ID
,Product.Prod_ID
) as CustVendProdGrpSales
WHERE (PCID IN (@PC) OR 'ALL' IN (@PC))
AND (BusinessUnit IN (@BU) OR 'ALL' IN (@BU))
AND (Fam_ID IN (@Family) or 'ALL' IN (@Family))
AND (Lin_ID IN (@ProdLine) OR 'ALL' IN (@ProdLine))
AND (Grp_ID IN (@ProdGrp) OR 'ALL' IN (@ProdGrp))
;
And then I tried to add this column from the query below:
,case when Row_Number() over (partition by salestrans.Branch, salestrans.CustId, BusinessUnit
,Product.Fam_ID
,Product.Lin_ID
,Product.Grp_ID
--,Product.Prod_ID
order by Product.Grp_ID) = 1
then sum(isnull(TotalCMNetSales,0)) else 0 end as Totalsalestest

This is the total customer sales query. I've tried doing an inner subselect several ways, and I was able to get some results the first time but it was not summing the customer sales correctly. Then, I tried a few other things, but it was "timing out" using up too much memory.
( SELECT
InvoiceDate as MonthDate
,Branch
,BusinessUnit
,CustID
,SUM(TotalCMNetSales) as TotalCMNetSales

FROM
( SELECT
CONVERT(DATETIME,DATEADD(mm, DATEDIFF(mm,0,salestrans.LDayDay), 0),101) AS InvoiceDate
,salestrans.Branch
,salestrans.BusinessUnit
,salestrans.CustID
--,salestrans.ProdID
--,Product.Fam_ID
--,Product.Lin_ID
--,Product.Grp_ID

,SUM (ISNULL (CASE WHEN DATEADD(m,DATEDIFF(m,0,salestrans.LDayDay),0) = dateadd(month,datediff(month,0,@TransDate)-0,0)
and salestrans.pctype IN ('S','A') THEN isnull(salestrans.extprice,0) ELSE 0 END, 0)) as TotalCMNetSales

FROM bi.salestrans salestrans

inner join
abcp.Customer customer
on (salestrans.Branch = customer.CompanyID
and salestrans.CustID = customer.CustomerID)

left join
bi.Product
on (salestrans.ProdWhs = product.Prod_Whs
and salestrans.ProdID = Product.Prod_ID)

WHERE
salestrans.LDayDay BETWEEN dateadd(month,datediff(month,0,@TransDate)-0,0)
AND dateadd(ms,-3,DATEADD(mm, DATEDIFF(month,0, @TransDate)+1, 0))
AND Customer.[Effective Date] <= @TransDate
AND (Customer.EndDate > @TransDate OR Customer.EndDate IS NULL)
AND Customer.[Excluded?] = 'NO'
AND (Customer.CompanyID IN (@CompanyID) --, @CompanyID2)
OR 'ALL' IN (@CompanyID) ) --, @CompanyID2))

and Product.Prod_Whs in ('008','003','009','020')
and salestrans.PcID not in ('65','95')

GROUP BY
CONVERT(DATETIME,DATEADD(mm, DATEDIFF(mm,0,salestrans.LDayDay), 0),101)
,salestrans.Branch
,salestrans.BusinessUnit
,salestrans.CustID
--,salestrans.ProdID
--,customer.CustName
--,Product.Fam_ID
--,Product.Lin_ID
--,Product.Grp_ID
) as CustSales
WHERE
(BusinessUnit IN (@BU) OR 'ALL' IN (@BU))
--AND (Fam_ID NOT IN (@Family) or 'ALL' IN (@Family))
--AND (Lin_ID NOT IN (@ProdLine) OR 'ALL' IN (@ProdLine))
--AND (Grp_ID NOT IN (@ProdGrp) OR 'ALL' IN (@ProdGrp))
GROUP BY
InvoiceDate
,Branch
,BusinessUnit
,CustID
--,Fam_ID
--,Lin_ID
--,Grp_ID
) as TotalCustSales

Cheers,
Rob

rarascon
Starting Member

6 Posts

Posted - 2013-05-01 : 22:49:29

After reading more online and other forum posts, I discovered that I was going about this wrong. I created another query that used a customer list based on the main query, so that I could get total sales for just those customers. It seems to be working, but it takes a while to populate the customer list. But at least it's working. Please let me know if someone has another suggestion.

Cheers,
Rob

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources