none
How to calculate difference

    Question

  •  

    I have to sum up values for a month and the previous month group by some id. Now I need to find the change between those two values in %.

     

    I was able to sum up the particular value for the month but i don't know how to subtract the values.

     

     

    SELECT

     

    SUM(CASE WHEN [Date] >= @p_Date1 AND [Date] < Dateadd(day, 1, @p_Date1) THEN nip.Bytes END) AS M,

    SUM(CASE WHEN [Date] >= @p_Date2 AND [Date] < Dateadd(day, 1, @p_Date2) THEN nip.Bytes END) AS M1

     

     

    How do i subtract M from M1?

     

     

    Tuesday, July 01, 2008 4:18 PM

Answers

  • Without reproducing the logic twice there are 2 method.  If you have SQL 2005 or Express, then use a Common Table Expression (CTE)

     

    Code Snippet

    WITH CTE_Table AS

    (

    SELECT

    SUM(CASE WHEN [Date] >= @p_Date1 AND [Date] < Dateadd(day, 1, @p_Date1) THEN nip.Bytes END) AS M,

    SUM(CASE WHEN [Date] >= @p_Date2 AND [Date] < Dateadd(day, 1, @p_Date2) THEN nip.Bytes END) AS M1

    )

     

    SELECT M, M1, M-M1 AS NewCol

    FROM CTE_Table;

     

     

     

    In SQL 2000 and lower, you can create a derived table from your query like:

     

    Code Snippet

    SELECT M, M1, M-M1 AS NewCol

    FROM

    (

    SELECT

    SUM(CASE WHEN [Date] >= @p_Date1 AND [Date] < Dateadd(day, 1, @p_Date1) THEN nip.Bytes END) AS M,

    SUM(CASE WHEN [Date] >= @p_Date2 AND [Date] < Dateadd(day, 1, @p_Date2) THEN nip.Bytes END) AS M1

    ) Tab

     

     

     

    Tuesday, July 01, 2008 4:47 PM
    Moderator

All replies

  • Without reproducing the logic twice there are 2 method.  If you have SQL 2005 or Express, then use a Common Table Expression (CTE)

     

    Code Snippet

    WITH CTE_Table AS

    (

    SELECT

    SUM(CASE WHEN [Date] >= @p_Date1 AND [Date] < Dateadd(day, 1, @p_Date1) THEN nip.Bytes END) AS M,

    SUM(CASE WHEN [Date] >= @p_Date2 AND [Date] < Dateadd(day, 1, @p_Date2) THEN nip.Bytes END) AS M1

    )

     

    SELECT M, M1, M-M1 AS NewCol

    FROM CTE_Table;

     

     

     

    In SQL 2000 and lower, you can create a derived table from your query like:

     

    Code Snippet

    SELECT M, M1, M-M1 AS NewCol

    FROM

    (

    SELECT

    SUM(CASE WHEN [Date] >= @p_Date1 AND [Date] < Dateadd(day, 1, @p_Date1) THEN nip.Bytes END) AS M,

    SUM(CASE WHEN [Date] >= @p_Date2 AND [Date] < Dateadd(day, 1, @p_Date2) THEN nip.Bytes END) AS M1

    ) Tab

     

     

     

    Tuesday, July 01, 2008 4:47 PM
    Moderator
  • Thanks,

     

    That was of great help!!!!

     

    I am running into another problem.

     

    I need to calculate the min, max and median of a set of data.  The following code can calculate the min, max value but i am unable to calculate the median. any thoughts about this???

     

     

     

    SELECT MAX(AvgTime) as Maximum ,Min(AvgTime) as Minimum , Count(AvgTime) as Count, Process

    FROM (

    SELECT CONVERT(VARCHAR(13), BeginTime, 120) AS BeginDatew,

    AVG(DATEDIFF(second, BeginTime, EndTime)) AS AvgTime,

    Process

    FROM dbo.ProcessLog

    WHERE BeginTime >= @p_BeginTime AND BeginTime < @p_EndTime

    AND (Process = @p_Process OR Process = @p_Process1)

    AND DATEDIFF(second, BeginTime, EndTime) >= 1

    GROUP BY CONVERT(VARCHAR(13), BeginTime, 120), Process

    )

    TAB Group By Process

    Thursday, July 10, 2008 9:09 PM