Bruce Gust
asked on
What does this SQL mean?
Here's the SELECT:
( select chargegroupid from txn where accountid = 12159877 group by chargegroupid having max(posted) > dbo.ufn_lastpaperstatement before(121 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_lastpaperstatement before(121 59877, cast('2050-01-01' as date)) )
dbo.ufn.lastpaperstatmentb efore(1215 9877...
That function looks like this:
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...
( select chargegroupid from txn where accountid = 12159877 group by chargegroupid having max(posted) > dbo.ufn_lastpaperstatement
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_lastpaperstatement
dbo.ufn.lastpaperstatmentb
That function looks like this:
USE [PCAR_Data]
GO
/****** Object: UserDefinedFunction [dbo].[ufn_lastPaperStatementBefore] Script Date: 10/03/2016 09:37:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ufn_lastPaperStatementBefore]
(
@AccountID BIGINT, @dateParam DATE
)
RETURNS DATE
BEGIN
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
)
RETURN (@firstStatement)
END
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...
ASKER
Hang on, zephyr! I'm seeing something now that I hadn't seen before.
You said that the function only takes one parameter, but I'm looking at it and it looks as though it takes two:
@AccountID BIGINT, @dateParam DATE
Right?
But if that is accurate, why go to the trouble of converting 2050-01-01 into a date format if it's already a date?
You said that the function only takes one parameter, but I'm looking at it and it looks as though it takes two:
@AccountID BIGINT, @dateParam DATE
Right?
But if that is accurate, why go to the trouble of converting 2050-01-01 into a date format if it's already a date?
ASKER
And what is this:
and reversedref is null
Thanks for weighing in!
and reversedref is null
Thanks for weighing in!
There must be some field called reversedref in txn, and the query is looking for instances where that field is NULL.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you, guys!
Actually, the first part of that is a second parameter being handed off to the user defined function:
so, this is the call to the UDF:
dbo.ufn_lastpaperstatement
But the UDF only takes one parameter, so that second part is not relevant to the result returned from the UDF.