Average of occupants in different departments

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Average of occupants in different departments

Author

Topic

slydroe
Starting Member

17 Posts

Posted - 2014-02-07 : 16:17:52

Hi guys,

I have this query that works perfectly. It counts previous month's bed occupants on a daily basis (like census) from first day to last day of month, and throws it to a temp table. Then it calculates the monthly average of the numbers in the temp table and outputs the avg to a column. It does this for a single department only. The output is this:

AvgDept1
-------
18

Now, I have to do this for many other departments, so I need a whole big statement that will give me this output:

AvgDept1 | AvgDept2 | AvgDept3
------- -------- --------
18 | 20 | 19

I need help on how to do this. Thank you!

This is my query so far:

DECLARE @SDate AS DateTime
DECLARE @EDate AS DateTime
DECLARE @tmp TABLE (DayCount float)

SET @SDate = DATEADD(m,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0))
SET @EDate = DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

WHILE @SDate <= @EDate
BEGIN
INSERT INTO @tmp (DayCount)
SELECT COUNT(Distinct b.PatKey) As Expr1
FROM Beds as b LEFT JOIN
Reservation as r ON b.PatKey = r.PatKey and b.DeptKey = r.DeptKey
WHERE (r.Date_Start <= (@SDate) AND(r.DeptKey =10) and
(@SDate)<=
CASE WHEN r.Date_End IS NULL then GETDATE()
WHEN r.Date_End IS NOT NULL THEN r.Date_End
end)
SET @SDate=@Sdate+1
END

SELECT Round(AVG(DayCount),0) AS AvgDept1 FROM @tmp

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-07 : 17:20:07

Hopefully you have set number of departments. Assuming so try this.
But you need to change "10", "11" and "12" in the red code below with whatever your actual deptKey values are.
If I understand what you're doing you can replace the WHILE loop with a numbers table (I used master..spt_values).
And if you don't need to pivot the results to columns with one row then you can just use the blue code.


select p.[10] as AvgDept1
       ,p.[11] as AvgDept2
       ,p.[12] as AvgDept3
from   (
       select deptKey
              ,round(avg(dayCount),0) as DeptAvg
       from   (
              select dateadd(day, v.number, @sdate) as dt
                     ,r.deptkey
                     ,count(distinct b.patkey) as dayCount
              from   beds b
              inner join reservation r
                     on r.patkey = b.patkey
                     and r.deptkey = b.deptkey
              inner join master..spt_values v
                     on v.type = 'p'
                     and v.number < datediff(day,@sdate,@edate)
                     and r.date_start <= dateadd(day, v.number, @sdate)
                     and dateadd(day, v.number, @sdate) <= isNull(r.date_end, getdate())
              where  r.deptKey in (10,11,12)
              group by dateadd(day, v.number, @sdate)
                     ,r.deptkey
              ) d
       group by deptKey
       ) g
Pivot (
       max(DeptAvg)
       for deptKey in ([10],[11],[12])
       ) as p

Be One with the Optimizer
TG

slydroe
Starting Member

17 Posts

Posted - 2014-02-07 : 17:40:19

Thanks! Will try this later today. I just got word from my supervisor that we actually have to combine certain departments together. So one column will be AvgDept1, but another column might be AvgDept2&3, and another might be AvgDept5_6_&7.

quote:

Originally posted by TG

Hopefully you have set number of departments. Assuming so try this.
But you need to change "10", "11" and "12" in the red code below with whatever your actual deptKey values are.
If I understand what you're doing you can replace the WHILE loop with a numbers table (I used master..spt_values).
And if you don't need to pivot the results to columns with one row then you can just use the blue code.


select p.[10] as AvgDept1
       ,p.[11] as AvgDept2
       ,p.[12] as AvgDept3
