title: getting a sub result from the same sql statement
body: <p>I have the next SQL statement:</p>
  <pre><code>SELECT xType0, xType1, xType2, SUM(xAmount) AS xxAmount FROM  (
  
                              SELECT 'Wared' AS xType0, 'Wared1' AS xType1,'Parent Payements' AS xType2, 
                              TheDate AS xDate, TheAmount AS xAmount, 
                              YEAR(TheDate) AS xYear, MONTH(TheDate) AS xMonth 
                              FROM tbl_parents_payments
  
                              UNION ALL
  
                              SELECT
                              CASE 
                              WHEN IncomeType = 'Wared' OR IncomeType = 'Deon Warda' THEN 'Wared'
                              WHEN IncomeType = 'Sader' OR IncomeType = 'Tsded' THEN 'Sader'
                              END AS xType0,
                              CASE 
                              WHEN IncomeType = 'Wared' THEN 'Wared1'
                              WHEN IncomeType = 'Sader' THEN 'Sader1'
                              WHEN IncomeType = 'Deon Warda' OR IncomeType = 'Tsded' THEN IncomeType
                              END AS xType1,
                              IncomeCatName AS xType2,
                              IncomeDate AS xDate, IncomeAmount AS xAmount, 
                              YEAR(IncomeDate) AS xYear, MONTH(IncomeDate) AS xMonth 
                              FROM tbl_income
                              INNER JOIN tbl_income_cat ON tbl_income.IncomeCat = tbl_income_cat.IncomeCatID
  
                              UNION ALL
  
                              SELECT 'Sader' AS xType0, 'Sader1' AS xType1,'' AS xType2, 
                              TheDate AS xDate, TheAmount AS xAmount, 
                              YEAR(TheDate) AS xYear, MONTH(TheDate) AS xMonth 
                              FROM tbl_teachers_payments
  
                              UNION ALL
  
                              SELECT 'Sader' AS xType0, 'Mashobat' AS xType1, ThePartner AS xType2, 
                              TheDate AS xDate, TheAmount AS xAmount, 
                              YEAR(TheDate) AS xYear, MONTH(TheDate) AS xMonth 
                              FROM tbl_partners_payments
  
  
                              ) maintbl
                              WHERE xDate BETWEEN @Date1 AND @Date2
                              GROUP BY xType0, xType1, xType2
  </code></pre>
  <p>by that SQL statement I get the values between 2 dates that called (xxAmount), my question is how to use the same <strong>sub</strong> SQL statement to get the values less than a specified date to be (xxxAmount)??</p>
  <p>how to use &quot;<strong>maintbl</strong>&quot; again with new condition.??</p>