Objective: Pull the last 30 days of data from a table that has a variable end date
Background: I have a table that contains purchase information but this table is only updated every two weeks therefore there's a lag in the data. Some day it can be 14 days behind and others 13 or 15 days behind.
My table contains a DATE_KEY column which joins to the DATE_DIM table on this key which is where I pull my date field from. I would use GETDATE or CURRENT_DATE but this is not appropriate in my case due to the lag.
I am using Sybase IQ and I believe I can't use a select statements in the where clause to compare dates, I got the following error:
Feature, scalar value subquery (at line 63) outside of a top level SELECT list, is not supported.
This is what I was trying to do
WHERE
TIME.[DAY] >= DATEADD(dd,-30,( SELECT
MAX([TIME1].[DAY])
FROM DB.DATE_DIM TIME1
JOIN DB.PURCHASES PURC
ON TIME1.KEY = PURC.KEY))
How can I pull the most recent 30 days of data given the constraints above?
According to the Sybase IQ documentation, you can use a comparison to a subquery, hence you could add a join to the DATE_DIM to the main FROM clause, and then compare that to a subquery similar to yours, just with the DATEADD moved into it. In the following code, I assume the alias for DATE_DIM in the main FROM clause is TIME0.
WHERE
TIME0.[DAY] >= (SELECT DATEADD(dd,-30, MAX([TIME1].[DAY]))
FROM DB.DATE_DIM TIME1
JOIN DB.PURCHASES PURC
ON TIME1.KEY = PURC.KEY
)