CODEWITHSUNDEEP
sqlsql-serversql-server-2012
mHelpMe
mHelpMe

Reputation: 6668

select rows where a change in column value happens

I have the following data shown below. It was produced by the following query:

;with noms as
(
    select DateS, Region, Sales from tblSalesRegion
    where Id = 'B2PM' 
)
select * from
noms as source pivot (max(Sales) for Region in ([UK],[US],[EUxUK],[JAP],[Brazil])) as pvt
order by DateS

Data:

 DateS      UK      US      EUxUK   JAP     Brazil
 2015-11-24 23634   22187   NULL    NULL    NULL
 2015-11-30 23634   22187   NULL    NULL    NULL
 2015-12-01 23634   22187   NULL    NULL    NULL
 2015-12-02 23634   22187   NULL    NULL    NULL
 2015-12-03 23634   22187   NULL    NULL    NULL
 2015-12-04 56000   22187   NULL    NULL    NULL
 2015-12-07 56000   22187   NULL    NULL    NULL
 2015-12-08 56000   22187   NULL    NULL    NULL
 2015-12-09 56000   22187   NULL    NULL    NULL
 2015-12-10 56000   10025   NULL    NULL    NULL
 2015-12-11 56000   10025   NULL    NULL    NULL
 2015-12-14 56000   10025   NULL    NULL    NULL

Below is the result I'm after. So basically when one of the values changes in one of the five columns (excluding the dateS column) I want that to be shown. Is there a way to do this in Sql? As I need the date I don't think a simple group by statement would work. Also be nice if I could change the NULL's to zeros

Result I'm looking for:

 DateS      UK      US      EUxUK   JAP     Brazil
 2015-11-24 23634   22187   0       0       0
 2015-12-04 56000   22187   0       0       0
 2015-12-10 56000   10025   0       0       0

Upvotes: 0

Views: 62

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

Seems like a simple GROUP BY is what you want:

;WITH noms AS
(
    SELECT DateS, Region, Sales 
    FROM tblSalesRegion
    WHERE Id = 'B2PM' 
) 
SELECT MIN(DateS), [UK],[US],[EUxUK],[JAP],[Brazil]
FROM ( 
   SELECT DateS, 
          COALESCE([UK], 0) AS [UK],
          COALESCE([US], 0) AS [US],
          COALESCE([EUxUK], 0) AS [EUxUK],
          COALESCE([JAP], 0) AS [JAP],
          COALESCE([Brazil], 0) AS [Brazil]
   FROM noms AS source 
   PIVOT (
      MAX(Sales) FOR Region IN ([UK],[US],[EUxUK],[JAP],[Brazil])) AS pvt
) AS t
GROUP BY [UK],[US],[EUxUK],[JAP],[Brazil] 
ORDER BY MIN(DateS)

Upvotes: 1

Related Questions