2

I want to do query. I use Microsoft SQL.

  • If product is equal to FAST...
    • and tree_level is equal 0,1,2,3,4,5, then count the number of 0,1,2,3,4,5. For example if there is number of 30 item which contain "0,1,2,3,4,5". Then multiply 30*2.
    • and tree_level equal -1, then count number of -1 and multiply by 2
  • If product is equal to MOBIL and tree_level is equal 0,1,2,3,4,5 count the number and multiply by 3
  • If product is equal to FACE...
    • and tree_level is equal to 0,1,2,3,4,5, then count the number of 0,1,2,3,4,5 and multiply by 3
    • and tree_level equal -1, then count number of -1 and multiply by 2

How can ı use where, case and counter statements together? I can not do that.

select
    DS.PersTel ,
    DW.AD ,
    DW.SOYAD ,
    DS.RefPhoner   ,
    DS.Product ,
    DS.Tree_level 

WHERE DS.Product like '%FACE%' (
CASE  
WHEN DS.Tree_level IN (0,1,2,3,4,5) THEN count(DS.Tree_level) * 3 
WHEN DS.Tree_level IN (-1) THEN count(DS.Tree_level) * 2
END  

WHERE DS.Product like '%MOBIL%'  (
CASE DS.Tree_level
WHEN DS.Tree_level IN (0,1,2,3,4,5) THEN count(DS.Tree_level) * 3 
END  )

WHERE DS.Product like '%FAST%' (
CASE  DS.Tree_level
WHEN DS.Tree_level IN (0,1,2,3,4,5) THEN count(DS.Tree_level) * 2 
WHEN DS.Tree_level IN (-1) THEN count(DS.Tree_level) * 2
END  )


    from dw_prod.FRTN.DIG_SEFER  AS DS 
    inner join dw_prod.dbo.DW_MUST AS DW 
    ON DW.CEP_TEL = DS.PersTel

updated case part

       select
        DS.PersTel ,
            DW.AD ,
            DW.SOYAD ,
            DS.RefPhoner   ,
            DS.Product ,
            DS.Tree_level 
    CASE  
    WHEN DS.Tree_level IN (0,1,2,3,4,5)AND DS.Product LIKE '%FACE%' THEN count(DS.Tree_level) * 3 
    WHEN DS.Tree_level IN (-1) THEN count(DS.Tree_level) * 2
    END AS Answer1 

    CASE DS.Tree_level
    WHEN DS.Tree_level IN (0,1,2,3,4,5) AND DS.Product LIKE '%MOBIL%' THEN count(DS.Tree_level) * 3 
    END AS Answer2 

    CASE  DS.Tree_level
    WHEN DS.Tree_level IN (0,1,2,3,4,5) AND DS.Product LIKE '%FAST%' THEN count(DS.Tree_level) * 2 
    WHEN DS.Tree_level IN (-1) THEN count(DS.Tree_level) * 2
    END AS Answer3

  from d.FR  AS Ds 
            inner join d.dbo.DW AS Dw 
            ON DW.CEP_TEL = DS.PersTel
Improve this question
5
  • Please provide sample output for what you want to achieve. Commented Aug 29, 2013 at 11:36
  • What's the actual problem? Commented Aug 29, 2013 at 11:47
  • I CAN not use with counter , where , case algorthm together Commented Aug 29, 2013 at 12:02
  • You can't use "count" in "where", use it on "having" Commented Aug 29, 2013 at 12:05
  • ı delete where statement but still counter not work Commented Aug 29, 2013 at 12:30

1 Answer 1

Reset to default
1

This should get you started:

select
    product,
    tree_level,
    count(1) over (partition by product,tree_level_category) 
        * case when product like '%FACE%'
               then case tree_level_category
                        when '0-5' then 3
                        when '-1' then 2
                    end
               when product like '%MOBIL%'
               then case tree_level_category
                        when '0-5' then 3
                    end
               when product like '%FAST%'
               then case tree_level_category
                   when '0-5' then 2
                   when '-1' then 2
              end
        end 
from (
    select
        product,
        tree_level,
        case when tree_level in (0,1,2,3,4,5)
             then '0-5'
             when tree_level = '-1'
             then '-1'
             else null 
        end tree_level_category
    from
       product
) as t

SQLFiddle here

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

how can ı use it with join
Just add it in in the lower query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.