How to merge two SQL SELECTqueries into one query in SQL Server CE?

Question

I have two SELECT queries, those return more than 45 and less than 45. I need to merge these 2 queries into one. If it is possible, how can I achieve it?

Query 1:

SELECT 
    SUM(tbl_invoices.pendingamount) AS morethan45 
FROM
    tbl_debit 
INNER JOIN 
    tbl_invoices ON tbl_debit.invoice = tbl_invoices.invoice  
WHERE 
    (tbl_invoices.state = - 1) 
    AND (DATEDIFF(day, tbl_debit.purchasedate, GETDATE()) >= 45)

Query 2:

SELECT 
    SUM(tbl_invoices.pendingamount) AS lessthan45
FROM 
    tbl_debit 
INNER JOIN 
    tbl_invoices ON tbl_debit.invoice = tbl_invoices.invoice  
WHERE 
    (tbl_invoices.state = - 1) 
    AND (DATEDIFF(day, tbl_debit.purchasedate, GETDATE()) < 45)

I tried following query but it returns an error.

SELECT (Query 1),(Query 2)

Please help me to solve this issue.

Answer 1

You should be able to use CASE, something like;

SELECT 
  SUM(CASE WHEN (DATEDIFF(day, tbl_debit.purchasedate, GETDATE()) >= 45)
           THEN tbl_invoices.pendingamount ELSE 0 END) AS morethan45,
  SUM(CASE WHEN (DATEDIFF(day, tbl_debit.purchasedate, GETDATE()) < 45)
           THEN tbl_invoices.pendingamount ELSE 0 END) AS lessthan45
FROM tbl_debit 
INNER JOIN tbl_invoices 
  ON tbl_debit.invoice = tbl_invoices.invoice  
WHERE (tbl_invoices.state = - 1)