How to Change SQL select query As new table in SQL Server CE?

Question

This is my current SQL query:

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)

Output of above query is shown here:

morethan45    |    lessthan45
  750         |       710

And I want to create a new table like below. Is it possible to create something new like below

Column 1      | Column 2
morethan45    |    750
lessthan45    |    710

score 2 · Accepted Answer · answered Feb 07 '16 at 03:25

Same query can be altered a bit to get the result.

Instead of aggregating the pendingamount based on condition you can add a new column to define the range and use it in Group by

SELECT CASE
         WHEN Datediff(day, tbl_debit.purchasedate, Getdate()) >= 45 THEN 'morethan45'
         ELSE 'lessthan45'
       END                             AS [Column 1],
       Sum(tbl_invoices.pendingamount) AS [Column 2]
FROM   tbl_debit
       INNER JOIN tbl_invoices
               ON tbl_debit.invoice = tbl_invoices.invoice
WHERE  tbl_invoices.state = -1
GROUP  BY CASE
            WHEN ( Datediff(day, tbl_debit.purchasedate, Getdate()) >= 45 ) THEN 'morethan45'
            ELSE 'lessthan45'
          END

score 1 · Answer 2 · answered Feb 07 '16 at 03:25

Use the case in a group by:

SELECT (CASE WHEN (DATEDIFF(day, tbl_debit.purchasedate, GETDATE()) >= 45)
             THEN 'MoreThan45'
             ELSE 'LessThan45'
        END) as Column1,
       SUM(tbl_invoices.pendingamount) as Column2
FROM tbl_debit INNER JOIN
     tbl_invoices 
     ON tbl_debit.invoice = tbl_invoices.invoice  
WHERE tbl_invoices.state = -1
GROUP BY (CASE WHEN (DATEDIFF(day, tbl_debit.purchasedate, GETDATE()) >= 45)
               THEN 'MoreThan45'
               ELSE 'LessThan45'
          END);

Use into or insert to populate a table.

How to Change SQL select query As new table in SQL Server CE?

2 Answers2