CODEWITHSUNDEEP
sqlsql-servercaseconditional-statements
A.Papa
A.Papa

Reputation: 486

Repeat value for all same ids condition is met for one ID in the group, CASE WHEN

I would like to use case when based on a condition. However, if the condition is met for the same ID at least once then I would like to repeat the 1 for all the rows with the specific ID.

Currently I am using the below:

Select  case when REPORTING_DATE  = Convert(datetime, '2016-11-30')  then 1 else 0  end as November 
        , Reporting_Date
        , ID
from TableA
where 1=1

Resulting to 

November    Reporting_Date  ID
  0        30-9-2016        A
  0        31-10-2016       A
  1        30-11-2016       A

However I would like to repeat the 1 for all A IDs 

 November   Reporting_Date      ID
      1        30-9-2016        A
      1        31-10-2016       A
      1        30-11-2016       A
      0        30-09-2016       B  
      0        31-10-2016       B
      0        30-09-2016       C

Any ideas, I tried with partition by but it does not seem to work... Thanks in advance.

Upvotes: 1

Views: 559

Answers (1)

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can try this.

Select  MAX(case when REPORTING_DATE  = Convert(datetime, '2016-11-30')  then 1 else 0  end) OVER(PARTITION BY ID) as November 
        , Reporting_Date
        , ID
from TableA
where 1=1

Upvotes: 4

Related Questions