• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

SQL Server query help


I need help with a query.  For each source, I want to print some data for the maximum value of created.

goal
   for each source
   print data for max(created)


-- This is a start but is not correct.
   select  convert(char(24),s_qos_data.source         ) as Source    
        ,convert(char(10),hn_qos_data_0009.Sampleavg) as SampleAvg
        ,                 s_qos_data.created          as Created
        ,convert(char(20),s_qos_data.target         ) as Target
   from s_qos_data
   join hn_qos_data_0009
   on s_qos_data.table_id = hn_qos_data_0009.table_id
   where qos = 'qos_disk_usage_perc'
   and created = (select max(created) from s_qos_data where qos = 'qos_disk_usage_perc')
   go


-- This is closer but still not right.  As written, 628 rows are produced but SampleAvg is not
-- in the query.  When I uncomment SampleAvg, I get 2672 rows.


   use nimbusSlm
   set nocount on
   declare @sourceName varchar(50)
   declare sourceCursor cursor for   select distinct source from s_qos_data order by source
   open sourceCursor
   fetch next from sourceCursor into @sourceName
   while @@fetch_status = 0

   begin
      select @sourceName = convert(char(24),@sourceName)
     
      select  
       @sourceName                                      as Source    
       -- ,convert(char(10),hn_qos_data_0009.SampleAvg) as SampleAvg
          ,                 s_qos_data.created          as Created
          ,convert(char(20),s_qos_data.target         ) as Target
      from s_qos_data join hn_qos_data_0009
      on s_qos_data.table_id = hn_qos_data_0009.table_id
      where qos = 'qos_disk_usage_perc'
      and created = (select max(created)
                 from s_qos_data where qos = 'qos_disk_usage_perc'
                 and s_qos_data.source = @sourceName)
      and qos = 'qos_disk_usage_perc'
      and s_qos_data.source = @sourceName
      group by source, created, target --, sampleAvg
      -- having sampleAvg=max(SampleAvg)

      fetch next from sourceCursor into @sourceName
   end

   close sourceCursor
   deallocate sourceCursor
0
AikiRyu
Asked:
AikiRyu
  • 3
  • 2
1 Solution
 
chapmandewCommented:
i think you just needed to correlate your query.

 select  convert(char(24),s_qos_data.source         ) as Source    
        ,convert(char(10),hn_qos_data_0009.Sampleavg) as SampleAvg
        ,                 s_qos_data.created          as Created
        ,convert(char(20),s_qos_data.target         ) as Target
   from s_qos_data
   join hn_qos_data_0009
   on s_qos_data.table_id = hn_qos_data_0009.table_id
   where qos = 'qos_disk_usage_perc'
   and created = (select max(created) from s_qos_data g where qos = 'qos_disk_usage_perc' and g.idvalue = s_qos_data.idvalue)
   go
0
 
AikiRyuAuthor Commented:
Thanks, we're getting closer!



-- Without "distinct," this query produces 17550 rows with many duplicates
-- When I include "distinct," query produces 2967 rows but I still have too many values.
-- I only want the maximum SampleAvg for this source
--
-- Source                   SampleAvg  Created                 Target
-- ------------------------ ---------- ----------------------- --------------------
-- allele                   39.66      2008-12-11 10:31:00     /var                
-- allele                   39.67      2008-12-11 10:31:00     /var                
-- allele                   39.69      2008-12-11 10:31:00     /var                
-- allele                   39.70      2008-12-11 10:31:00     /var                
-- allele                   39.71      2008-12-11 10:31:00     /var                
-- allele                   39.73      2008-12-11 10:31:00     /var                
-- allele                   39.74      2008-12-11 10:31:00     /var                ** I only want this row **


select distinct
       convert(char(24),s_qos_data.source         ) as Source    
      ,convert(char(10),hn_qos_data_0009.Sampleavg) as SampleAvg
      ,                 s_qos_data.created          as Created
      ,convert(char(20),s_qos_data.target         ) as Target
from s_qos_data
join hn_qos_data_0009
on s_qos_data.table_id = hn_qos_data_0009.table_id
where qos = 'qos_disk_usage_perc'
and created =
    (select max(created) from s_qos_data g
     where qos = 'qos_disk_usage_perc'
     and g.table_id = s_qos_data.table_id)
order by source
go
0
 
chapmandewCommented:
how bout this....

select  convert(char(24),s_qos_data.source         ) as Source    
        ,convert(char(10),hn_qos_data_0009.Sampleavg) as SampleAvg
        ,                 s_qos_data.created          as Created
        ,convert(char(20),s_qos_data.target         ) as Target
   from s_qos_data
   join hn_qos_data_0009
   on s_qos_data.table_id = hn_qos_data_0009.table_id
   where qos = 'qos_disk_usage_perc'
   and created = (select max(created) from s_qos_data g where qos = 'qos_disk_usage_perc' and g.idvalue = s_qos_data.idvalue)
and convert(char(10),hn_qos_data_0009.Sampleavg = (select max(convert(char(10),Sampleavg) from hn_qos_data_0009  g where g.idvalue = hn_qos_data_0009.idvalue)
0
 
AikiRyuAuthor Commented:

I added "distinct" and it looks good!

-- 702 rows
select distinct  
       convert(char(24),s_qos_data.source         ) as Source    
      ,convert(char(10),hn_qos_data_0009.Sampleavg) as SampleAvg
      ,                 s_qos_data.created          as Created
      ,convert(char(20),s_qos_data.target         ) as Target
from s_qos_data
join hn_qos_data_0009
on s_qos_data.table_id = hn_qos_data_0009.table_id
where qos = 'qos_disk_usage_perc'
and created = (select max(created) from s_qos_data g
             where qos = 'qos_disk_usage_perc'
             and g.table_id = s_qos_data.table_id)
and hn_qos_data_0009.SampleAvg = (select max(SampleAvg) from hn_qos_data_0009 g
                             where g.table_id = hn_qos_data_0009.table_id)
order by source, target
0
 
chapmandewCommented:
great!
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now