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

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
Asked:
Rayne
  • 4
  • 3
2 Solutions
 
PortletPaulfreelancerCommented:
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 |

Open in new window

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
      WHERE DateOfUpload = '20140101'
     ) AS prev
JOIN (SELECT *
      FROM table1
      WHERE DateOfUpload = '20140105'
     ) 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

Open in new window

0
 
PortletPaulfreelancerCommented:
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
      WHERE DateOfUpload = @prev
     ) AS prev
JOIN (SELECT *
      FROM table1
      WHERE DateOfUpload = @recent
     ) AS recent
       ON prev.account = recent.account
      AND prev.product = recent.product
      AND prev.period = recent.period
;

Open in new window

0
 
RayneAuthor 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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
RayneAuthor Commented:
this is awesome, thank you again. I was stuck in a mud slide :(
0
 
PortletPaulfreelancerCommented:
:) thank you, it's my pleasure. Cheers, Paul
0
 
RayneAuthor Commented:
Thanks again Paul, this again helped after 1 year AGAIN :)
You rock
0
 
PortletPaulfreelancerCommented:
:) 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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now