InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 20:13:52 GMT20http://www.sqlservercentral.com/Forums/Topic1423150-391-1.aspxshnikees - I think it worked!!thx so much! :-DFri, 22 Feb 2013 11:18:59 GMTrarasconhttp://www.sqlservercentral.com/Forums/Topic1423150-391-1.aspxHi 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.ProdFamilyorder by salestrans.Branch, salestrans.custid, isnull(salestrans.Route, ' ')Fri, 22 Feb 2013 11:06:33 GMTrarasconhttp://www.sqlservercentral.com/Forums/Topic1423150-391-1.aspxHow about this:[code="sql"]--== 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[/code]Fri, 22 Feb 2013 10:47:59 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1423150-391-1.aspxThanks 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 R2The 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 1410001 Dry Groceries 101 1410001 Meat 101 14This 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 value10001 Dry Groceries 101 0 <<< remaining rows for same Cust# and Route will show 010001 Meat 101 0In 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!Fri, 22 Feb 2013 10:12:47 GMTrarascon