Solved

TSQL Question

Posted on 2014-04-25
7
280 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 500 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 500 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
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

707 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