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
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
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)ASKER
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
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
ASKER
?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Sire :)
ASKER
Hi I posted my question here? can you please help?
https://www.experts-exchange.com/questions/28689975/Follow-up-to-This.html
https://www.experts-exchange.com/questions/28689975/Follow-up-to-This.html
ASKER
was not sure of the code
ASKER
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?
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?
ASKER
ASKER
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
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
ASKER