CODEWITHSUNDEEP
sqlsql-serversql-server-2008
winchmore
winchmore

Reputation: 37

SQL Server 2008 another way to do this

![enter image description here][1]

Brings the correct results of one row freespace,avg and days left. Would have to do another query to get the latest freespace. can use row_number rank but that would bring back 700. Any way to do this better rgs 

declare @top  int=3
declare @dept varchar(20)='dept_a'

select * from dept

select top(@top) * 
from dept
where usage<0
order by datestamp desc


select  
    freespace ,
    avgDay,
    (freespace/avgDay) as daysLeft 
from 
    (select top(1) freespace 
     from dept 
     where deptname = @dept 
     order by datestamp desc ) as freespace,
    (select avg(usage) AvgDAy 
     from
          (select top(@top) * 
           from dept
           where usage < 0 
             and DeptName = @dept
           order by datestamp desc) as AvgSel
    ) as AvgDay

Data http://sqlfiddle.com/#!6/210dd/1

Results for Dept A - 920 freespace 133 avg day 6 days left http://sqlfiddle.com/#!6/210dd/2

Results for Dept B - 400 freespace 66 avg day 6 days left http://sqlfiddle.com/#!6/210dd/4

Upvotes: 0

Views: 90

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

This is basically your query, if I understand correctly:

select  freespace ,avgDay,(freespace/avgDay) as daysLeft
from (select top(1) freespace
      from dept
      where deptname = @dept
      order by datestamp desc
     ) as freespace cross join
     (select avg(usage) as AvgDAy
      from (select top(@top) *
            from dept
            where usage < 0 and DeptName = @dept
            order by datestamp desc
           ) as AvgSel
     ) as AvgDay

A more efficient form would be:

select max(case when seqnum_1 = 1 then freespace end) as freespace,
       avg(case when seqnum_2 <= 3 and usage < 0 then usage end) as dailyusage,
       (max(case when seqnum_1 = 1 then freespace end) /
        avg(case when seqnum_2 <= 3 and usage < 0 then usage end)
       ) as days_left
from (select d.*,
             row_number() over (order by datestamp desc) as seqnum_1,
             row_number() over (partition by (case when usage < 0 then 1 else 0 end)
                                order by datestamp desc) as seqnum_2
      from dept
      where dept = @dept
     ) d
where seqnum_1 = 1 or (seqnum_2 <= 3 and usage < 0);

In general, window functions are going to be faster than order by.

Here is a SQL Fiddle.

Upvotes: 1

Related Questions