February 22, 2013 at 10:12 am
Thanks in advance for your help.
I'm working with an existing query that is called CustSalesbyRoute. I've made a recent change to it to get a distinct count of Route Stops by Customer and this information is stored in a separate table I created for CustRouteStops. However, the query also includes Product Family so this duplicates my distinct Route-Stop count. I've confirmed that several reports use Product Family from this table to pull various data, otherwise, I would eliminate this column and everything would work great. I also know that this essentially goes against relational database integrity; however, I'm left to work on it, and I'm hoping to have a solution that is equivalent to Excel's FREQUENCY/MATCH functions or SUMPRODUCT, etc.
SQL Server 2008 R2
The question is this: Is there a way to display the Customer Route-Stops value for the first instance of rows that have multiple Product Family values and show 0 for any others for the same Customer/Route combination? For example, below is what the query is doing now if I include Product Family:
CustID ProdFamily Route #ofStops
10001 Dairy 101 14
10001 Dry Groceries 101 14
10001 Meat 101 14
This is what I would like to see happen because I need to leave Product Family in the table:
CustID ProdFamily Route #ofStops
10001 Dairy 101 14 <<< 1st instance (or only once) will provide the Stops value
10001 Dry Groceries 101 0 <<< remaining rows for same Cust# and Route will show 0
10001 Meat 101 0
In other words, say we take the example I provided above, which creates duplicate rows with the distinct CustID/Route combo. This shows the customer with 14 stops (let's say within that month) for each ProdFamily providing inaccurate #ofStops. So, I tried a case when that counts the number of duplicate distinct CustID/Route combo and if the count is >1 then only provide the #ofStops once, not on all rows. If it is <= 1 then the #ofStops is ok. The case when I have now is not recognizing the dupe rows.
Thanks again!
February 22, 2013 at 10:48 am
How about this:
--== TEST DATA ==--
declare @data as table
(
CustID int,
ProdFamily varchar(15),
Route tinyint,
#ofStops int
);
insert @data values (10001, 'Dairy', 101, 14)
insert @data values (10001, 'Dry Groceries', 101, 14)
insert @data values (10001, 'Meat', 101, 14)
insert @data values (10002, 'Dairy', 101, 15)
insert @data values (10002, 'Dry Groceries', 101, 15)
insert @data values (10002, 'Meat', 101, 15)
--== SUGGESTED SOLUTION ==--
select CustID, ProdFamily, Route,
case when Row_Number() over (partition by CustId order by ProdFamily) = 1 then #ofStops else 0 end as [#ofStops]
from @data
February 22, 2013 at 11:06 am
Hi Laurie: Thx for the response!
I had seen some other posts that used partition by regarding distinct counts, so I'll give this a shot.
I'm hoping to not complicate things, but I'll post the query I have now in case you see how/where to include this. You'll see my "crude" attempt to figure this out the first time. = ) You'll also see my comments that the first data point was incorrect and this is why I'm working on correcting it now. It's working fine until I put Product Family back in the query. I'm going to work on it in the meantime.
Thx again! 🙂
>>>
SELECT
RIGHT(CONVERT(VARCHAR(10), salestrans.LDayDay, 103), 7) AS [MMYYYY],
--DATENAME(MM, salestrans.LDayDay) + ' ' + CAST(YEAR(salestrans.LDayDay) AS VARCHAR(4)) AS [MonthYYYY],
salestrans.Branch,
salestrans.CustID,
customer.CustName,
customer.Address1 CustAddress1,
customer.PcName,
customer.PcgroupName,
substring(customer.PcName,1,2) PcID,
salestrans.ProdFamily,
substring(customer.SlsName, 5, 50) SlsDesc,
substring(customer.SlsName, 1, 3) SlsID,
ISNULL(customer.BidID, 'NONE') CustBid,
isnull(salestrans.Route, ' ') Route,
SUM(ISNULL(salestrans.IsStop, 0)) sumistop, /* this one is Stops in BI - not accurate, does not always count multiple stop addresses */
case when salestrans.ProdFamily <> 'Services'
and count(salestrans.Branch+salestrans.CustID+isnull(salestrans.Route, ' ')) >1
then isnull(Stops,0) else 0 end as Stops, /* added this for distinct count of stops in reports */
SUM( ISNULL(salestrans.exttrucst,0)) as exttrucst,
SUM (ISNULL(salestrans.extavgcst,0)) as extavgcst,
SUM (ISNULL (star2sales.billback, 0)) as billbacks,
SUM (ISNULL (star2sales.oidisc, 0)) as oidisc,
(SUM (ISNULL (star2sales.oidisc, 0)))
+ (SUM (ISNULL (star2sales.billback, 0)))
+ SUM (ISNULL (salestrans.cbu, 0))
+ SUM (ISNULL (star2sales.purrebate, 0)) sumrebate, /* researching for what this is used for - replacing with actual Billbacks from Star */
SUM (ISNULL (salestrans.extprice, 0)) as extprice,
SUM (ISNULL(salestrans.invqt,0)) as invqt,
SUM(ISNULL(salestrans.Cbu,0)) as cbu
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
(select
TransDate,
CompanyID,
CustomerID,
Route,
SUM(ISNULL(Stops,0)) AS Stops /* added this for distinct count of stops in reports */
FROM abcp.CustRouteStops
group by TransDate,
CompanyID,
CustomerID,
Route
) as CustRouteStops
on CustRouteStops.CompanyID = salestrans.Branch
and CustRouteStops.CustomerID = salestrans.CustID
--AND salestrans.Branch = CustRouteStops.CompanyID
AND RIGHT(CONVERT(VARCHAR(10), salestrans.LDayDay, 103), 7) = CustRouteStops.TransDate
-- AND salestrans.CustID = CustRouteStops.CustomerID
AND CustRouteStops.Route = isnull(salestrans.Route, ' ')
AND customer.CompanyID = CustRouteStops.CompanyID
AND customer.CustomerID = CustRouteStops.CustomerID
WHERE
--salestrans.LDayDay BETWEEN dateadd(month,datediff(month,0,?)-1,0)
--AND dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0, ?), 0))
--and customer.[Effective Date] <= dateadd(month,datediff(month,0,?)-1,0)
--and (customer.EndDate >= dateadd(day,-3,DATEADD(mm, DATEDIFF(mm,0, ?), 0)) OR customer.EndDate IS NULL)
salestrans.LDayDay BETWEEN dateadd(month,datediff(month,0,'2/1/2013')-1,0)
AND dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0, '2/1/2013'), 0))
and customer.[Effective Date] <= dateadd(month,datediff(month,0,'2/1/2013')-1,0)
and (customer.EndDate >= dateadd(day,-3,DATEADD(mm, DATEDIFF(mm,0, '2/1/2013'), 0)) OR customer.EndDate IS NULL)
GROUP BY
RIGHT(CONVERT(VARCHAR(10), salestrans.LDayDay, 103), 7),
-- DATENAME(MM, salestrans.LDayDay) + ' ' + CAST(YEAR(salestrans.LDayDay) AS VARCHAR(4)),
salestrans.Branch,
salestrans.CustID,
customer.CustName,
customer.Address1,
customer.PcName,
customer.PcgroupName,
substring(customer.PcName,1,2) ,
substring(customer.SlsName, 5, 50) ,
substring(customer.SlsName, 1, 3) ,
ISNULL(customer.BidID, 'NONE') ,
isnull(salestrans.Route, ' '),
case when salestrans.ProdFamily <> 'Services'
and count(salestrans.Branch+salestrans.CustID+isnull(salestrans.Route, ' ')) >1
then (isnull(Stops,0)) else 0 end,
salestrans.ProdFamily
order by salestrans.Branch, salestrans.custid, isnull(salestrans.Route, ' ')
February 22, 2013 at 11:18 am
shnikees - I think it worked!!
thx so much! 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply