SQL Server 2008 Provide Value Once for Multiple In

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 2008 Forums

Transact-SQL (2008)

SQL Server 2008 Provide Value Once for Multiple In

Author

Topic

rarascon
Starting Member

6 Posts

Posted - 2013-02-22 : 12:21:20

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!

Cheers,
Rob

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-22 : 13:01:05

Your query could be rewritten to allow for this, but without seeing the query, it is hard to tell. Alternative is to keep your existing query and add some code on top of it like this:

SELECT
	CustID,
	ProdFamily,
	[Route],
	CASE WHEN RN = 1 THEN [#ofStops] ELSE 0 END AS [#ofStops]
FROM
(
	SELECT
		CustID,
		ProdFamily,
		[Route],
		[#ofStops],
		ROW_NUMBER() OVER (PARTITION BY CustID,Route ORDER BY ProdFamily) AS RN
	FROM
		YourTableOrQuery
) s

You may need to change the columns in the PARTITION BY clause if it is not doing exactly what you want.

rarascon
Starting Member

6 Posts

Posted - 2013-02-22 : 13:17:01

Thx James! That sounds like a good option, so I'm trying it now.
Here's the full query with your suggestion. Let me know what you think:
>>>
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'
then case when Row_Number() over (partition by salestrans.Branch, salestrans.CustId, isnull(salestrans.Route, ' ') order by ProdFamily) = 1 --then #ofStops else 0 end as [#ofStops]
-- and count(salestrans.Branch+salestrans.CustID+isnull(salestrans.Route, ' ')) >1
then max(isnull(Stops,0)) else 0 end 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, ' ')

Cheers,
Rob

rarascon
Starting Member

6 Posts

Posted - 2013-02-22 : 13:18:23

shnikees - I think it worked!!
thx so much!

Cheers,
Rob

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-22 : 16:18:32

You are welcome - glad to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources