SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Server 2008 Provide Value Once for Multiple In
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rarascon
Starting Member

USA
6 Posts

Posted - 02/22/2013 :  12:21:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 02/22/2013 :  13:01:05  Show Profile  Reply with Quote
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.
Go to Top of Page

rarascon
Starting Member

USA
6 Posts

Posted - 02/22/2013 :  13:17:01  Show Profile  Reply with Quote
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
Go to Top of Page

rarascon
Starting Member

USA
6 Posts

Posted - 02/22/2013 :  13:18:23  Show Profile  Reply with Quote
shnikees - I think it worked!!
thx so much!

Cheers,
Rob
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 02/22/2013 :  16:18:32  Show Profile  Reply with Quote
You are welcome - glad to help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000