6 Replies Latest reply on Apr 3, 2014 9:55 AM by 8clover

    Help with SQL Report Writer- Group By

    Ckesey

      Hello everyone.

       

      Here is a quick overview of what I'm trying to do.

       

      We have a piece of network gear that is sending traps to our NPM. We are interested in one specific message it sends and more importantly how often/when. I'd like to create a report/graph that breaks down the days into blocks that will tell us how many of these specific traps we have received during this specific time period. This would help us troubleshoot an issue. Currently I've been working with Report Writer and have the sql statement that shows only the specific traps we are concerned about.....however it doesn't group it.

       

      For Example:

      1:00PM    10

      1:30PM    157

      2:00PM    12

      2:30PM    11

      or something similar.....

       

      Can someone help me here with the remainder of the statement? The grouping syntax I've tried hasn't worked thus far. Is there a better way I can organize this data (graph maybe?)

       

      SELECT [DateTime] ,[IPAddress] ,[TrapType] FROM [Traps] where [TrapType] like '%NSG9000%';

        • Re: Help with SQL Report Writer- Group By
          Leon Adato

          I'm not familiar with the tables involved, but I think what you want is:

           

          SELECT [DateTime] ,COUNT([IPAddress]) FROM [Traps] where [TrapType] like '%NSG9000%';

          GROUP BY [DateTime]

          ORDER BY [DateTime] ASCENDING

          • Re: Help with SQL Report Writer- Group By
            8clover

            Leon is right, you need to add a group by statement.  A straight group by on a date will group by the smallest time element, ususally second, and often does not give the desired grouping. Given your example, it looks like you want to group by 30 minute blocks.  Not sure if you want just counts or the actual details.  Below are some not so pretty queries that might get you what you are looking for.  The formatting is better handled in the Report Writer to actually display them in groups and do any additional field formatting.  For instance, to display them in 30 minute blocks, under the Report Grouping tab, add the DateTimeGroup to the Report Groups.

             

            To show the details:

            select
            cast( ( cast( datepart( year, [DateTime] ) as varchar ) + '/' +
            cast( datepart( month, [DateTime] ) as varchar ) + '/' +
            cast( datepart( day, [DateTime] ) as varchar ) + ' ' +
            cast( datepart( hour, [DateTime] ) as varchar ) + ':' +
            cast( (datepart( minute, [DateTime] ) / 30 ) * 30 as varchar ) ) as datetime ) as DateTimeGroup,
            IPAddress, TrapType
            from Traps
            where TrapType like '%NSG9000%'
            order by [DateTime]
            

             

            To show just counts:

            select
            cast( ( cast( datepart( year, [DateTime] ) as varchar ) + '/' +
            cast( datepart( month, [DateTime] ) as varchar ) + '/' +
            cast( datepart( day, [DateTime] ) as varchar ) + ' ' +
            cast( datepart( hour, [DateTime] ) as varchar ) + ':' +
            cast( (datepart( minute, [DateTime] ) / 30 ) * 30 as varchar ) ) as datetime ) as DateTimeGroup,
            count(*)
            from Traps
            where TrapType like '%NSG9000%'
            group by 
            cast( ( cast( datepart( year, [DateTime] ) as varchar ) + '/' +
            cast( datepart( month, [DateTime] ) as varchar ) + '/' +
            cast( datepart( day, [DateTime] ) as varchar ) + ' ' +
            cast( datepart( hour, [DateTime] ) as varchar ) + ':' +
            cast( (datepart( minute, [DateTime] ) / 30 ) * 30 as varchar ) ) as datetime )
            
              • Re: Help with SQL Report Writer- Group By
                Ckesey

                Thanks 8clover that's what I was looking for.

                 

                The only issue I have is they are not arranged by date and the time slots are all over the place (as far as order).

                 

                Can I add an order by statement at the end to put them in some sort of ascending/descending order?

                  • Re: Re: Help with SQL Report Writer- Group By
                    8clover

                    Sure can.  There are a couple of ways to go about it.  The below is not pretty, but is simple in that it builds off of what we already have. You can change the asc to desc to change the sort order

                     

                    select  
                    cast( ( cast( datepart( year, [DateTime] ) as varchar ) + '/' +  
                    cast( datepart( month, [DateTime] ) as varchar ) + '/' +  
                    cast( datepart( day, [DateTime] ) as varchar ) + ' ' +  
                    cast( datepart( hour, [DateTime] ) as varchar ) + ':' +  
                    cast( (datepart( minute, [DateTime] ) / 30 ) * 30 as varchar ) ) as datetime ) as DateTimeGroup,  
                    count(*)  
                    from Traps  
                    where TrapType like '%NSG9000%'  
                    group by   
                    cast( ( cast( datepart( year, [DateTime] ) as varchar ) + '/' +  
                    cast( datepart( month, [DateTime] ) as varchar ) + '/' +  
                    cast( datepart( day, [DateTime] ) as varchar ) + ' ' +  
                    cast( datepart( hour, [DateTime] ) as varchar ) + ':' +  
                    cast( (datepart( minute, [DateTime] ) / 30 ) * 30 as varchar ) ) as datetime )
                    order by
                    cast( ( cast( datepart( year, [DateTime] ) as varchar ) + '/' +  
                    cast( datepart( month, [DateTime] ) as varchar ) + '/' +  
                    cast( datepart( day, [DateTime] ) as varchar ) + ' ' +  
                    cast( datepart( hour, [DateTime] ) as varchar ) + ':' +  
                    cast( (datepart( minute, [DateTime] ) / 30 ) * 30 as varchar ) ) as datetime ) asc
                    
                • Re: Help with SQL Report Writer- Group By
                  bluefunelemental

                  As an aside- the new webbased report tool includes time summarization without needing to group by.