SQL Server 2008 Provide Value Once for Multiple Instances in Select statement

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SQL Server 2008 Provide Value Once for...

SQL Server 2008 Provide Value Once for Multiple Instances in Select statement

Rate Topic

Display Mode

Topic Options

Author

Message

rarascon

Posted Friday, February 22, 2013 10:12 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, June 06, 2013 8:20 AM
Points: 21, Visits: 84

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!

Post #1423150

laurie-789651

Posted Friday, February 22, 2013 10:47 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:26 PM
Points: 278, Visits: 808

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

Post #1423166

rarascon

Posted Friday, February 22, 2013 11:06 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, June 06, 2013 8:20 AM
Points: 21, Visits: 84

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! Smile

>>>
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, ' ')

Post #1423174

rarascon

Posted Friday, February 22, 2013 11:18 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, June 06, 2013 8:20 AM
Points: 21, Visits: 84

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

Post #1423180

« Prev Topic | Next Topic »

Permissions