calculation for the multiple periods in the date range

Question

calculation for the multiple periods in the date range

s_nyanzowa

SSC Rookie

Points: 25
More actions
May 22, 2011 at 8:13 am

#250517

please help
how can i do this calculation for the whole period ( Tperiod1 to Tperiod48) without repeating these scripts 48 times?
SELECT
AI.PRODUCT,
AI.Order_Date,
CASE WHEN AI.Period01 > 0 AND POWER(RE.Period01,2) > POWER(RI.Period01,2) THEN SQRT(POWER(AI.Period01,2)+ POWER(RE.Period01,2))*2
ELSE CASE WHEN AI.Period01 > 0 AND POWER(RI.Period01,2) > POWER(RE.Period01,2) THEN SQRT(POWER(AI.Period01,2)+ POWER(RI.Period01,2))*2
ELSE 0 END
END AS 'PPD1',
CASE WHEN AI.Period02 > 0 AND POWER(RE.Period02,2) > POWER(RI.Period02,2) THEN SQRT(POWER(AI.Period02,2)+ POWER(RE.Period02,2))*2
ELSE CASE WHEN AI.Period02 > 0 AND POWER(RI.Period02,2) > POWER(RE.Period02,2) THEN SQRT(POWER(AI.Period02,2)+ POWER(RI.Period02,2))*2
ELSE 0 END
END AS 'PPD2'
FROM ##AI AI INNER JOIN ##RI RI
ON AI.PRODUCT=RI.PRODUCT AND AI.SETT_DATE=RI.SETT_DATE
INNER JOIN ##RE RE ON AI.PRODUCT=RE.PRODUCT AND AI.SETT_DATE=RE.SETT_DATE
GROUP BY
AI.PRODUCT,
AI.Order_date,
AI.Period01,
RI.Period01,
RE.Period01,
AI.Period02,
RI.Period02,
RE.Period02
HERE IS MY RESULTS
PRODUCTORDER_DATEPPD1 PPD2
112301/07/201083.7448505983.36186178

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

It might be more efficient to unpivot the data instead of comparing column by column.

You could either lookup UNPIVOT in BOL (BooksOnLine, the SQL Server help system, usually installed together with SQL Server) or you could post table def and ready to use sample data as described in the first link in my signature to get a coded example.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

s_nyanzowa SSC Rookie Points: 25 More actions · Answer 2

Thanks for your help

Here is a data sample

TABLE AI

PRODUCTMQORDER_DATETPeriod1TPeriod2TPeriod3TPeriod4TPeriod5

10023AI01/07/2010414176.545.546

TABLE RE

PRODUCTMQORDER_DATETPeriod1TPeriod2TPeriod3TPeriod4TPeriod5

10023RE01/07/2010414176.545.546

TABLE RI

PRODUCTMQORDER_DATETPeriod1TPeriod2TPeriod3TPeriod4TPeriod5

10023RI01/07/2010414176.545.546

LutzM SSC Guru Points: 107049 More actions · Answer 3

Errrmm... Did you read the article I pointed you at?

We might have a slightly different definition of "ready to use"...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

s_nyanzowa SSC Rookie Points: 25 More actions · Answer 4

sorry I didnt understand the article

-===== Create the test table

CREATE TABLE ##TBL_AI

(PRODUCT_ID INTEGER NOT NULL,

PRODUCT_CODE VARCHAR(25) NOT NULL,

ORDER_DATE DATETIME,

TPERIOD01 DECIMAL(9,4),

TPERIOD02 DECIMAL(9,4))

CREATE TABLE ##TBL_RI

(PRODUCT_ID INTEGER NOT NULL,

PRODUCT_CODE VARCHAR(25) NOT NULL,

ORDER_DATE DATETIME,

TPERIOD01 DECIMAL(9,4),

TPERIOD02 DECIMAL(9,4))

DROP TABLE ##TBL_RI

CREATE TABLE ##TBL_RE

(PRODUCT_ID INTEGER NOT NULL,

PRODUCT_CODE VARCHAR(25) NOT NULL,

ORDER_DATE DATETIME,

TPERIOD01 DECIMAL(9,4),

TPERIOD02 DECIMAL(9,4))

DROP TABLE ##TBL_RE

--===== Insert the test data into the test table

INSERT INTO ##TBL_AI

(PRODUCT_ID,PRODUCT_CODE, ORDER_DATE, TPERIOD01, TPERIOD02)

SELECT '4','AI','01/07/2010',5.1709,4.3333 UNION ALL

SELECT '4','AI','01/07/2010',5.5319,3.5633 UNION ALL

SELECT '4','AI','01/07/2010',5.5793,4.6664 UNION ALL

SELECT '4','AI','01/07/2010',5.2471,2.9900 UNION ALL

SELECT '4','AI','01/07/2010',5.1177,1.5546 UNION ALL

SELECT '4','AI','01/07/2010',5.5510,1.5443 UNION ALL

SELECT '4','AI','01/07/2010',5.5128,0.0024 UNION ALL

SELECT '4','AI','01/07/2010',5.5758,8.4538

INSERT INTO ##TBL_RI

(PRODUCT_ID,PRODUCT_CODE, ORDER_DATE, TPERIOD01, TPERIOD02)

SELECT '4','RI','01/07/2010',1.1709,4.3333 UNION ALL

SELECT '4','RI','01/07/2010',1.1319,3.1633 UNION ALL

SELECT '4','RI','01/07/2010',1.1793,4.6664 UNION ALL

SELECT '4','RI','01/07/2010',1.2471,2.9900 UNION ALL

SELECT '4','RI','01/07/2010',1.1177,1.1146 UNION ALL

SELECT '4','RI','01/07/2010',1.1110,1.1443 UNION ALL

SELECT '4','RI','01/07/2010',1.1128,0.0024 UNION ALL

SELECT '4','RI','01/07/2010',1.1718,8.4138

INSERT INTO ##TBL_RE

(PRODUCT_ID,PRODUCT_CODE, ORDER_DATE, TPERIOD01, TPERIOD02)

SELECT '4','RE','01/07/2010',0.1709,4.3333 UNION ALL

SELECT '4','RE','01/07/2010',0.0319,3.0633 UNION ALL

SELECT '4','RE','01/07/2010',0.0793,4.6664 UNION ALL

SELECT '4','RE','01/07/2010',0.2471,2.9900 UNION ALL

SELECT '4','RE','01/07/2010',0.1177,1.0046 UNION ALL

SELECT '4','RE','01/07/2010',0.0010,1.0443 UNION ALL

SELECT '4','RE','01/07/2010',0.0128,0.0024 UNION ALL

SELECT '4','RE','01/07/2010',0.0708,8.4038