SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
rarascon
rarascon
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 143
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!
laurie-789651
laurie-789651
SSChasing Mays
SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)

Group: General Forum Members
Points: 616 Visits: 1272
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




rarascon
rarascon
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 143
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, ' ')
rarascon
rarascon
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 143
shnikees - I think it worked!!
thx so much! :-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search