?
Solved

TSQL Question

Posted on 2014-04-25
7
Medium Priority
?
281 Views
Last Modified: 2015-06-15
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
Comment
Question by:Rayne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 2000 total points
ID: 40023917
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40023923
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
 

Author Comment

by:Rayne
ID: 40024058
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Author Comment

by:Rayne
ID: 40024076
this is awesome, thank you again. I was stuck in a mud slide :(
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40024384
:) thank you, it's my pleasure. Cheers, Paul
0
 

Author Comment

by:Rayne
ID: 40830655
Thanks again Paul, this again helped after 1 year AGAIN :)
You rock
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40831362
:) too kind. All the best, Paul
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question