metric differecne between two dates

Hello,

There is a a table in SQL server that is kind of a history table.
There is always an amount specific to the combination of account + Item + month
Now a report runs every day that puts the day’s date  into this history table along with other information.
 So for a given combination of (account + item + month) on a specific date, amount can change from date to date. See excel.

Now I want to do a tsql code that does this:
For date = 2/2 and date = 2/1, it grabs the difference of the [amount] for the same combination of (account + Item + month) and ONLY displays the top 2 highest and top two lowest [amount] differences for any combination of (account + Item + month)
tsql-magic.xlsx
RayneAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

RayneAuthor Commented:
So grab the top 2 maximum positive and bottom 2 minimum negative amount for the difference for [amount] pertaining to a given  combination of account + item + month and show it
dsackerContract ERP Admin/ConsultantCommented:
Here is a working solution using a temporary table created from the data you provided in the Excel spreadsheet:

DECLARE @Table TABLE (
    account     varchar(4)      NOT NULL,
    Item        varchar(2)      NOT NULL,
    [month]     varchar(2)      NOT NULL,
    amount      smallint        NOT NULL,
    [date]      date            NOT NULL )

INSERT INTO @Table VALUES ('ac1', 'i1', 'm1', 100, '2015-02-01')
INSERT INTO @Table VALUES ('ac1', 'i1', 'm1', 400, '2015-02-02')
INSERT INTO @Table VALUES ('ac1', 'i2', 'm1', 200, '2015-02-01')
INSERT INTO @Table VALUES ('ac1', 'i2', 'm1', 100, '2015-02-02')
INSERT INTO @Table VALUES ('ac1', 'i1', 'm2', 600, '2015-02-01')
INSERT INTO @Table VALUES ('ac1', 'i1', 'm2', 700, '2015-02-02')
INSERT INTO @Table VALUES ('ac2', 'i2', 'm1', 200, '2015-02-01')
INSERT INTO @Table VALUES ('ac2', 'i2', 'm1', 50, '2015-02-02')

;WITH myResults AS
(
SELECT  t1.account,
        t1.item,
        t1.[month],
        t1.amount - t2.amount AS amount_diff
FROM    @Table t1
CROSS APPLY (SELECT TOP 1 t2.amount
             FROM   @Table t2
             WHERE  t2.account = t1.account
             AND    t2.Item    = t1.Item
             AND    t2.[month] = t1.[month]
             AND    t2.[date]  < t1.[date]
             ORDER BY t2.[date] DESC) t2
)
SELECT TOP 2 *
FROM   myResults
WHERE  amount_diff = (SELECT MAX(amount_diff) FROM myResults)
OR     amount_diff = (SELECT MIN(amount_diff) FROM myResults)

Open in new window

RayneAuthor Commented:
Thank you so much for your help :)
But the history table will have several different dates, multiple dates. So your query will know to always compare between two specific dates – like if I say give me the difference of metrics between date1 = 2/29 and date2=3/1. I would like to compare only two specific dates/ days only >> of my liking
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

RayneAuthor Commented:
?
dsackerContract ERP Admin/ConsultantCommented:
Ah, in that case, you'll want to run this as a script. Eventually you may want to create a stored procedure from this, but you can copy/paste this into SSMS, play with the dates, and see if you like it.

DECLARE @Date1  date,
        @Date2  date

SET @Date1 = '2015-02-02'
SET @Date2 = '2015-02-01'   -- always put the lesser date here

DECLARE @Table TABLE (
    account     varchar(4)      NOT NULL,
    Item        varchar(2)      NOT NULL,
    [month]     varchar(2)      NOT NULL,
    amount      smallint        NOT NULL,
    [date]      date            NOT NULL )

INSERT INTO @Table VALUES ('ac1', 'i1', 'm1', 100, '2015-02-01')
INSERT INTO @Table VALUES ('ac1', 'i1', 'm1', 400, '2015-02-02')
INSERT INTO @Table VALUES ('ac1', 'i2', 'm1', 200, '2015-02-01')
INSERT INTO @Table VALUES ('ac1', 'i2', 'm1', 100, '2015-02-02')
INSERT INTO @Table VALUES ('ac1', 'i1', 'm2', 600, '2015-02-01')
INSERT INTO @Table VALUES ('ac1', 'i1', 'm2', 700, '2015-02-02')
INSERT INTO @Table VALUES ('ac2', 'i2', 'm1', 200, '2015-02-01')
INSERT INTO @Table VALUES ('ac2', 'i2', 'm1',  50, '2015-02-02')

;WITH myResults AS
(
SELECT  t1.account,
        t1.item,
        t1.[month],
        t1.amount - t2.amount AS amount_diff
FROM    @Table t1
CROSS APPLY (SELECT TOP 1 t2.amount
             FROM   @Table 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
)
SELECT TOP 2 *
FROM   myResults
WHERE  amount_diff = (SELECT MAX(amount_diff) FROM myResults)
OR     amount_diff = (SELECT MIN(amount_diff) FROM myResults)

Open in new window

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
RayneAuthor Commented:
Thank you Sire :)
RayneAuthor Commented:
Hi I posted my question here? can you please help?
http://www.experts-exchange.com/questions/28689975/Follow-up-to-This.html
RayneAuthor Commented:
was not sure of the code
RayneAuthor Commented:
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?
RayneAuthor Commented:
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
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.