SWQL Edit to Combine Rows into Singular Row

Hello,

I have the following SWQL and screenshot below for context. The current output takes the average availability for the past 3 months and creates a single row for each. How could I edit my current SWQL to combine those rows into one row for each of my sites? 

Output:

SWQL:

SELECT
[data].[DisplayName] AS [Site]
, [data].[ContainerStatus].[PercentAvailability] AS [PercentAvailability]
, [data].[InstanceSiteId] AS [InstanceSiteId]
, Case
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 1 Then 'Jan'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 2 Then 'Feb'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 3 Then 'Mar'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 4 Then 'Apr'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 5 Then 'May'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 6 Then 'Jun'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 7 Then 'Jul'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 8 Then 'Aug'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 9 Then 'Sep'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 10 Then 'Oct'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 11 Then 'Nov'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 12 Then 'DEC'
Else ''
End as [Month]
, Round(Avg(([data].[ContainerStatus].PercentAvailability)*1.00), 3) as [Average Percent Availability]

FROM orion.groups AS data
WHERE
([data].[CustomProperties].[Business_Unit_Groups]) = ('X')
And MONTHDIFF( DateTrunc('Month', [data].[ContainerStatus].DateTime), Getdate() ) < 3
Group By ContainerID, DateTrunc('Month', [data].[ContainerStatus].DateTime)
Order By ContainerID, DateTrunc('Month', [data].[ContainerStatus].DateTime)

Parents
  • This should work, assuming it doesn't take too long to calculate.

    SELECT 
    g.DisplayName as Site
    , Case
    When n1.mm = 1 Then 'Jan'
    When n1.mm = 2 Then 'Feb'
    When n1.mm = 3 Then 'Mar'
    When n1.mm = 4 Then 'Apr'
    When n1.mm = 5 Then 'May'
    When n1.mm = 6 Then 'Jun'
    When n1.mm = 7 Then 'Jul'
    When n1.mm = 8 Then 'Aug'
    When n1.mm = 9 Then 'Sep'
    When n1.mm = 10 Then 'Oct'
    When n1.mm = 11 Then 'Nov'
    When n1.mm = 12 Then 'Dec'
    End as [1 Month Ago]
    , n1.[Average Percent Availability] as [1 Month Ago Availability]
    , Case
    When n2.mm = 1 Then 'Jan'
    When n2.mm = 2 Then 'Feb'
    When n2.mm = 3 Then 'Mar'
    When n2.mm = 4 Then 'Apr'
    When n2.mm = 5 Then 'May'
    When n2.mm = 6 Then 'Jun'
    When n2.mm = 7 Then 'Jul'
    When n2.mm = 8 Then 'Aug'
    When n2.mm = 9 Then 'Sep'
    When n2.mm = 10 Then 'Oct'
    When n2.mm = 11 Then 'Nov'
    When n2.mm = 12 Then 'Dec'
    End as [2 Months Ago]
    , n2.[Average Percent Availability] as [2 Months Ago Availability]
    , Case
    When n3.mm = 1 Then 'Jan'
    When n3.mm = 2 Then 'Feb'
    When n3.mm = 3 Then 'Mar'
    When n3.mm = 4 Then 'Apr'
    When n3.mm = 5 Then 'May'
    When n3.mm = 6 Then 'Jun'
    When n3.mm = 7 Then 'Jul'
    When n3.mm = 8 Then 'Aug'
    When n3.mm = 9 Then 'Sep'
    When n3.mm = 10 Then 'Oct'
    When n3.mm = 11 Then 'Nov'
    When n3.mm = 12 Then 'Dec'
    End as [3 Months Ago]
    , n3.[Average Percent Availability] as [3 Months Ago Availability]
    
    FROM orion.groups g
    left join ( -- N-1
    select cs.ContainerID, month(addmonth(-1,DATETRUNC('month',getdate()))) as mm, avg(cs.PercentAvailability) as [Average Percent Availability]
    from orion.ContainerStatus cs
    where cs.Container.CustomProperties.Business_Unit_Groups = 'X'
    and cs.DateTime < DATETRUNC('month',getdate())
    and cs.DateTime >= addmonth(-1,DATETRUNC('month',getdate()))
    group by cs.ContainerID, month(cs.DateTime)
    ) n1 on n1.containerid=g.ContainerID
    
    left join ( -- N-2
    select cs.ContainerID,month(addmonth(-2,DATETRUNC('month',getdate()))) as mm, avg(cs.PercentAvailability) as [Average Percent Availability]
    from orion.ContainerStatus cs
    where cs.Container.CustomProperties.Business_Unit_Groups = 'X'
    and cs.DateTime < addmonth(-1,DATETRUNC('month',getdate()))
    and cs.DateTime >= addmonth(-2,DATETRUNC('month',getdate()))
    group by cs.ContainerID, month(cs.DateTime)
    ) n2 on n2.containerid=g.ContainerID
    left join ( -- N-3
    select cs.ContainerID, month(addmonth(-3,DATETRUNC('month',getdate()))) as mm, avg(cs.PercentAvailability) as [Average Percent Availability]
    from orion.ContainerStatus cs
    where cs.Container.CustomProperties.Business_Unit_Groups = 'X'
    and cs.DateTime < addmonth(-2,DATETRUNC('month',getdate()))
    and cs.DateTime >= addmonth(-3,DATETRUNC('month',getdate()))
    group by cs.ContainerID, month(cs.DateTime)
    ) n3 on n3.containerid=g.ContainerID
    
    WHERE g.CustomProperties.Business_Unit_Groups = 'X'

  • This works great! Thank you! One small change I want to make is to pull the current month and then the previous two months like you have. What do I need to change to make that switch? 

Reply Children
No Data