title: Pivot query where the pivot results can be date filtered with two seperate dates?
body: <p>Here is my query below:</p>
  
  <pre><code>select employid,deprtmnt,jobtitle,[H-RT],[H-OT]
  from 
  (                  
   SELECT employid,deprtmnt,jobtitle,chekdate,UNTSTOPY,Category                  
   FROM 
   (                  
     SELECT employid,deprtmnt,jobtitle,chekdate,UNTSTOPY,payrolcd as     category              
     FROM [upr30300]    
     where DEPRTMNT like '600%'          
   ) t                  
   where chekdate = '2012-03-09' and Category in ('H-RT','H-OT')                  
  ) d             
  PIVOT
  (
    SUM(UNTSTOPY) 
    FOR Category IN ([H-RT],[H-OT])
  )p
  </code></pre>
  
  <p>The result shows columns including the two pivoted(H-RT and H-OT). I can filter by chekdate and get the results for the filter but am trying to figure out how to get two EXTRA columns showing H-RT, H-OT which would show the filtered chekdate plus 14 days.</p>
  
  <p>I was trying to work with a dateadd function but cannot figure out how to get 1 result set that includes....H-RT(period1),H-OT(period1),H-RT(period2),H-OT(period2)...would anyone have a direction or idea of how to get this done?</p>
  
  <p>Thank you in advance!</p>
  
  <p>Here is the sample data:</p>
  
  <pre><code>EMPLOYID    DEPRTMNT    JOBTITLE    H-RT    H-OT
  ABC123          600-20  CA      77.78000    7.47000
  ABC124          600-80  CSA     55.65000    0.58000
  ABC125          600-70  RAA     77.68000    0.03000
  </code></pre>
  
  <p>Here is desired data:</p>
  
  <pre><code>EMPLOYID    DEPRTMNT    JOBTITLE    H-RT1   H-OT1      H-RT2      H-OT2
  ABC123          600-20  CA      77.78000    7.47000    80.00      12.00
  ABC124          600-80  CSA     55.65000    0.58000    74.00      16.00
  ABC125          600-70  RAA     77.68000    0.03000    48.00       2.00
  </code></pre>
  
  <p>THe desired data columns H-RT1,H-OT1 is where chekdate = '2012-03-09' and columns H-RT2,H-OT2 would be where chekdate = '2012-03-09' + 14 days.</p>