Filter Date Range

Other SQL Server Topics
1

Hi Experts,
I'm attempting to obtain information from a customer transaction table's invoice_date column.

We need data on the set of clients' YTD (year to date) and PYTD (previous year to date) sales.

Example: From 01-01-2022 to 23-05-2022 and 01-01-2023 to 23-05-2023.
(The date will keep on changing)

Please, could you assist with the request?

Thank You
Yoga

2 
SELECT 
    -- Beginning of this year
    CAST(DATEADD(YEAR,DATEDIFF(YEAR,'19000101',GETDATE()),'19000101') AS DATE),
    -- Same date last year
    CAST(DATEADD(YEAR, -1, GETDATE() ) AS DATE),
    -- Beginning of last year
    CAST(DATEADD(DAY, -1, DATEADD(YEAR,DATEDIFF(YEAR,'19000101',GETDATE()),'19000101')) AS DATE)

Observe the behavior when current year or prior year is a leap year and date is Feb 28 or 29 and make sure that that is what you want.

3

For YTD or PYTD you need to calculate the first of this year, the first of last year and the current date this year and same date last year:

DECLARE @current_year = DATEADD(year, DATEDIFF(year, 0, getdate()), 0)
      , @current_date = CAST(getdate() AS date);

DECLARE @previous_year = DATEADD(year, -1, @curYearStart)
      , @previous_date = DATEADD(year, -1, @current_date);

Now, you just use those in the query:

SELECT ...
  FROM ...
 WHERE (date_column >= @current_year AND date_column < @current_date)
    OR (date_column >= @previous_year AND date_column < @previous_date)
4

Sure, I can help you with the request to obtain Year-to-Date (YTD) and Previous Year-to-Date (PYTD) sales data from a customer transaction table's invoice_date column.

To get the YTD and PYTD sales, you can use SQL queries based on the current date and previous year's date. Assuming you have a table named "transactions" with columns "customer_id", "invoice_date", and "sales_amount", here's an example SQL query for YTD and PYTD sales:

-- Calculate the current date and previous year's date
DECLARE @CurrentDate DATE = GETDATE();
DECLARE @PreviousYearDate DATE = DATEADD(YEAR, -1, @CurrentDate);

-- YTD Sales (Current Year)
SELECT 
    customer_id,
    SUM(sales_amount) AS YTD_Sales
FROM
    transactions
WHERE
    invoice_date >= DATEFROMPARTS(YEAR(@CurrentDate), 1, 1) AND
    invoice_date <= @CurrentDate
GROUP BY
    customer_id;

-- PYTD Sales (Previous Year)
SELECT 
    customer_id,
    SUM(sales_amount) AS PYTD_Sales
FROM
    transactions
WHERE
    invoice_date >= DATEFROMPARTS(YEAR(@PreviousYearDate), 1, 1) AND
    invoice_date <= @PreviousYearDate
GROUP BY
    customer_id;

Explanation:

  1. The @CurrentDate variable is used to store the current date, obtained from the GETDATE() function.
  2. The @PreviousYearDate variable is used to store the previous year's date, calculated using the DATEADD() function with a value of -1 year from the current date.
  3. For YTD Sales, the query filters the transactions that occurred from the beginning of the current year (DATEFROMPARTS(YEAR(@CurrentDate), 1, 1)) up to the current date (@CurrentDate).
  4. For PYTD Sales, the query filters the transactions that occurred from the beginning of the previous year (DATEFROMPARTS(YEAR(@PreviousYearDate), 1, 1)) up to the previous year's date (@PreviousYearDate).
  5. Both queries use the SUM() function to calculate the total sales amount for each customer and group the results by customer_id.

Please make sure to replace "transactions" with the actual name of your customer transaction table in the database. Additionally, the date format used in the example is in YYYY-MM-DD, which is the standard SQL date format. If your database uses a different date format, adjust the queries accordingly.