How to return values of two date ranges, from the same date column and value column, in two differen-CodePudding

I have following data in my table,

Table = BillHeader

   Sales column = Sales

   Date column = CreateDate

   Location name = Location

Result needed:

Location	Sum_of_Sale_1	Sum_of_Sale_2

Sum_of_Sale_1 = Sum of Sales up to yesterday for this month.

Sum_of_Sale_2 = Sum of Sales up to same date range as Sum_of_Sale_1 during last month.

For example, if today is 20th of June, Sum_of_Sale_1 = Sum of sales from 1st June to 19th of June

and Sum_of_Sale_2 = Sum of sales from 1st May to 19th of May.

Basically what I need is these two results of different date ranges, which should be selected form the same three columns, should appear next to each other in the result. I want to know how the sales performance was last month's same date range as to this month's date range (up to yesterday for this month).

Thanks!!

SELECT AmountToBePaid as Sum_of_Sale_1

FROM BillHeader

where CreateDate between '2022-09-01' and '2022-9-02'

UNION

SELECT AmountToBePaid as Sum_of_Sale_2

FROM BillHeader

where CreateDate between '2022-06-01' and '2022-6-02'

I chose a fixed date range to figure things out first.

CodePudding user response：

Considering a sales CreateDate is likely of type Datetime or Datetime2, a safe approach would be:

DECLARE @yesterday DATE = GETDATE();
DECLARE @lastMonth DATE = DATEADD(MONTH, -1, @yesterday);
DECLARE @firstDayOfThisMonth DATE = DATEADD(DAY, 1 - DAY(@yesterday), @yesterday);
DECLARE @firstDayOfLastMonth DATE = DATEADD(DAY, 1 - DAY(@lastMonth), @lastMonth);

SELECT @yesterday,
       @firstDayOfThisMonth,
       @lastMonth,
       @firstDayOfLastMonth;

SELECT [locationId],
       SUM(   CASE
                  WHEN CreateDate >= @firstDayOfThisMonth
                       AND CreateDate < @yesterday THEN
                      AmountToBePaid
              END
          ) AS Sum_of_Sale_1,
       SUM(   CASE
                  WHEN CreateDate >= @firstDayOfLastMonth
                       AND CreateDate < @lastMonth THEN
                      AmountToBePaid
              END
          ) AS Sum_of_Sale_2
FROM BillHeader
GROUP BY [locationId];

EDIT: Note that in dates like March 31,30 previous month's end date could be Feb 28, 29.

CodePudding user response：

You could use conditional aggregation with the following date functions:

DATEADD(Day, 1, EOMONTH(GETDATE(), -1)) gets the first date of the current month, i.e. current month is Jan-2023 it will return '2023-01-01'.

CAST(GETDATE() AS DATE) gets today's date.

DATEADD(Day, 1, EOMONTH(GETDATE(), -2)) gets the first date of the previous month, i.e. current month is Jan-2023 it will return '2022-12-01'.

DATEADD(Month, -1, CAST(GETDATE() AS DATE) gets the date of the day one-month pre today's date.

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

See demo