Calculating the days between 2 dates-CodePudding

In my database, I have a column that looks like this:

screenshot

And I need to try and filter the entries in this table by whether they are 30/60/90 days from the date selected by the user.

Here is my current SQL query that I have written in SQL Server Management Studio:

SELECT 
    Account, DCBalance, (SUM(Credit) - SUM(Debit)) AS '30day' 
FROM 
    [Graceland Estates BC].[dbo].[PostGL] AS gl
INNER JOIN 
    [Graceland Estates BC].[dbo].[Client] ON gl.DrCrAccount = Client.DCLink 
WHERE 
    DrCrAccount = 15 AND AccountLink <> '2'
    AND TxDate > 2021-11-30  
GROUP BY
    Account, DCBalance

In this code (SUM(Credit) - SUM(Debit)) is the balance for that time period that needs to be printed. DrCrAccount decides which user the balance belongs to.

Does anyone know how to determine the amount (in days) from the database entry ?

CodePudding user response：

Here's one approach:

WITH some_cte AS (
  SELECT Account
       , DCBalance
       , Credit
       , Debit
       , CASE
           WHEN TxDate >= DateAdd(dd, -30, GetDate()) THEN '0-30'
           WHEN TxDate >= DateAdd(dd, -60, GetDate()) THEN '30-60'
           WHEN TxDate >= DateAdd(dd, -90, GetDate()) THEN '60-90'
           ELSE '90 '
         END AS date_period
  ...
  WHERE TxDate >= DateAdd(dd, -90, GetDate())
)
SELECT Account
     , DCBalance
     , date_period
     , Sum(Credit) - Sum(Debit) As balance
FROM   some_cte
GROUP
    BY Account
     , DCBalance
     , date_period
;