I have a table Sales with the following columns –
| Emp ID | Activity Date | Sales |
|---|---|---|
| 1234 | 2024-01-01 | 254.22 |
| 1234 | 2024-05-08 | 227.10 |
| 5678 | 2023-02-01 | 254.22 |
| 5678 | 2024-05-01 | 227.10 |
I need to find the total trailing 12 months sales of each employee. Not every employee has a sale on each activity date.
The end goal is to answer business questions like – For all employees active in Jan 2024, what was the trailing 12 months sales for them?
| Emp ID | Activity Date | Sales |
|---|---|---|
| 1234 | 2024-01-01 | 254.22 |
| 1234 | 2024-05-08 | 227.10 |
| 5678 | 2023-02-01 | 254.22 |
| 5678 | 2024-05-01 | 227.10 |
From Sales table, I tried to first do a cross join with a calendar dimension table, but that is not the result I expected. The approach I was thinking was too have all calendar dates, and all employee IDs with sales (0 is no sales for that employee for that date), and then do a partition by empID for rows preceding.
select CAL.*, S.emp_id, S.ACTIVITY_DAte, S.Sales
from sales s
cross join CAL
Vertika Sharma is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.