What does this SQL mean?

Here's the SELECT:

 ( select chargegroupid from txn where accountid = 12159877 group by chargegroupid having max(posted) > dbo.ufn_lastpaperstatementbefore(12159877, 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)) )

dbo.ufn.lastpaperstatmentbefore(12159877...

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

Open in new window


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...
brucegustPHP DeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Try this.

1. cast('2050-01-01' as date) -- This is correct because you are passing this value to a date parameter. Without casting it will be considered as a string. This value will be received by the 2nd parameter of the function.


2. Function takes 2 parameters - One Bigint and 2nd one is a Date. Both are compulsory parameters to pass. In you case also you are passing 2 parameters

dbo.ufn_lastpaperstatementbefore(12159877, cast('2050-01-01' as date))




3. reversedref is null  -- This is a filter in the having clause. It has nothing to do with the function. It will come into the picture after group by. It is basically part of the SELECT query.


Note we are just appending the function's code to the SELECT query.

Please let me know in case more questions.

Enjoy!
0
 
zephyr_hex (Megan)DeveloperCommented:
This last part: cast('2050-01-01' as date)) ) and reversedref is null )

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_lastpaperstatementbefore(12159877, cast('2050-01-01' as date))

But the UDF only takes one parameter, so that second part is not relevant to the result returned from the UDF.
0
 
brucegustPHP DeveloperAuthor Commented:
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?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
brucegustPHP DeveloperAuthor Commented:
And what is this:

and reversedref is null

Thanks for weighing in!
0
 
zephyr_hex (Megan)DeveloperCommented:
There must be some field called reversedref in txn, and the query is looking for instances where that field is NULL.
0
 
zephyr_hex (Megan)Connect With a Mentor DeveloperCommented:
I mis-spoke when I said the UDF does nothing with the second parameter.  I missed the definition of the second parameter because it's on the same line as the first parameter:

ALTER FUNCTION [dbo].[ufn_lastPaperStatementBefore]
    (
      @AccountID BIGINT, @dateParam DATE
    )

Open in new window


And it's used here to limit the results from the UDF:

cast(statement.exportdate as date) < @dateParam

Open in new window


So the UDF will only return results prior to that date that's passed in.
0
 
brucegustPHP DeveloperAuthor Commented:
Thank you, guys!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.