By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table BillHeader(Sales INT, CreateDate DATE, Location VARCHAR(50));
Insert Into BillHeader Values
(10,'2023-01-10','L1'),
(20,'2023-01-11','L1'),
(30,'2023-01-12','L1'),
(40,'2023-01-23','L1'),
(5,'2022-12-10','L1'),
(15,'2022-12-11','L1'),
(20,'2022-12-12','L1'),
(25,'2022-12-23','L1');
8 rows affected
SELECT Location,
SUM(CASE
WHEN CreateDate >= DATEADD(Day, 1, EOMONTH(GETDATE(), -1)) AND
CreateDate < CAST(GETDATE() AS DATE)
THEN Sales END) Sum_of_Sale_1,
SUM(CASE
WHEN CreateDate >= DATEADD(Day, 1 ,EOMONTH(GETDATE(), -2)) AND
CreateDate < DATEADD(Month, -1, CAST(GETDATE() AS DATE))
THEN Sales END) Sum_of_Sale_2
FROM BillHeader
GROUP BY Location
| Location | Sum_of_Sale_1 | Sum_of_Sale_2 |
|---|---|---|
| L1 | 60 | 40 |
Warning: Null value is eliminated by an aggregate or other SET operation.
select DATEADD(Day, 1, EOMONTH(GETDATE(), -1)) d1_from,
CAST(GETDATE() AS DATE) d1_to,
DATEADD(Day, 1 ,EOMONTH(GETDATE(), -2)) d2_from,
DATEADD(Month, -1, CAST(GETDATE() AS DATE)) d2_to
| d1_from | d1_to | d2_from | d2_to |
|---|---|---|---|
| 2023-01-01 | 2023-01-22 | 2022-12-01 | 2022-12-22 |