How to show one column in two column base on second column in SQL Server

Question

I have a table sales with columns

Month   SalesAmount
--------------------------
4   50000
5   60000
6   70000
7   50000
8   60000
9   40000

I want result like this

From Month  To Month    Result 
-----------------------------------------------
4                  6    Increasing
6                  7    Decreasing
7                  8    Increasing
8                  9    Decreasing

without using a cursor

Answer 1

If you are using only month no in your table structure, you can try something like this

SELECT s1.month AS From_Month, 
       s2.month AS To_Month, 
       CASE 
         WHEN s2.salesamount > s1.salesamount THEN 'Increasing' 
         ELSE 'Decresing' 
       END      AS res 
FROM   sales AS s1, 
       sales AS s2 
WHERE  s1.month + 1 = s2.month 

demo at http://sqlfiddle.com/#!6/0819d/11

Answer 2

Try this. Basically, you need to join the table to itself by the month (+1), then pull the data you want/perform any calcs.

Select 
    M1.Month as [From],
    M2.Month as [To],
    Case
        When M2.SalesAmount > M1.SalesAmount Then 'Increasing'
        When M2.SalesAmount < M1.SalesAmount Then 'Decreasing'
        Else 'Holding Steady'
    End
From sales M1
    Inner Join sales M2 on M2.Month = M1.Month + 1

This works if you want the breakdown month by month. However, your example data set compresses months 4-6. Without more details on how you determine what to compress, I'm going to make the following assumptions:

• You want detailed data for the last 3 periods, and a compressed summary of all other periods.
• You wish only the overall trend between the first month and the last month inside the compressed period. i.e. you want to know the difference between the first, and the last month values.

To do that, the query starts to get more complicated. I've done it with two Unioned queries:

With 
compressed_range as
( select min([Month]) as min_month, max([Month]) - 3 as max_month from sales )
Select 
    M1.[Month] as [From], 
    M2.[Month] as [To],
    Case
        When M2.SalesAmount > M1.SalesAmount Then 'Increasing'
        When M2.SalesAmount < M1.SalesAmount Then 'Decreasing'
        Else 'Holding Steady'
    End
From sales M1 
    Inner Join sales M2 on M2.[Month] = ( select max_month from compressed_range )
Where M1.Month = ( select min_month from compressed_range )
Union All
Select 
    M1.Month as [From],
    M2.Month as [To],
    Case
        When M2.SalesAmount > M1.SalesAmount Then 'Increasing'
        When M2.SalesAmount < M1.SalesAmount Then 'Decreasing'
        Else 'Holding Steady'
    End
From sales M1
    Inner Join sales M2 on M2.Month = M1.Month + 1
Where M2.Month >= (Select max_month + 1 from compressed_range)

Answer 3

This gives your desired result:

DECLARE @T TABLE (Month INT, SalesAmount MONEY);
INSERT @T
VALUES (4, 50000), (5, 60000), (6, 70000), (7, 50000), (8, 60000), (9, 40000);

WITH CTE AS
(   SELECT  FromMonth = T2.Month,
            ToMonth = T.Month,
            Result = CASE T2.Result
                        WHEN -1 THEN 'Decreasing'
                        WHEN 0 THEN 'Static'
                        WHEN 1 THEN 'Increasing'
                    END,
            GroupingSet = ROW_NUMBER() OVER(ORDER BY T.Month) - ROW_NUMBER() OVER(PARTITION BY T2.Result ORDER BY T.Month)
    FROM    @T T
            CROSS APPLY
            (   SELECT  TOP 1 
                        T2.SalesAmount, 
                        T2.Month,
                        Result = SIGN(T.SalesAmount - T2.SalesAmount)
                FROM    @T T2
                WHERE   T2.Month < T.Month
                ORDER BY T2.Month DESC
            ) T2
)
SELECT  FromMonth = MIN(FromMonth),
        ToMonth = MAX(ToMonth),
        Result
FROM    CTE
GROUP BY Result, GroupingSet
ORDER BY FromMonth;

The first stage is to get the sales amount for the previous month each time:

SELECT  *
FROM    @T T
        CROSS APPLY
        (   SELECT  TOP 1 
                    T2.SalesAmount, 
                    T2.Month,
                    Result = SIGN(T.SalesAmount - T2.SalesAmount)
            FROM    @T T2
            WHERE   T2.Month < T.Month
            ORDER BY T2.Month DESC
        ) T2
ORDER BY T.MONTH

Will Give:

Month   SalesAmount SalesAmount Month   Result
5       60000.00    50000.00    4       1.00
6       70000.00    60000.00    5       1.00
7       50000.00    70000.00    6       -1.00
8       60000.00    50000.00    7       1.00
9       40000.00    60000.00    8       -1.00

Where Result is just an indicator of whether or not the amount has increased or decreased. You then need to apply an ordering trick whereby each member of a sequence - it's postion in the sequence is constant for sequential members. So with the above data set if we added:

RN1 = ROW_NUMBER() OVER(ORDER BY T.Month),
RN2 = ROW_NUMBER() OVER(PARTITION BY T2.Result ORDER BY T.Month)

Month   SalesAmount SalesAmount Month   Result  RN1 RN2 | RN1 - RN2
5       60000.00    50000.00    4       1.00    1   1   |     0
6       70000.00    60000.00    5       1.00    2   2   |     0
7       50000.00    70000.00    6       -1.00   3   1   |     2
8       60000.00    50000.00    7       1.00    4   3   |     1
9       40000.00    60000.00    8       -1.00   5   2   |     3

So you can see for the first 2 rows the final column RN1 - RN2 remains the same as they are both increasing, then when the result changes, the difference between these two row_numbers chnages, so creates a new group.

You can then group by this calculation (the GroupingSet column in the original query), to group your consecutive periods of increase and decrease together.

Example on SQL Fiddle