Follow up to This

Please refer to this question -
http://www.experts-exchange.com/questions/28689681/metric-differecne-between-two-dates.html#a40831327

Hi,
I am trying to understand the logic of your code – the cross apply definition   - can you explain how you ended up with the query result?  
There are two tables T1 and T2
1.      Select the maximum value for amount for each different combination of (account +item+ month) and sort it descending.
2.      Do a Cartesian product of T1 with what we got in Step 1?
RayneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
re: 2.      Do a Cartesian product of T1 with what we got in Step 1?

With a JOIN operator such as cartesian product , both inputs (both tables) represent static relations. With APPLY, the left side query is a static relation, but the right side query can be a table expression with correlations to elements from the left table.
0
RayneAuthor Commented:
Thank you all
0
RayneAuthor Commented:
I've requested that this question be deleted for the following reason:

duplicate
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mike EghtebasDatabase and Application DeveloperCommented:
The link presented by Jim is an excellent link to get exact and quick results. It is pretty educational.

In the code from the link copied below for reference, there is a cte (myResults) basically is a subquery to the outer query following it. The cte itself also is made up a left and right queries using cross apply.

The left query here:
SELECT t1.account,
        t1.item,
        t1.[month],
        t1.amount - t2.amount AS amount_diff
FROM    #w t1

produces a static result including   t1.account to be used dynamically in the second (right side) query below. For each t1.account, the maybe one or more rows generated (exactly like cursor with old tsql techniques).

CROSS APPLY (SELECT TOP 1 t2.amount
             FROM   #w t2
             WHERE  t2.account = t1.account
             AND    t2.Item    = t1.Item
             AND    t2.[month] = t1.[month]
             AND    t2.[date]  = @Date2
             ORDER BY t2.[date] DESC) t2
WHERE   t1.[date] = @Date1

Mike
0
RayneAuthor Commented:
Jim,

thank you so much for the link. I will keep that expectation from next time.
I liked "Cheetahs with ADHD on drugs"
0
RayneAuthor Commented:
Mike,

What does this return?

SELECT TOP 1 t2.amount
             FROM   #w t2
             WHERE  t2.account = t1.account
             AND    t2.Item    = t1.Item
             AND    t2.[month] = t1.[month]
             AND    t2.[date]  = @Date2
             ORDER BY t2.[date] DESC
0
RayneAuthor Commented:
the result of above then gets cross joined to the left query result?
0
RayneAuthor Commented:
thank you
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I liked "Cheetahs with ADHD on drugs"
One of my favorites.   The original line was 'Cheetas with ADHD on Meth'.  I used that line on Vitor once, who is Swiss, and he asked me what all three meant.
0
RayneAuthor Commented:
hahahaahaha
0
Mike EghtebasDatabase and Application DeveloperCommented:
Rayne,

Below query potentially returns multiple rows but your requirement apparently is to pick a matching row with the latest date.

To do this, the [date] column is sorted descending and Top(1) is applied to it so that one record is returned which meets your criteria.

CROSS APPLY (SELECT TOP 1 t2.amount
             FROM   #w t2
             WHERE  t2.account = t1.account
             AND    t2.Item    = t1.Item
             AND    t2.[month] = t1.[month]
             AND    t2.[date]  = @Date2
             ORDER BY t2.[date] DESC) t2

BTW, you need to replace  #w with table name you are using.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperCommented:
The author is following for a solution and I am helping him.

Mike
0
Mike EghtebasDatabase and Application DeveloperCommented:
Thanks.
0
RayneAuthor Commented:
Thank you Mike for your help :)
Sorry /i was out of city. /i will review all of your links and points.
Thank you Jim was not closing this question. Mike is awesome help.
0
RayneAuthor Commented:
Thank you Mike
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.