none
Help with Division Calcualtion RRS feed

  • Question

  • Hi, 

    I need help with the following logic to be put into some code if possible please? I am looking at working out the renewal rate of contracts but I'm not too sure how to code this up. 

    I need to essentially write out: 

    Value of contracts renewed (Variable: Contract_Type = '00 Renewal' and Variable: Date_Start = This month and Variable: 'Contract_Amount')

    /

    Value of contracts due for renewal (Variable: Date_End = This month and Variable: 'Contract_Amount')

    The Output I am looking for would be:

    Date      %

    Jan 18   22%

    Feb 18   13%

    Mar 18   70%

    etc..........      

    I hope this makes sense, if it doesn't please let me know. 

    Thanks in advance

    Thursday, January 10, 2019 11:09 AM

Answers

  • Try with the NULLIF Function :

    (SUM(CASE WHEN Contract_Type = '00 Renewal' AND MONTH(Date_Start)=MONTH(GETDATE()) AND YEAR(Date_Start)=YEAR(GETDATE()) THEN Contract_Amount ELSE 0 END)/
    NULLIF(SUM(CASE WHEN MONTH(Date_End)=MONTH(GETDATE()) AND YEAR(Date_End)=YEAR(GETDATE()) THEN Contract_Amount ELSE 0 END),0))*100
    


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Thursday, January 10, 2019 2:57 PM
    Answerer
  • Thanks for your efforts, it doesn't give me what I need as the percentages are all zero, I will have a think about what else I can provide in order to help get an answer. 

    If the data type for ContractAmount is int, try multiplying it with 1E0 to convert it to a float. With only ints, you get integer division.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, January 10, 2019 10:31 PM
    Moderator

All replies

  • Hi,

    You mean something like this :

    CONVERT(VARCHAR(7), Date_Start), 126),
    (SUM(CASE WHEN Contract_Type = '00 Renewal' AND MONTH(Date_Start)=MONTH(GETDATE()) AND YEAR(Date_Start)=YEAR(GETDATE()) THEN Contract_Amount ELSE 0 END)/
    SUM(CASE WHEN MONTH(Date_End)=MONTH(GETDATE()) AND YEAR(Date_End)=YEAR(GETDATE()) THEN Contract_Amount ELSE 0 END))*100
    FROM TableName
    GROUP BY CONVERT(VARCHAR(7), Date_Start), 126)


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Thursday, January 10, 2019 11:17 AM
    Answerer
  • Thanks for the suggestion, but I have ran that but get this error message:

    Msg 156, Level 15, State 1, Line 8
    Incorrect syntax near the keyword 'CONVERT'.

    Any idea why please?

    Thursday, January 10, 2019 11:36 AM
  • If I take the CONVERT part of the code out I then get another error message:

    Msg 8134, Level 16, State 1, Line 2
    Divide by zero error encountered.

    Sorry, there are two things now that i am not sure how to resolve?

    Thursday, January 10, 2019 11:53 AM
  • Try with the NULLIF Function :

    (SUM(CASE WHEN Contract_Type = '00 Renewal' AND MONTH(Date_Start)=MONTH(GETDATE()) AND YEAR(Date_Start)=YEAR(GETDATE()) THEN Contract_Amount ELSE 0 END)/
    NULLIF(SUM(CASE WHEN MONTH(Date_End)=MONTH(GETDATE()) AND YEAR(Date_End)=YEAR(GETDATE()) THEN Contract_Amount ELSE 0 END),0))*100
    


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Thursday, January 10, 2019 2:57 PM
    Answerer
  • So you post a very incomplete description of your problem, and I am amazed that Ousama was able to type something up without any knowledge of your tables. And now you expect a solution that works without any problems?

    If you want to be fed a tested solution that works out of the box, you should provide CREATE TABLE statement(s) for your tables, INSERT statements with sample data and the expected result given the sample. Then we can copy and past into a query window and develop a tested solution.

    If you only post a vague description of your problem, you will at best only get vague description of a solution back.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se


    Thursday, January 10, 2019 2:59 PM
    Moderator
  • @Erland

    I am new to SQL so I am trying to build this up in my mind and thankfully with the help of Ousama, I hope to find a resolution. 

    I'm not saying I do expect a resolution but any help is greatly appreciated (which we all look for on this forum and this is kind of the point of the forum, right?). I am not doing this to be awkward, I just have very limited knowledge of SQL and in the very early stages of learning and as I learn, I will know what I need to provide in order to help get a resolution. You have to remember, we're not all experts on here and won't always know what to post because we don't know ourself. 

    All I was simply saying is that an error message has occurred that I am not familiar with, if someone on the forum is and can help, that would be great. 

    Thursday, January 10, 2019 3:46 PM
  • Thanks for your efforts, it doesn't give me what I need as the percentages are all zero, I will have a think about what else I can provide in order to help get an answer. 
    Thursday, January 10, 2019 4:10 PM
  • Thanks for your efforts, it doesn't give me what I need as the percentages are all zero, I will have a think about what else I can provide in order to help get an answer. 

    If the data type for ContractAmount is int, try multiplying it with 1E0 to convert it to a float. With only ints, you get integer division.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, January 10, 2019 10:31 PM
    Moderator