Sql Query To Sum Amount Before the Date And After Between Dates

I am looking for a SQL query to sum the amount before the date and after between dates.

I have the following table in my database. Now what I want to find out is the total sum (DebitAmount) – Sum (CreditAmount) that is before 2021 and then from 2021-01-01 to 2021-03-01:

Table Script

CREATE TABLE [Ledger](
    [BaseLedgerID] [int] IDENTITY(1,1) NOT NULL,
    [SaveDate] [datetime] NULL CONSTRAINT [DF_BaseLedger_SaveDate]  DEFAULT (getdate()),
    [Account] [nvarchar](max) NULL,
    [DebitAmount] [decimal](18, 2) NULL CONSTRAINT [DF_BaseLedger_DebitAmount]  DEFAULT ((0)),
    [CreditAmount] [decimal](18, 2) NULL CONSTRAINT [DF_BaseLedger_CreditAmount]  DEFAULT ((0)),
 CONSTRAINT [PK_BaseLedger] PRIMARY KEY CLUSTERED 
(
    [BaseLedgerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]




INSERT Ledger (  [SaveDate],[Account], [DebitAmount], [CreditAmount]) VALUES ('2019-01-01','Sale',500,0)
INSERT Ledger (  [SaveDate],[Account], [DebitAmount], [CreditAmount]) VALUES ('2019-02-01','Sale',600,0)
INSERT Ledger (  [SaveDate],[Account], [DebitAmount], [CreditAmount]) VALUES ('2019-03-01','Sale',800,0)

INSERT Ledger (  [SaveDate],[Account], [DebitAmount], [CreditAmount]) VALUES ('2020-01-01','Sale',1200,0)
INSERT Ledger (  [SaveDate],[Account], [DebitAmount], [CreditAmount]) VALUES ('2020-02-01','Sale',200,0)
INSERT Ledger (  [SaveDate],[Account], [DebitAmount], [CreditAmount]) VALUES ('2020-03-01','Sale',300,0)

INSERT Ledger (  [SaveDate],[Account], [DebitAmount], [CreditAmount]) VALUES ('2021-01-01','Sale',2000,0)
INSERT Ledger (  [SaveDate],[Account], [DebitAmount], [CreditAmount]) VALUES ('2021-02-01','Sale',2500,0)
INSERT Ledger (  [SaveDate],[Account], [DebitAmount], [CreditAmount]) VALUES ('2021-03-01','Sale',450,0)

The Query I Have tried

-------------Previous Years
SELECT        Account, SUM(DebitAmount) - SUM(CreditAmount) AS Amount
FROM            Ledger WHERE CAST(SaveDate AS Date) < '2021-01-01'
Group by Account

Union

---------Curent Year
SELECT        Account, SUM(DebitAmount) - SUM(CreditAmount) AS Amount
FROM            Ledger WHERE CAST(SaveDate AS Date) BETWEEN '2021-01-01' AND '2021-03-01'
Group by Account

The Result is in two row but i want to be sum in one row and if possible to do not use Union

+---------+---------+
| Account | Amount  |
+---------+---------+
| Sale    | 3600.00 |
| Sale    | 4950.00 |
+---------+---------+

Answer

I would suggest putting the value in two separate columns. If you want the result by account:

SELECT Account,
       (SUM(CASE WHEN SaveDate < '2021-01-01' THEN DebitAmount ELSE 0 END) -
        SUM(CASE WHEN SaveDate < '2021-01-01' THEN CreditAmount ELSE 0 END)
      ) AS Amount_Before,
       (SUM(CASE WHEN SaveDate BETWEEN '2021-01-01' AND '2021-03-01' THEN DebitAmount ELSE 0 END) -
        SUM(CASE WHEN SaveDate BETWEEN '2021-01-01' AND '2021-03-01' THEN CreditAmount ELSE 0 END)
      ) AS Amount_After
FROM Ledger 
GROUP BY Account;

If you want totals, then remove Account from the SELECT and remove the GROUP BY.

Advertisement

Answer