5

I have a wide table that looks like this:

Table Windspeed
Timestamp           A-004   A-005a  A-006a ... (column 161) E-009
09-Apr-19 00:00:00  13.09   13.95   10.16                   11.71
09-Apr-19 00:05:00  12.17   11.98   12.54                   10.66

I need to generate a new table in the same format, but replace the column values from a lookup table that looks like this:

Table Powercurve
WindSpeedBin    Power_WdSpd Turb_Num
...
9.5             1216.9      1
10              1362.9      1
10.5            1476.3      1
11              1597        1
11.5            1701.5      1
12              1798.3      1
12.5            1828.5      1
13              1840        1
13.5            1846.1      1
...
9.5             1211.9      2
10              1342.9      2
10.5            1436.3      2
11              1517        2
11.5            1691.5      2
12              1713.3      2
12.5            1801.5      2
13              1833.1      2
13.5            1846.1      2
...

And finally, a third table that looks like this:

Table NameLookup
TName   Tag     TNum
A-004   A04     1
A-005a  A05     2
A-006a  A06     3
A-008a  A08     4
A-009   A09     5
...
E-009   E09     162

I need to generate a table that takes the values from table 1 (Windspeed), looks up the values in table 2 (Powercurve) (round to the nearest .5 to match WindSpeedBin), using the column names to lookup the Turb_Num value. Any ideas how to go about this? the end result would look something like this:

Table Expected_Production
Timestamp           A-004   A-005a  A-006a ... (column 161) E-009
09-Apr-19 00:00:00  1840    1846.1  1528.5                  1780.6
09-Apr-19 00:05:00  1828.5  1713.3  1843.9                  1614.5
...

This is for Microsoft SQL Server 2017

7

This should work for you. At least for the entries in the PowerCurve table that I copied over, it works correctly. This should get you close. See comments in the code for more details.

Assistance from https://stackoverflow.com/questions/9873990/round-to-5-or-1-0-in-sql on how to round to nearest .5/0.

/** FIRST - we setup the data and tables we need.

    Please note that I didn't copy the entire table of dbo.PowerCurve
    across, so my output is not as complete as yours, but this should
    get you close.
    */

DROP TABLE IF EXISTS dbo.Windspeed 
DROP TABLE IF EXISTS dbo.PowerCurve
DROP TABLE IF EXISTS dbo.NameLookup 

CREATE TABLE dbo.Windspeed 
    (
    [TimeStamp] DATETIME NOT NULL
    , [A-004] DECIMAL(5,2) NOT NULL
    , [A-005a] DECIMAL(5,2) NOT NULL
    , [A-006a] DECIMAL(5,2) NOT NULL
    , [E-009] DECIMAL(5,2) NOT NULL
    )

INSERT INTO dbo.Windspeed 
([TimeStamp], [A-004], [A-005a], [A-006a], [E-009])
VALUES ('4/9/2019 00:00:00', 13.09, 13.95, 10.16, 11.71)
    , ('4/9/2019 00:05:00', 12.17, 11.98, 12.54,10.66)

CREATE TABLE dbo.PowerCurve 
    (
    WindSpeedBIN DECIMAL(5,2) NOT NULL
    , Power_Wdspd DECIMAL(10,2) NOT NULL
    , Turb_Num INT NOT NULL
    )

INSERT INTO dbo.PowerCurve 
(WindSpeedBIN, Power_Wdspd, Turb_Num)
VALUES (13.0, 1840, 1)
    , (13.5, 1846.1, 1)
    , (13.0, 1833.1, 2)
    , (13.5, 1846.1, 2)
    , (12.0, 1798.3, 1)
    , (12.5, 1828.5, 1)
    , (12.0, 1713.3, 2)
    , (12.5, 1801.5, 2)

CREATE TABLE dbo.NameLookup 
    (
    TName VARCHAR(10) NOT NULL
    , Tag VARCHAR(10) NOT NULL
    , TNum INT NOT NULL
    )

INSERT INTO dbo.NameLookup
    (TName, Tag, Tnum)
VALUES ('A-004', 'A04', 1)
    , ('A-005a', 'A05', 2)
    , ('A-006a', 'A06', 3)
    , ('A-008a', 'A08', 4)
    , ('E-009', 'E09', 5)

Get the data

/** GET THE DATA
    - CTE_Source - unpivots so we can get the TNum and PowerCurve
    - CTE_Join - looks up the TNum in dbo.NameLocation and dbo.PowerCurve
    - CTE_Pivot - pivots the data back to make the output what we want
    **/

;WITH CTE_Source AS
    (
    SELECT [TimeStamp]
        , [TName]
        , [Value]
    FROM dbo.Windspeed AS W
        UNPIVOT ([Value] FOR TName IN ([A-004], [A-005a], [A-006a], [E-009])) AS P
    )
, CTE_Join AS
    (
    --NOTE rounding answer from https://stackoverflow.com/questions/9873990/round-to-5-or-1-0-in-sql
    SELECT S.[TimeStamp]
        , S.[TName]
        , S.[Value]
        , CONVERT(DECIMAL(5,2), ROUND(S.[Value] * 2, 0)/2)  AS RoundedValue
        , NL.TNum
        , PC.Power_Wdspd
    FROM CTE_Source AS S
        LEFT OUTER JOIN dbo.NameLookup AS NL ON NL.TName = S.TName
        LEFT OUTER JOIN dbo.PowerCurve AS PC ON PC.WindSpeedBIN = CONVERT(DECIMAL(5,2), ROUND(S.[Value] * 2, 0)/2) AND PC.Turb_Num = NL.TNum 
    )
, CTE_Pivot AS
    (
    SELECT [TimeStamp]
        , MAX([A-004]) AS [A-004]
        , MAX([A-005a]) AS [A-005a]
        , MAX([A-006a]) AS [A-006a]
        , MAX([E-009]) AS [E-009]
    FROM CTE_Join AS J
        PIVOT (MAX(Power_Wdspd) FOR TName IN ([A-004], [A-005a], [A-006a], [E-009])) AS P
    GROUP BY [TimeStamp]
    )
SELECT * FROM CTE_Pivot 
| improve this answer | |
  • I was able to test this today and it works well. Thanks! I had to make some minor tweaks to the CONVERT(DECIMAL(5,2), ROUND(S.[Value] * 2, 0)/2) AS RoundedValue as I was getting the error Arithmetic overflow error converting float to data type numeric I changed it to DECIMAL(10,2) – srumelhart May 8 '19 at 16:58
  • I'm glad it worked out for you. Yeah, working with floats can be tough, wasn't sure what the datatypes were but glad you worked through it. – Jonathan Fite May 8 '19 at 17:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.