from   (
       select deptKey
              ,round(avg(dayCount),0) as DeptAvg
       from   (
              select dateadd(day, v.number, @sdate) as dt
                     ,r.deptkey
                     ,count(distinct b.patkey) as dayCount
              from   beds b
              inner join reservation r
                     on r.patkey = b.patkey
                     and r.deptkey = b.deptkey
              inner join master..spt_values v
                     on v.type = 'p'
                     and v.number < datediff(day,@sdate,@edate)
                     and r.date_start <= dateadd(day, v.number, @sdate)
                     and dateadd(day, v.number, @sdate) <= isNull(r.date_end, getdate())
              where  r.deptKey in (10,11,12)
              group by dateadd(day, v.number, @sdate)
                     ,r.deptkey
              ) d
       group by deptKey
       ) g
Pivot (
       max(DeptAvg)
       for deptKey in ([10],[11],[12])
       ) as p

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-07 : 19:23:28

just create a little mapping table that maps deptKeys to deptGroups. Then join that table by deptKey but group by deptGroup. (in the blue code)

Be One with the Optimizer
TG

slydroe
Starting Member

17 Posts

Posted - 2014-02-07 : 19:34:47

@TG - the query written in blue works well and gives me this output:

DeptKey | AvgDept
--------- | --------
___1____|____5_____
___2____|____8_____
___3____|____9_____

However, I need each averaged department to be the column name, so I want the output to be like this:

AvgDept1 | AvgDept2 | AvgDept3
----------------------------------
___5_____|____8____|___9_____

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-07 : 21:36:07

The outer part of the query I wrote (outside the blue) does the pivoting with one row and a column for each department.
you can either alias the column names as I have done. Or tell me which table has your department names.

Be One with the Optimizer
TG

slydroe
Starting Member

17 Posts

Posted - 2014-02-07 : 21:41:49

I tried it, but somehow it's telling me error msg about compatibility blahblah and that I should run an alter database so I can use pivot… I'm using SQL 2008.

quote:
Originally posted by TG

The outer part of the query I wrote (outside the blue) does the pivoting with one row and a column for each department.
you can either alias the column names as I have done. Or tell me which table has your department names.

Be One with the Optimizer
TG

slydroe
Starting Member

17 Posts

Posted - 2014-02-07 : 21:46:50

Here's the actual error msg:
You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-07 : 21:53:32

sounds like your database is set to a lower compatibility level than your server. Probably 2000 because I think PIVOT was available in 2005. You can see by right clicking the database in object explorer and selecting "properties". Then go to the "options" pane and look at the compatibility level.

You have two choices:
1. set your compatibility level up.
2. re-write the PIVOT part to in a way that is backward compatible with your DB level. I can help with that if you want but the easiest thing to do is select a higher compatibility level.

What is your pleasure?

Be One with the Optimizer
TG

slydroe
Starting Member

17 Posts

Posted - 2014-02-07 : 21:56:17

Would it affect any existing sp we have if I set it higher?

quote:
Originally posted by TG

sounds like your database is set to a lower compatibility level than your server. Probably 2000 because I think PIVOT was available in 2005. You can see by right clicking the database in object explorer and selecting "properties". Then go to the "options" pane and look at the compatibility level.

You have two choices:
1. set your compatibility level up.
2. re-write the PIVOT part to in a way that is backward compatible with your DB level. I can help with that if you want but the easiest thing to do is select a higher compatibility level.

What is your pleasure?

Be One with the Optimizer
TG

slydroe
Starting Member

17 Posts

Posted - 2014-02-07 : 21:57:52

And you're right… it's 2000. I think I'll be safer if we just modify the pivot command for backward compatibility. Would you mind?

quote:
Originally posted by slydroe

Would it affect any existing sp we have if I set it higher?

quote:
Originally posted by TG

sounds like your database is set to a lower compatibility level than your server. Probably 2000 because I think PIVOT was available in 2005. You can see by right clicking the database in object explorer and selecting "properties". Then go to the "options" pane and look at the compatibility level.

You have two choices:
1. set your compatibility level up.
2. re-write the PIVOT part to in a way that is backward compatible with your DB level. I can help with that if you want but the easiest thing to do is select a higher compatibility level.

