Complex Pivot using the existing data

Question

--ALTER PROCEDURE Energy.GetGTSTDataForManualEntry

--(

-- @Date DateTime

-- )

--AS

BEGIN

DECLARE @Date DateTime = '2024-03-29 00:00:00.000';

DECLARE @CalendarID INT = Time.GetCalendarIDByLocationCode('Energy');

DECLARE @DayPeriod Time.SqlPeriod = Time.CreatePeriodFromPlantStartDate(@CalendarID, @Date - 1, 'Day');

DECLARE @StartDate DATETIME;

DECLARE @EndDate DATETIME;

SET @StartDate = @DayPeriod.GregorianStartDate;

SET @EndDate = @DayPeriod.GregorianEndDate;

DECLARE @TABLE TABLE (

ID INT,  

StartDate DateTime,  

EndDate DateTime,  

Name NVarchar(250),  

POINTNUMBER INT,  

StartDateValue DECIMAL(38,6),  

StartDateCorected DECIMAL(38,6),  

EndDateValue DECIMAL(38,6),  

EndDateCorrected DECIMAL(38,6),  

DayValue DECIMAL(38,6),  

NetEnergy DECIMAL(38,6)  

);

WITH RankedTable AS (

SELECT   

    ROW_NUMBER() OVER (ORDER BY ES.Groups) AS RowNum,  

    --CASE WHEN ES.Groups LIKE 'GT%' THEN LEFT(ES.Groups,4)  



    -- ELSE LEFT(ES.Groups,5) END AS Name,  

	ES.Groups as Name,

    ES.POINTNUMBER,  

    MAX(CASE WHEN GSTTIME = @StartDate THEN VALUE END) AS StartDateValue,  

    MAX(CASE WHEN GSTTIME = @EndDate THEN VALUE END) AS EndDateValue,  

    MAX(CASE WHEN GSTTIME = @EndDate THEN VALUE END) - MAX(CASE WHEN GSTTIME = @StartDate THEN VALUE END) AS DayValue  

FROM   

    Energy.ScadaStations ES  

LEFT JOIN  

    Energy.HOUR_AVG_1 EH ON EH.POINTNUMBER = ES.POINTNUMBER  

WHERE   

    ES.POINTNUMBER IN (  

        79508,     

        79510,      

        79513,      

        79515,      

        79517,      

        79519,      

        79521,      

        79523,      

        79525,      

        79527,      

        79529,      

        79531,      

        79533,      

        79535,     

        79509,      

        79511,      

        79514,      

        79516,      

        79518,      

        79520,      

        79522,      

        79524,      

        79526,      

        79528,      

        79530,      

        79532,      

        79534,      

        79536      

    )  

GROUP BY   

    ES.Groups,   

    ES.POINTNUMBER  

)

INSERT INTO @TABLE (ID, StartDate, EndDate, Name, POINTNUMBER, StartDateValue, EndDateValue, DayValue)

SELECT

RowNum AS ID,  

@StartDate AS StartDate,  

@EndDate AS EndDate,  

Name as Station,  

POINTNUMBER,  

StartDateValue,  

EndDateValue,  

DayValue  

FROM

RankedTable;  

SELECT

A.ID,   

LEFT(A.Name,4) as Station,   

A.POINTNUMBER,   

**A.StartDateValue,**   

**ISNULL(A.StartDateCorected, A.StartDateValue) AS StartDateCorected,**  

**A.EndDateValue,**   

**ISNULL(A.EndDateCorrected, A.EndDateValue) AS EndDateCorrected,**  

**A.DayValue--,**  

FROM

 @TABLE A

 INNER JOIN @TABLE B ON A.ID =B.ID 

 

END

I would like to pivot the columns highlighted in bold on the basics of Station column . Please suggest a way forward

Answer 1

Fixed the issue