It's not so hard to do this transformation. Step by step:
DATEADD(DAY
, ISNULL(a.[due_days], 30) + 30
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
) < GETDATE()
means:
[dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
+ (ISNULL(a.[due_days], 30) + 30) DAYS
< GETDATE()
then we have to break the ISNULL()
into 2 cases:
[dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
+ (a.[due_days] + 30) DAYS
< GETDATE()
OR
a.[due_days] IS NULL
AND
[dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
+ (30 + 30) DAYS
< GETDATE()
which can be written as:
(a.[due_days] + 30) DAYS
< GETDATE() - [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
OR
a.[due_days] IS NULL
AND
(30 + 30) DAYS
< GETDATE() - [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
so we can use DATEDIFF()
:
(a.[due_days] + 30)
< DATEDIFF( day
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
, GETDATE()
)
OR
a.[due_days] IS NULL
AND
(30 + 30)
< DATEDIFF( day
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
, GETDATE()
)
and finally:
a.[due_days]
< DATEDIFF( day
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
, GETDATE()
) - 30
OR
a.[due_days] IS NULL
AND
30
< DATEDIFF( day
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
, GETDATE()
) - 30
Corrected, taking care of the time parts:
a.[due_days]
< DATEDIFF( day
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
, GETDATE()
) - 30
- CASE WHEN DATEADD( day
, DATEDIFF( day
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
, GETDATE()
)
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
) > GETDATE()
THEN 1 ELSE 0
END
OR
a.[due_days] IS NULL
AND
30
< DATEDIFF( day
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
, GETDATE()
) - 30
- CASE WHEN DATEADD( day
, DATEDIFF( day
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
, GETDATE()
)
, [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
) > GETDATE()
THEN 1 ELSE 0
END
You could simplify it a bit, with the use of CROSS APPLY
:
CROSS APPLY
( SELECT gdt = GETDATE(),
calc = [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
) AS c
CROSS APPLY
( SELECT diff = x.diff - CASE WHEN DATEADD( day, x.diff, c.calc ) > c.gdt
THEN 1 ELSE 0
END
FROM
( SELECT diff = DATEDIFF( day, c.calc, c.gdt) - 30
) AS x
) AS y
----
WHERE ( a.[due_days] < y.diff
OR a.[due_days] IS NULL AND 30 < y.diff
)
Best Answer
Just like the error says, you can't have aggregates in the WHERE clause. WHERE is logically before GROUP BY, so it filters rows before they are grouped, not after.
For filtering the groups, use HAVING (or push the GROUP BY into a subquery). eg