Need a sales $ for the past 90 days

Other SQL Server Topics
1

Hello Experts,
Need a sales $ for the past 90 days:

Table Name: Sales
Data Available: 2022, 2023 & 2024 YTD

Criteria: Approved Date (Any date from 1st Jan 2024 to till date).
The sales data need for the past 90 days from 20240111.
Ex: If, the Approved Date is 20240111, the 90th day was 2024-10-13 (Approved date - 90 days).
Now, I need to get the "sales" from 2024-10-13 to 2024-01-11.

Note: The current date format is "YYYYMMDD"

Thank You,
Yoga

1 Like
2

How to post a T-SQL question on a public forum | spaghettidba

2 Likes
3

And you tried:

where salesdate between approvedDate and (approvedDate - 90)

Are you wanting to pass in a user generated parameter?

4 

DECLARE @ApprovedDate date;
SET @ApprovedDate = ISNULL(@ApprovedDate, GETDATE());

SELECT *
FROM dbo.Sales
WHERE ApprovedDate >= CONVERT(varchar(8), DATEADD(DAY, -90, @ApprovedDate), 112) AND
    AprovedDate <= CONVERT(varchar(8), @ApprovedDate, 112)
5

Sidebar... I strongly recommend NOT storing dates as text. Just one date like that takes 8 bytes for the 2 and 2 bytes for the location offset (some people incorrectly call it the "length"... it's not a length) for a total of 10 bytes.

Storing it as a DATE datatype would only take 3 bytes and open up a world of high performance things you can do with the DATE datatype.