CODEWITHSUNDEEP
sqlsql-server-2008
SaNa3819
SaNa3819

Reputation: 347

how to separate name and add according to it?

Table called Product and columns are ID, Pname, Profit, Loss, Month. Total first six month Total=Sum(Profit)-Sum(Loss), in one column called Col1 and last six month Total=Sum(Profit)-Sum(Loss) in one column called Col2 separated by their Pname.

Sample Input:

ID  PName  Month  Profit  Loss
--  -----  -----  ------  ----
1   A      JAN    5       2
2   B      FEB    4       2
3   A      MAR    10      3
4   B      Nov    5       2

Expected Output

PName  Col1  col2
-----  ----  ----
A      10    0
B      2     3

What will be the way to do this in SQL Server?

Upvotes: 0

Views: 43

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

This seems to do the job:

declare @t table (ID int not null,PName char(1) not null,Month char(3) not null,
                  Profit int not null,Loss int not null)
insert into @t(ID,PName,Month,Profit,Loss) values
(1,'A','JAN',5,2),
(2,'B','FEB',4,2),
(3,'A','MAR',10,3),
(4,'B','Nov',5,2)

;With Halves as (
    select PName,
        CASE WHEN Month in ('Jan','Feb','Mar','Apr','May','Jun') THEN 1
            ELSE 2 END as Half,
        Profit - Loss as Net
    from @t
)
select PName,
    COALESCE([1],0) as Col1,
    COALESCE([2],0) as Col2
from Halves pivot (SUM(Net) for Half in ([1],[2])) p

I'd usually recommend against having a column called Month (since it's a keyword) and would more normally expect it to be e.g. a date column fixed on the 1st of the appropriate month (and either have a relevant year or be for a fixed year also)

I've taken one simplifying step that SUM(Profit) - SUM(Loss) is the same as SUM(Profit - Loss)

Upvotes: 2

Related Questions