Here's the SELECT:
( select chargegroupid from txn where accountid = 12159877 group by chargegroupid having max(posted) > dbo.ufn_lastpaperstatement
59877, cast('2050-01-01' as date)) ) and reversedref is null )
This is actually a subquery of a much larger SELECT, but the thing that makes this significant is that it seems to be limiting the returned number of rows to a date or a range of dates and I'm trying to get my head around it.
The first part: select chargegroupid from txn where accountid = 12159877 group by chargegroupid having max(posted) >
I'm grabbing the chargegroupid that has the largest, or in this case, the most recent date which is greater than...
Breaking this down now, one piece at a time:
dbo.ufn_lastpaperstatementbefore(12159877, cast('2050-01-01' as date)) )
That function looks like this:
/****** Object: UserDefinedFunction [dbo].[ufn_lastPaperStatementBefore] Script Date: 10/03/2016 09:37:54 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER FUNCTION [dbo].[ufn_lastPaperStatementBefore]
@AccountID BIGINT, @dateParam DATE
DECLARE @firstStatement DATE
SET @firstStatement = (
select coalesce(max(cast(statement.exportdate as date)),'1950-01-01') from statement with (NOLOCK)
where accountid = @AccountID
and cast(statement.exportdate as date) < @dateParam
and statementtype = 's'
and voided is null
This function is going to give me the most recent statement based on the export date.
This last part: cast('2050-01-01' as date)) ) and reversedref is null )
I don't understand...