Solved

What does this SQL mean?

Posted on 2016-10-03
7
65 Views
Last Modified: 2016-10-03
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...
0
Comment
Question by:brucegust
  • 3
  • 3
7 Comments
 
LVL 43

Expert Comment

by:zephyr_hex (Megan)
ID: 41826680
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
 

Author Comment

by:brucegust
ID: 41826693
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
 

Author Comment

by:brucegust
ID: 41826694
And what is this:

and reversedref is null

Thanks for weighing in!
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 43

Expert Comment

by:zephyr_hex (Megan)
ID: 41826710
There must be some field called reversedref in txn, and the query is looking for instances where that field is NULL.
0
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 250 total points
ID: 41826729
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
 
LVL 43

Assisted Solution

by:zephyr_hex (Megan)
zephyr_hex (Megan) earned 250 total points
ID: 41826739
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
 

Author Comment

by:brucegust
ID: 41826939
Thank you, guys!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question