May 1, 2013 at 8:49 am
This is related to a previous post, but I didn't have all the information. Now, I'm including the selects to see if I can find a solution, or need to go in a completely different direction. This also relates to SSRS, which is what these queries are built to run this report. I'm hoping to come to some solution, so all suggestions are appreciated! = )
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
May 1, 2013 at 10:21 am
What I ask this: I can run the two queries separately in SSRS, but I didn't have a way of joining the same customers that purchased one or more Product Groups to the query of total customer sales. It doesn't matter if it's customers that didn't buy the Product Group(s) or if it's just all customer. I just need to be able to join it to the customers that did buy those Product Group(s). Is this possible to join these queries in the report itself?
May 1, 2013 at 8:49 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply