?
Solved

What does this SQL mean?

Posted on 2016-10-03
7
Medium Priority
?
79 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 44

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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 44

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 29

Accepted Solution

by:
Pawan Kumar earned 1000 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 44

Assisted Solution

by:zephyr_hex (Megan)
zephyr_hex (Megan) earned 1000 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

752 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