Solved

What does this SQL mean?

Posted on 2016-10-03
7
71 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

732 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