# 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

• 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

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