Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012
    Posts
    2

    Unanswered: Inner join with condition and sum

    I have following problem:


    table1 contains sales data:
    Code:
    Custnro		route		sal.rep		EUR
    -------		-----		-------		---
    1234		10		25		100
    1234		11		25		100
    1234		12		40		100
    4321		10		25		50
    4321		10		40		20
    4321		11		50		30
    table2 contains rules
    Code:
    Custnro		route		sal.rep
    -------		-----		-------
    1234		10;12		25
    4321				25;40
    I need to get following joined table:
    Code:
    Custnro		EUR
    -------		---
    1234		100
    4321		70

    I have done this so far:

    select table2.Custnro, table1.EUR from table2
    inner join table1
    on table2.Custnro=table1.Custnro

    Code:
    Custnro		EUR
    -------		---
    1234		100
    1234		100
    1234		100
    4321		50
    4321		20
    4321		30
    Question1: How do I sum Sales?

    select table2.Custnro, sum(table1.EUR) as YHT from table2
    inner join table1
    on table2.Custnro=table1.Custnro

    Doesn't work...

    Question2: How do I filter sales using rules?

    All ideas are wellcome!

  2. #2
    Join Date
    Jan 2005
    Posts
    28
    Hi,
    Please give a try for the following SQL select statement
    You can find the sql split string function source codes at referenced link.
    I hope that helps you to solve the problem.
    By the way I successfully get the desired return set


    Code:
    with rules as (
    select 
    	t.Custnro, 
    	case when s.val = '' then NULL else s.val end as salrep,
    	case when r.val = '' then NULL else r.val end as route
    from table2 t
    cross apply dbo.Split(isnull(t.salrep,''), ';') s
    cross apply dbo.Split(isnull(t.route,''), ';') r
    )
    select
    	data.Custnro,
    	sum(EUR) as EUR
    from table1 data
    inner join rules on data.Custnro = rules.Custnro
    where
    	data.route = isnull(rules.route, data.route)
    	and data.salrep = isnull(rules.salrep, data.salrep)
    group by data.Custnro

  3. #3
    Join Date
    Jul 2012
    Posts
    2
    Thank you for quick reply!

    Got that sum working now. Going to try that split next...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •