I'm doing some maintenance on our outsystems apps and I'm having some trouble understanding some of the SQL used by the devs.


We have several #SomeTable on our advanced querys.

From what I can understand from query, we are creating the table #MonthData and the #DayData on the SELECT INTOs. 

But where does the #MonthDataStats come from in this query since its a temp table?


SyntaxEditor Code Snippet

SELECT
    [DimMeterTypeId], [DimMeterId], [DimAreaId], [DimDate],
    [Value],
    RANK() OVER(ORDER BY [DimMeterTypeId], [DimMeterId], [DimAreaId]) as [Group]
INTO #MonthData
FROM (
        SELECT
            {MeterData_Real_15Min}.[DimMeterTypeId], {MeterData_Real_15Min}.[DimAreaId], {MeterData_Real_15Min}.[DimMeterId],
            {MeterData_Real_15Min}.[DimDate],
            {MeterData_Real_15Min}.[Value]
        FROM
            {MeterData_Real_15Min}
            LEFT JOIN {Area} ON {MeterData_Real_15Min}.[DimAreaId] = {Area}.[Id]
        WHERE
            {MeterData_Real_15Min}.[DimDateTime] >= @FromDate
        AND {MeterData_Real_15Min}.[DimDateTime] < @ToDate
        AND {MeterData_Real_15Min}.[DimMeterId] IS NULL 
        AND {MeterData_Real_15Min}.[DimAreaId] IS NOT NULL
        AND {MeterData_Real_15Min}.[DimMeterTypeId] = @MeterTypeId
        AND {Area}.[Is_Active] = 1
        AND {Area}.[WaterSystemId] = @WaterSystemId
        @FilterRealData_ExpandInline
        AND NOT EXISTS (
            SELECT 1 FROM {Event}
            WHERE
                {Event}.[Is_Active] = 1
                AND {Event}.[IsRemoveFromPattern] = 1
                AND {Event}.[StartDate] <= {MeterData_Real_15Min}.[DimDateTime]
                AND {Event}.[EndDate] >= {MeterData_Real_15Min}.[DimDateTime]
                AND {Event}.[MeterTypeId] = {MeterData_Real_15Min}.[DimMeterTypeId]
                AND {Event}.[MeterId] IS NULL
                AND {Event}.[AreaId] = {MeterData_Real_15Min}.[DimAreaId] 
                AND {Event}.[WaterSystemId] = @WaterSystemId
        )
) MD;

-------------------------------------------------------------------------
-- Pattern_DayMonth - MONTH
-------------------------------------------------------------------------

@MonthGroupStats_ExpandInline

-------------------------------------------------------------------------
-- Pattern_DayMonth - DAY
-------------------------------------------------------------------------
SELECT [Value], [Group]
INTO #DayData
FROM #MonthData
WHERE [DimDate] = @RefDate;

@DayGroupStats_ExpandInline

INSERT INTO {MeterData_PatternDayMonth_Incompleted} (
    [Tenant_Id], [ReportId], [DimMeterTypeId], [DimMeterId], 
    [DimAreaId], [DimDate], [P05], [P50_Median], [P95], 
    [Month_P05], [Month_P50_Median], [Month_P95]
)
SELECT
    @TenantId, @ReportId, [DimMeterTypeId], [DimMeterId], [DimAreaId], 
    @RefDate,DDS.[P05], DDS.[P50_Median], DDS.[P95], 
    MDS.[P05], MDS.[P50_Median], MDS.[P95]
FROM
    #MonthDataStats MDS
    LEFT JOIN
    #DayDataStats DDS
    ON MDS.[Group] = DDS.[Group]
    LEFT JOIN
    (
        SELECT DISTINCT [Group], [DimMeterTypeId], [DimMeterId], [DimAreaId]
        FROM #MonthData
    ) MD
    ON MDS.[Group] = MD.[Group];

-------------------------------------------------------------------------
-- DROP TEMP TABLES
-------------------------------------------------------------------------
DROP TABLE #DayData;
DROP TABLE #DayDataStats;
DROP TABLE #MonthData;
DROP TABLE #MonthDataStats

Marco Damião wrote:

I'm doing some maintenance on our outsystems apps and I'm having some trouble understanding some of the SQL used by the devs.


We have several #SomeTable on our advanced querys.

From what I can understand from query, we are creating the table #MonthData and the #DayData on the SELECT INTOs. 

But where does the #MonthDataStats come from in this query since its a temp table?


SyntaxEditor Code Snippet

SELECT
    [DimMeterTypeId], [DimMeterId], [DimAreaId], [DimDate],
    [Value],
    RANK() OVER(ORDER BY [DimMeterTypeId], [DimMeterId], [DimAreaId]) as [Group]
INTO #MonthData
FROM (
        SELECT
            {MeterData_Real_15Min}.[DimMeterTypeId], {MeterData_Real_15Min}.[DimAreaId], {MeterData_Real_15Min}.[DimMeterId],
            {MeterData_Real_15Min}.[DimDate],
            {MeterData_Real_15Min}.[Value]
        FROM
            {MeterData_Real_15Min}
            LEFT JOIN {Area} ON {MeterData_Real_15Min}.[DimAreaId] = {Area}.[Id]
        WHERE
            {MeterData_Real_15Min}.[DimDateTime] >= @FromDate
        AND {MeterData_Real_15Min}.[DimDateTime] < @ToDate
        AND {MeterData_Real_15Min}.[DimMeterId] IS NULL 
        AND {MeterData_Real_15Min}.[DimAreaId] IS NOT NULL
        AND {MeterData_Real_15Min}.[DimMeterTypeId] = @MeterTypeId
        AND {Area}.[Is_Active] = 1
        AND {Area}.[WaterSystemId] = @WaterSystemId
        @FilterRealData_ExpandInline
        AND NOT EXISTS (
            SELECT 1 FROM {Event}
            WHERE
                {Event}.[Is_Active] = 1
                AND {Event}.[IsRemoveFromPattern] = 1
                AND {Event}.[StartDate] <= {MeterData_Real_15Min}.[DimDateTime]
                AND {Event}.[EndDate] >= {MeterData_Real_15Min}.[DimDateTime]
                AND {Event}.[MeterTypeId] = {MeterData_Real_15Min}.[DimMeterTypeId]
                AND {Event}.[MeterId] IS NULL
                AND {Event}.[AreaId] = {MeterData_Real_15Min}.[DimAreaId] 
                AND {Event}.[WaterSystemId] = @WaterSystemId
        )
) MD;

-------------------------------------------------------------------------
-- Pattern_DayMonth - MONTH
-------------------------------------------------------------------------

@MonthGroupStats_ExpandInline

-------------------------------------------------------------------------
-- Pattern_DayMonth - DAY
-------------------------------------------------------------------------
SELECT [Value], [Group]
INTO #DayData
FROM #MonthData
WHERE [DimDate] = @RefDate;

@DayGroupStats_ExpandInline

INSERT INTO {MeterData_PatternDayMonth_Incompleted} (
    [Tenant_Id], [ReportId], [DimMeterTypeId], [DimMeterId], 
    [DimAreaId], [DimDate], [P05], [P50_Median], [P95], 
    [Month_P05], [Month_P50_Median], [Month_P95]
)
SELECT
    @TenantId, @ReportId, [DimMeterTypeId], [DimMeterId], [DimAreaId], 
    @RefDate,DDS.[P05], DDS.[P50_Median], DDS.[P95], 
    MDS.[P05], MDS.[P50_Median], MDS.[P95]
FROM
    #MonthDataStats MDS
    LEFT JOIN
    #DayDataStats DDS
    ON MDS.[Group] = DDS.[Group]
    LEFT JOIN
    (
        SELECT DISTINCT [Group], [DimMeterTypeId], [DimMeterId], [DimAreaId]
        FROM #MonthData
    ) MD
    ON MDS.[Group] = MD.[Group];

-------------------------------------------------------------------------
-- DROP TEMP TABLES
-------------------------------------------------------------------------
DROP TABLE #DayData;
DROP TABLE #DayDataStats;
DROP TABLE #MonthData;
DROP TABLE #MonthDataStats

Hi Marco,


Why you dont use a SP and EXEC the SP in the advanced query?


Best Regards,

FV


Solution

Hi Marco,

Check advance queries done before this one. Because since its all done in the same connection in theory the temporary table is still there and that is why is possible to reference it here.

Regards,

Marcelo

Solution

Marcelo Ferreira wrote:

Hi Marco,

Check advance queries done before this one. Because since its all done in the same connection in theory the temporary table is still there and that is why is possible to reference it here.

Regards,

Marcelo


Hi Marcelo,

Thank you for your feedback! I think I've understood where the table comes from now. One of the input parameters is an action that creates the temporary table.


Cheers