Adding "Last Month" or "This Month" to a SWQL query

Hello,

Here is my query so far:

SELECT
[data].[DisplayName] AS [Site],
[data].[ContainerStatus].[PercentAvailability] AS [PercentAvailability],
[data].[InstanceSiteId] AS [InstanceSiteId]
FROM orion.groups AS data
WHERE
((([data].[CustomProperties].[Business_Unit_Groups]) = ('X')) AND ((([data].[ContainerStatus].[PercentAvailability]) != (100))))

I am looking to have this query now track the overall availability of the current month and the previous month. They do not have to be tracked in the same query but if someone could provide the additional SWQL line(s) that would pull those date ranges, that would be great! 

  • This should get you closer, just merge the two and I bet you get it done. 

    SELECT 
    ContainerID
    , Case 
        When MONTH(  DateTrunc('Month', DateTime) ) = 1 Then 'Jan'
        When MONTH(  DateTrunc('Month', DateTime) ) = 2 Then 'Feb'
        When MONTH(  DateTrunc('Month', DateTime) ) = 3 Then 'Mar'
        When MONTH(  DateTrunc('Month', DateTime) ) = 4 Then 'Apr'
        When MONTH(  DateTrunc('Month', DateTime) ) = 5 Then 'May'
        When MONTH(  DateTrunc('Month', DateTime) ) = 6 Then 'Jun'
        When MONTH(  DateTrunc('Month', DateTime) ) = 7 Then 'Jul'
        When MONTH(  DateTrunc('Month', DateTime) ) = 8 Then 'Aug'
        When MONTH(  DateTrunc('Month', DateTime) ) = 9 Then 'Sep'
        When MONTH(  DateTrunc('Month', DateTime) ) = 10 Then 'Oct'
        When MONTH(  DateTrunc('Month', DateTime) ) = 11 Then 'Nov'
        When MONTH(  DateTrunc('Month', DateTime) ) = 12 Then 'DEC'
        Else ''
    End as [Month]
    , MONTHDIFF(  DateTrunc('Month', DateTime), Getdate() )         as [Months Ago]
    , Avg(PercentAvailability) as [Average Percent Available], AVG(PercentMembersAvailability) as [Average Percent Member Available]
    FROM Orion.ContainerStatus
    
    Where MONTHDIFF(  DateTrunc('Month', DateTime), Getdate() ) < 2
    Group By ContainerID,   DateTrunc('Month', DateTime)
    Order By ContainerID,   DateTrunc('Month', DateTime)

  • I greatly appreciate the help, but I am not much of a SWQL/SQL expert. What is the easiest way to merge the two? I am having issues with which FROM statement to use when I try to combine them. 

  • I can't test it, but it would be something like: 

    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]
    , Avg([data].[ContainerStatus].PercentAvailability) as [Average Percent Availability]
    
    FROM orion.groups AS data
    WHERE
              ([data].[CustomProperties].[Business_Unit_Groups]) = ('X')
         And  MONTHDIFF(  DateTrunc('Month', [data].[ContainerStatus].DateTime), Getdate() ) < 2
    Group By ContainerID,   DateTrunc('Month', [data].[ContainerStatus].DateTime)
    Order By ContainerID,   DateTrunc('Month', [data].[ContainerStatus].DateTime)


    I went with the ''orion groups' for the from since we already knew how it was related (by SWQL's built in relationships) to containerstatus.  Its easiest to see these connections in SWQL studio, part of the SDK. Also I didn't use the not equal 100% available filter. If you want to add that back to the where clause, it might be worth changing it to :   "AND Avg([data].[ContainerStatus].PercentAvailability) != (100)" To filter out months that less that 100% available, but normally if you have a bad month, you want to see it get better, so I dropped it. 

    As for being a SWQL expert, you don't have to be, but if you think you might continue to want less generic and more specific information from Orion, a little practice goes a long way. SWQL is obviously patterned after SQL, but its scope is smaller, and can save you some time (like the built in relationships between tables). 

     put together: https://www.youtube.com/watch?v=8pTQo8Oqk8c&t=4s and its a good place to get started.

  • This has been a major help, thank you so much!