title: Select Case FROM Union
body: <p>In a purpose of reporting and creating report (MS SQL SERVER 2014) which will compare number of transactions and amounts from last day, same day last week and same day last month I need some query like this one, which is not working:</p>
  
  <pre><code>Select 
     Acquirer = case when Date = @Day then a.Acquirer
                     when Date = @LastWeekDay then b.Acquirer
                     when Date = @LastMonthDay then c.Acquirer
                end
  ,  RC =       case when Date = @Day then a.RC
                     when Date = @LastWeekDay then b.RC
                     when Date = @LastMonthDay then c.RC
                end
  , Desc =      case when Date = @Day then a.Desc
                     when Date = @LastWeekDay then b.Desc
                     when Date = @LastMonthDay then c.Desc
                end
  , Day_Tr_Count =  case when Date = @Day then a.Count end
  , LWD_Tr_Count =  case when Date = @LastWeekDay then b.Count end
  , LMD_Tr_Count =  case when Date = @LastMonthDay then c.Count end
  , Day_Tr_Amount =  case when Date = @Day then a.Amount end
  , LWD_Tr_Amount =  case when Date = @LastWeekDay then b.Amount end
  , LMD_Tr_Amount =  case when Date = @LastMonthDay then c.Amount end
  
  from
    ( select * from T1
       union
      select * from T2
       union
      select * from T3) x
  group by Acquirer,RC,Desc
  </code></pre>
  
  <p>All 3 tables have pretty same structure: </p>
  
  <pre><code>T1,T2,T3(acquirer,RC,Desc,Count,Amount,Date).
  </code></pre>
  
  <p>Report should be:</p>
  
  <pre><code>result_table(Acquirer,RC,Desc,Daily_Count,Last_Week_Day_Count,Last_Month_Count,Daily_Amount,Last_Week_Day_Amount,Last_Month_Day_Amount)
  </code></pre>