getting a sub result from the same sql statement

Question

I have the next SQL statement:

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

by that SQL statement I get the values between 2 dates that called (xxAmount), my question is how to use the same sub SQL statement to get the values less than a specified date to be (xxxAmount)??

how to use "maintbl" again with new condition.??

Answer 1

SELECT xType0, xType1, xType2, xMonth, xYear, xAmount FROM (
SELECT 
    CASE
        WHEN TheDate < '2021-01-01' THEN 'Wared Sabiq'
        WHEN TheDate > '2021-01-31' THEN 'Wared Lahik'    
        WHEN TheDate BETWEEN '2021-01-01' AND '2021-01-31' THEN 'Wared1'
    END AS xType0,
    CASE
        WHEN TheDate < '2021-01-01' THEN 'Wared1'
        WHEN TheDate > '2021-01-31' THEN 'Wared2'    
        WHEN TheDate BETWEEN '2021-01-01' AND '2021-01-31' THEN 'Wared2'
    END AS xType1,
    'Payment' AS xType2, 
    TheDate AS xDate, SUM(TheAmount) AS xAmount, 
    YEAR(TheDate) AS xYear, MONTH(TheDate) AS xMonth 
    FROM tbl_parents_payments
    GROUP BY xType0, xType1, xType2, xMonth, xYear

UNION ALL

SELECT
    CASE 
        WHEN (IncomeType = 'Wrd' OR IncomeType = 'Wareda') AND IncomeDate < '2021-01-01' THEN 'Wared Sabiq'
        WHEN (IncomeType = 'Wrd' OR IncomeType = 'Wareda') AND IncomeDate > '2021-01-31' THEN 'Wared Lahik'
        WHEN (IncomeType = 'Wrd' OR IncomeType = 'Wareda') AND IncomeDate BETWEEN '2021-01-01' AND '2021-01-31' THEN 'Wared1'
        WHEN (IncomeType = 'sdr' OR IncomeType = 'Wareda tas') AND IncomeDate < '2021-01-01' THEN 'Sadir Sabik'
        WHEN (IncomeType = 'sdr' OR IncomeType = 'Wareda tas') AND IncomeDate > '2021-01-31' THEN 'Sadir Lahik'
        WHEN (IncomeType = 'sdr' OR IncomeType = 'Wareda tas') AND IncomeDate BETWEEN '2021-01-01' AND '2021-01-31' THEN 'Sader2'
    END AS xType0,
    CASE 
        WHEN (IncomeType = 'Wrd') AND IncomeDate < '2021-01-01' THEN 'Wared2'
        WHEN (IncomeType = 'Wrd') AND IncomeDate > '2021-01-31' THEN 'Wared2'
        WHEN (IncomeType = 'Wrd') AND IncomeDate BETWEEN '2021-01-01' AND '2021-01-31' THEN 'Wared2'
        WHEN (IncomeType = 'sdr') AND IncomeDate < '2021-01-01' THEN 'Sader1'
        WHEN (IncomeType = 'sdr') AND IncomeDate > '2021-01-31' THEN 'Sader1'
        WHEN (IncomeType = 'sdr') AND IncomeDate BETWEEN '2021-01-01' AND '2021-01-31' THEN 'Sader2'
        WHEN (IncomeType = 'Wareda' OR IncomeType = 'Wareda tas') AND IncomeDate < '2021-01-01' THEN CONCAT(IncomeType, ' sabik')
        WHEN (IncomeType = 'Wareda' OR IncomeType = 'Wareda tas') AND IncomeDate > '2021-01-31' THEN CONCAT(IncomeType, ' Lahik')
        WHEN (IncomeType = 'Wareda' OR IncomeType = 'Wareda tas') AND IncomeDate BETWEEN '2021-01-01' AND '2021-01-31' THEN                     CONCAT(IncomeType, ' tm')
    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
    GROUP BY xType0, xType1, xType2, xMonth, xYear
    
UNION ALL

SELECT 
    CASE
        WHEN TheDate < '2021-01-01' THEN 'Sadir Sabik'
        WHEN TheDate > '2021-01-31' THEN 'Sadir Lahik'    
        WHEN TheDate BETWEEN '2021-01-01' AND '2021-01-31' THEN 'tm'
    END AS xType0,
    CASE
        WHEN TheDate < '2021-01-01' THEN 'tm'
        WHEN TheDate > '2021-01-31' THEN 'tm'    
        WHEN TheDate BETWEEN '2021-01-01' AND '2021-01-31' THEN 'Sader2'
    END AS xType1,
    'ttm' AS xType2, 
    TheDate AS xDate, SUM(TheAmount) AS xAmount, 
    YEAR(TheDate) AS xYear, MONTH(TheDate) AS xMonth 
    FROM tbl_teachers_payments
    GROUP BY xType0, xType1, xType2, xMonth, xYear
    
UNION ALL

SELECT 
    CASE
        WHEN TheDate < '2021-01-01' THEN 'Sadir Sabik'
        WHEN TheDate > '2021-01-31' THEN 'Sadir Lahik'    
        WHEN TheDate BETWEEN '2021-01-01' AND '2021-01-31' THEN 'tm'
    END AS xType0,
    CASE
        WHEN TheDate < '2021-01-01' THEN 'mashobat'
        WHEN TheDate > '2021-01-31' THEN 'mashobat'    
        WHEN TheDate BETWEEN '2021-01-01' AND '2021-01-31' THEN 'mashobat'
    END AS xType1,
    ThePartner AS xType2, 
    TheDate AS xDate, SUM(TheAmount) AS xAmount, 
    YEAR(TheDate) AS xYear, MONTH(TheDate) AS xMonth 
    FROM tbl_partners_payments
    GROUP BY xType0, xType1, xType2, xMonth, xYear
    
) xmaintbl

ORDER BY xType0 ASC
    

Answer 2

You could stored all your unions results into a CTE(Common table expression), then use your different conditions next. and choose your desired result in the end. for example

;WITH maintbl AS
(
    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
),
DateBetween AS
(
    SELECT xType0, xType1, xType2, SUM(xAmount) AS xxAmount 
    FROM
    manitbl
    WHERE xDate BETWEEN @Date1 AND @Date2
    GROUP BY xType0, xType1, xType2
),
DateBefore AS
(
    SELECT xType0, xType1, xType2, SUM(xAmount) AS xxAmount 
    FROM
    manitbl
    WHERE xDate <= @Date1
    GROUP BY xType0, xType1, xType2
)
SELECT * FROM DateBefore ----DateBetween