Solved

What does this SQL mean?

Posted on 2016-10-03
7
57 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 42

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 42

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 42

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SqlDataBase 7 48
optimize stored procedure 6 25
VB.NET 2008 - SQL Timeout 9 24
SQL Server Import/Error Wizard error 12 17
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

770 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