What is your pleasure?

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-07 : 22:08:05

>>Would it affect any existing sp we have if I set it higher?
It's possible. There are a few bits of syntax that was allowed in 2000 but not in later versions. Safest thing is to test it in a non-production environment. Here is a way to pivot in 2000:


select max(case when deptKey = 10 then DeptAvg end) as DeptAvg1
       ,max(case when deptKey = 11 then DeptAvg end) as DeptAvg2
       ,max(case when deptKey = 12 then DeptAvg end) as DeptAvg3
from   (
       select deptKey
              ,round(avg(dayCount),0) as DeptAvg
       from   (
              select dateadd(day, v.number, @sdate) as dt
                     ,r.deptkey
                     ,count(distinct b.patkey) as dayCount
              from   beds b
              inner join reservation r
                     on r.patkey = b.patkey
                     and r.deptkey = b.deptkey
              inner join master..spt_values v
                     on v.type = 'p'
                     and v.number < datediff(day,@sdate,@edate)
                     and r.date_start <= dateadd(day, v.number, @sdate)
                     and dateadd(day, v.number, @sdate) <= isNull(r.date_end, getdate())
              where  r.deptKey in (10,11,12)
              group by dateadd(day, v.number, @sdate)
                     ,r.deptkey
              ) d
       group by deptKey
       ) g

Be One with the Optimizer
TG

slydroe
Starting Member

17 Posts

Posted - 2014-02-07 : 22:24:17

I'm sure everyone here knows you're a GURU! Well, I'm new here and I'm just starting to figure that out. That's exactly what I needed! I appreciate everything!

quote:

Originally posted by TG

>>Would it affect any existing sp we have if I set it higher?
It's possible. There are a few bits of syntax that was allowed in 2000 but not in later versions. Safest thing is to test it in a non-production environment. Here is a way to pivot in 2000:


select max(case when deptKey = 10 then DeptAvg end) as DeptAvg1
       ,max(case when deptKey = 11 then DeptAvg end) as DeptAvg2
       ,max(case when deptKey = 12 then DeptAvg end) as DeptAvg3
from   (
       select deptKey
              ,round(avg(dayCount),0) as DeptAvg
       from   (
              select dateadd(day, v.number, @sdate) as dt
                     ,r.deptkey
                     ,count(distinct b.patkey) as dayCount
              from   beds b
              inner join reservation r
                     on r.patkey = b.patkey
                     and r.deptkey = b.deptkey
              inner join master..spt_values v
                     on v.type = 'p'
                     and v.number < datediff(day,@sdate,@edate)
                     and r.date_start <= dateadd(day, v.number, @sdate)
                     and dateadd(day, v.number, @sdate) <= isNull(r.date_end, getdate())
              where  r.deptKey in (10,11,12)
              group by dateadd(day, v.number, @sdate)
                     ,r.deptkey
              ) d
       group by deptKey
       ) g

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-07 : 22:31:28

You're welcome. Glad I could help.

Be One with the Optimizer
TG

slydroe
Starting Member

17 Posts

Posted - 2014-02-07 : 22:56:19

There's one more thing I need your help with: Some departments need to be counted as one, and also averaged as one department.

I tried to modify your code, but the logic is not right:

select max(case when deptkey in ('1','2') then DeptAvg end) as AvgDept1_2
,max(case when deptkey = 3 then DeptAvg end) as AvgDept3

and i think because at the bottom of the whole query is this:

where deptkey in ('1','2','3')

I'm not getting the right average for AvgDept1_2

Thanks!

quote:
Originally posted by TG

You're welcome. Glad I could help.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-07 : 23:04:21

change the MAX to SUM. That should combine multiple departments

Be One with the Optimizer
TG

slydroe
Starting Member

17 Posts

Posted - 2014-02-07 : 23:20:04

That's it! Thanks again!

quote:
Originally posted by TG

change the MAX to SUM. That should combine multiple departments

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources