• Status: Solved
• Priority: Medium
• Security: Public
• Views: 287

# TSQL Question

Hello

There is table that stores metric specific to the combination of account and product, period. The data is appended to the table  with time stamp (last column)
Now how do I do something in TSQL  - for each combination of account, product, period – get me the difference of metric1 of 5 jan to metric 1 of 1 jan. Please look excel

Thank you
eu-qestion.xlsx
0
Rayne
• 4
• 3
2 Solutions

freelancerCommented:
This result:
``````| ACCOUNT | PRODUCT | PERIOD | DIFF_METRIC1 | DIFF_METRIC2 | DIFF_METRIC3 | DIFF_METRIC4 |
|---------|---------|--------|--------------|--------------|--------------|--------------|
|     pl1 |      B1 |     p1 |           32 |          -32 |           -1 |           42 |
|     pl1 |      B1 |     p2 |           14 |          -34 |           32 |           26 |
|     pl1 |      B1 |     p3 |           37 |           20 |          -23 |           -7 |
|     pl1 |      B1 |     p4 |          -15 |          -38 |          -27 |           -4 |
|     pl1 |      B1 |     p5 |          -34 |           11 |           -6 |           39 |
|     pl1 |      B1 |     p6 |          -21 |           21 |           36 |           -6 |
|     pl1 |      B1 |     p7 |          -11 |           -6 |          -36 |          -16 |
|     pl1 |      B1 |     p8 |           -1 |           23 |           35 |           38 |
|     pl1 |      B1 |     p9 |           -2 |           29 |           11 |            0 |
|     pl1 |      B1 |    p10 |           19 |            3 |           37 |            6 |
|     pl1 |      B1 |    p11 |           33 |           15 |           39 |           30 |
|     pl1 |      B1 |    p12 |            7 |           13 |           22 |           -2 |
``````
from this query:
``````SELECT
recent.account
, recent.product
, recent.period
, prev.metric1 - recent.metric1 AS diff_metric1
, prev.metric2 - recent.metric2 AS diff_metric2
, prev.metric3 - recent.metric3 AS diff_metric3
, prev.metric4 - recent.metric4 AS diff_metric4
FROM (SELECT *
FROM table1
) AS prev
JOIN (SELECT *
FROM table1
) AS recent
ON prev.account = recent.account
AND prev.product = recent.product
AND prev.period = recent.period
;

data:

CREATE TABLE Table1
([account] varchar(3), [product] varchar(2), [Period] varchar(3), [Metric1] int, [metric2] int, [metric3] int, [metric4] int, [DateOfUpload] datetime)
;

INSERT INTO Table1
([account], [product], [Period], [Metric1], [metric2], [metric3], [metric4], [DateOfUpload])
VALUES
('pl1', 'B1', 'p1', 56, 11, 34, 45, '2014-01-01 00:00:00'),
('pl1', 'B1', 'p2', 45, 10, 35, 40, '2014-01-01 00:00:00'),
('pl1', 'B1', 'p3', 45, 42, 20, 26, '2014-01-01 00:00:00'),
('pl1', 'B1', 'p4', 25, 12, 19, 10, '2014-01-01 00:00:00'),
('pl1', 'B1', 'p5', 1, 34, 9, 40, '2014-01-01 00:00:00'),
('pl1', 'B1', 'p6', 6, 34, 39, 44, '2014-01-01 00:00:00'),
('pl1', 'B1', 'p7', 18, 15, 9, 16, '2014-01-01 00:00:00'),
('pl1', 'B1', 'p8', 9, 49, 44, 47, '2014-01-01 00:00:00'),
('pl1', 'B1', 'p9', 8, 35, 19, 40, '2014-01-01 00:00:00'),
('pl1', 'B1', 'p10', 36, 5, 44, 29, '2014-01-01 00:00:00'),
('pl1', 'B1', 'p11', 37, 48, 46, 47, '2014-01-01 00:00:00'),
('pl1', 'B1', 'p12', 32, 28, 49, 16, '2014-01-01 00:00:00'),
('pl1', 'B1', 'p1', 24, 43, 35, 3, '2014-01-05 00:00:00'),
('pl1', 'B1', 'p2', 31, 44, 3, 14, '2014-01-05 00:00:00'),
('pl1', 'B1', 'p3', 8, 22, 43, 33, '2014-01-05 00:00:00'),
('pl1', 'B1', 'p4', 40, 50, 46, 14, '2014-01-05 00:00:00'),
('pl1', 'B1', 'p5', 35, 23, 15, 1, '2014-01-05 00:00:00'),
('pl1', 'B1', 'p6', 27, 13, 3, 50, '2014-01-05 00:00:00'),
('pl1', 'B1', 'p7', 29, 21, 45, 32, '2014-01-05 00:00:00'),
('pl1', 'B1', 'p8', 10, 26, 9, 9, '2014-01-05 00:00:00'),
('pl1', 'B1', 'p9', 10, 6, 8, 40, '2014-01-05 00:00:00'),
('pl1', 'B1', 'p10', 17, 2, 7, 23, '2014-01-05 00:00:00'),
('pl1', 'B1', 'p11', 4, 33, 7, 17, '2014-01-05 00:00:00'),
('pl1', 'B1', 'p12', 25, 15, 27, 18, '2014-01-05 00:00:00')
;

http://sqlfiddle.com/#!3/129e4/4
``````
0

freelancerCommented:
if you need parameters too, then it would like something like this:
``````DECLARE @prev AS datetime, @recent AS datetime
SET @prev = '20140101'
SET @recent = '20140105'

SELECT
recent.account
, recent.product
, recent.period
, prev.metric1 - recent.metric1 AS diff_metric1
, prev.metric2 - recent.metric2 AS diff_metric2
, prev.metric3 - recent.metric3 AS diff_metric3
, prev.metric4 - recent.metric4 AS diff_metric4
FROM (SELECT *
FROM table1
) AS prev
JOIN (SELECT *
FROM table1
) AS recent
ON prev.account = recent.account
AND prev.product = recent.product
AND prev.period = recent.period
;
``````
0

Author Commented:
Portlet Paul,

You are my life saver, thank you again. You have jumped in to help several times before as well.

Thank you Sire :)
0

Author Commented:
this is awesome, thank you again. I was stuck in a mud slide :(
0

freelancerCommented:
:) thank you, it's my pleasure. Cheers, Paul
0

Author Commented:
Thanks again Paul, this again helped after 1 year AGAIN :)
You rock
0

freelancerCommented:
:) too kind. All the best, Paul
0
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.