CODEWITHSUNDEEP
sql-servert-sql
Colin R
Colin R

Reputation: 25

t-sql select all values based on top 5 query

I've been hunting through documentation and examples and playing to try and make this work but am having no luck so I'm hoping someone might point me in the right direction.

I have my top 5 items from a table called maintenance via query based the month, basically giving me the top 5 nodes with the highest calls this month.

Ex. Top 5 select


select top 5  
  maint.node_id
  ,maint.sc_tot
FROM
  server.dbo.maintenance as maint
where
    maint.province_name='provname
and
    maint.system_code='syscode'
and
    maint.city_name='cityname'
and
( 
    year(maint.startdate)=2015
    and 
    month(maint.startdate)=07
)
group by
  maint.node_id
  ,maint.sc_tot

order by
sum(isnull(maint.je_tot,0)+isnull(maint.sc_tot,0)+isnull(maint.tt_tot,0))
desc

Output is

node_id sc_tot
node1   30
node2   28
node3   27
node4   23
node5   23

What I need to do is to now select the sum of the calls for the month for each of those nodes without a time frame. Basically giving me the history and ultimately the trend for each node, from the same maintenance table.

Ex. Quick summary(not exact details)

node startdate sc_tot
node1 jan       10
node1 feb       15
node1 mar       36
node2 jan       14
node2 feb       22
etc....

This query will then be used in sql report builder for some reporting and graphing, but as long as i have the values I'm good.

Is this possible with just a single sql query?

Upvotes: 0

Views: 71

Answers (2)

xQbert
xQbert

Reputation: 35323

UNTESTED...

Perhaps something like...

I think you're after the top 5 nodes with the highest call volume in July of 2015, and then you want the node history by month/year of everything in the system for those top 5 nodes.

WITH CTE AS (   
  SELECT top 5 a.node_id, a.sc_tot
  FROM server.dbo.maintenance as a
  WHERE a.province_name='provname'
    and a.system_code='syscode'
    and a.city_name='cityname'
    and year(a.startdate)=2015
    and month(a.startdate)=07
  GROUP BY a.node_id ,a.sc_tot
  order by sum(isnull(a.je_tot,0)+isnull(a.sc_tot,0)+isnull(a.tt_tot,0)) desc)


SELECT TopRec.node_ID, sum(maint.sc_tot) as SumOfCalls, 
       year(maint.startdate) as YR, month(maint.startdate) as MO
FROM CTE as TopRec
INNER JOIN server.dbo.maintenance as maint
  on CTE.Node_Id = Maint.Node_ID
GROUP BY TopRec.node_ID, year(maint.startdate), month(maint.startdate)

This is essentially the same as this (inline view): but using a newer technique... you can see why people prefer it for readability.

SELECT TopRec.node_ID, sum(maint.sc_tot) as SumOfCalls, 
       year(maint.startdate) as YR, month(maint.startdate) as MO
FROM (    
  SELECT top 5 a.node_id, a.sc_tot
  FROM server.dbo.maintenance as a
  WHERE a.province_name='provname'
    and a.system_code='syscode'
    and a.city_name='cityname'
    and year(a.startdate)=2015
    and month(a.startdate)=07
  GROUP BY a.node_id ,a.sc_tot
  order by sum(isnull(a.je_tot,0)+isnull(a.sc_tot,0)+isnull(a.tt_tot,0)) desc)) 
  as TopRec
  INNER JOIN server.dbo.maintenance as maint
    on CTE.Node_Id = Maint.Node_ID
  GROUP BY TopRec.node_ID, year(maint.startdate), month(maint.startdate)

Upvotes: 2

CoOl
CoOl

Reputation: 2797

The following should work: 

select top 5  
  maint.node_id
  ,convert(char(3), maint.startdate, 0) [mon]
  ,maint.sc_tot
FROM
  server.dbo.maintenance as maint
where
    maint.province_name='provname'
and
    maint.system_code='syscode'
and
    maint.city_name='cityname'
and
    year(maint.startdate)=2015
group by
  maint.node_id
  ,month(maint.startdate)
  ,maint.sc_tot

order by
sum(isnull(maint.je_tot,0)+isnull(maint.sc_tot,0)+isnull(maint.tt_tot,0))
desc

Though UNTESTED

Upvotes: 1

Related Questions