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

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

  • 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

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

  • shnikees - I think it worked!!

    thx so much! 😀

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply