Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

T-SQL Calculation Expand / Collapse
Author
Message
Posted Thursday, May 8, 2014 8:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 292, Visits: 1,037
Hi,


I have a table where I need to calculate Trend based on Week Type and Unit .


CREATE TABLE #REport
(
TYPE VARCHAR(20),
Unit VARCHAR(20),
Forecast INT,
Week INT,
Trend NUMERIC(9,2)
)

INSERT INTO #REport( TYPE, Unit, Forecast, Week, Trend )
SELECT 'Net Revenue','XXX',0,1,0
UNION
SELECT 'Net Revenue','XXX',0,2,0
UNION
SELECT 'Net Revenue','XXX',0,3,0
UNION
SELECT 'Net Revenue','XXX',0,4,0
UNION
SELECT 'Net Revenue','XXX',105000,5,0
UNION
SELECT 'Invest','XXX',0,1,0
UNION
SELECT 'Invest','XXX',0,2,0
UNION
SELECT 'Invest','XXX',0,3,0
UNION
SELECT 'Invest','XXX',0,4,0
UNION
SELECT 'Invest','XXX', 45951 ,5,0

SELECT * FROM #REport
DROP TABLE #REport

--FOR every TYPE AND Unit, the trend should be calculated AS follows :

--FOR Week 1 OF TYPE = 'Invest' and Unit = 'XXX'
--Trend = 45951/MAX week number = 45951/5 = 9190.2

--FOR Week 2 OF TYPE = 'Invest' and Unit = 'XXX'
--Trend = 9190.2 + 45951/5 = 18380.4-- Trend of Week 1 + 45951/5


--FOR Week 3 OF TYPE = 'Invest' and Unit = 'XXX'
--Trend = 18380.4 + 45951/5 = 27570.6-- Trend of Week 2 + 45951/5

--FOR Week 4 OF TYPE = 'Invest' and Unit = 'XXX'
--Trend = 27570.6 + 45951/5 = 36760.8-- Trend of Week 3 + 45951/5


--FOR Week 5 OF TYPE = 'Invest' and Unit = 'XXX'
--Trend = 36760.8 + 45951/5 = 45951-- Trend of Week 4 + 45951/5


Thanks,
PSB
Post #1568920
Posted Thursday, May 8, 2014 8:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:11 PM
Points: 1,886, Visits: 18,547
just to be sure.....can you please provide the actual results you require based on your sample data......and not the calcs.

ta.


__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #1568937
Posted Thursday, May 8, 2014 8:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 292, Visits: 1,037
Desired result ..

SELECT 'Invest' AS TYPE,'XXX' AS Unit,0 AS ForeCast,1 AS Week,9190.2 AS Trend
UNION
SELECT 'Invest' AS Type,'XXX' AS Unit,0 AS ForeCast,2 AS Week,18380.4 AS Trend
UNION
SELECT 'Invest' AS TYPE,'XXX' AS Unit,0 AS ForeCast,3 AS Week,27570.6 AS Trend
UNION
SELECT 'Invest' AS Type,'XXX' AS Unit,0 AS ForeCast,4 AS Week,36760.8 AS Trend
UNION
SELECT 'Invest' AS Type,'XXX' AS Unit, 45951 AS ForeCast,5 AS Week,45951 AS Trend
Post #1568943
Posted Thursday, May 8, 2014 8:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 7,129, Visits: 13,510
Otherwise you will get answers like this:

SELECT *, Trend = ([Week]/5.0) * MAX(Forecast) OVER(PARTITION BY [TYPE])
FROM #REport

Which exactly meets your requirements (apart from the trivial matter of rounding) but is almost certainly incorrect.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1568946
Posted Thursday, May 8, 2014 9:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:11 PM
Points: 1,886, Visits: 18,547
ChrisM@Work (5/8/2014)
Otherwise you will get answers like this:

SELECT *, Trend = ([Week]/5.0) * MAX(Forecast) OVER(PARTITION BY [TYPE])
FROM #REport

Which exactly meets your requirements (apart from the trivial matter of rounding) but is almost certainly incorrect.


Chris ...your mind reading skills are quite extraordinary
but have a suspicion that OP has oversimplified........we can wait and see


__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #1568952
Posted Thursday, May 8, 2014 9:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 7,129, Visits: 13,510
J Livingston SQL (5/8/2014)
ChrisM@Work (5/8/2014)
Otherwise you will get answers like this:

SELECT *, Trend = ([Week]/5.0) * MAX(Forecast) OVER(PARTITION BY [TYPE])
FROM #REport

Which exactly meets your requirements (apart from the trivial matter of rounding) but is almost certainly incorrect.


Chris ...your mind reading skills are quite extraordinary
but have a suspicion that OP has oversimplified........we can wait and see


J Livingstone - you give me way too much credit for a Wild Guess


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1568975
Posted Thursday, May 8, 2014 11:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 292, Visits: 1,037
Thanks for the solution! it works perfectly .
Post #1569036
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse