A view - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

A view

Author

Topic

ArviL
Starting Member

25 Posts

Posted - 2011-03-29 : 06:26:57

Hi

A table ResourceUse (Resource_ID, Dep_ID, FromD, ToD) determines, which departments are using resoursce and for which time interval. The primary key is a composite one, composed from fields <Resource_ID>, <Dep_ID> and <FromD>. The empty <ToD> field indicates, that the given department is using the given resource currently.

A table DepUsers (Dep_ID, FromD, Users) determines the number of individual users in every department at given time. The primary key is a composite one, composed from fields <Dep_ID> and <FromD>.
P.e. the number of users @Users1 for department @Dep1 at date @Date1 is calculated as
SELECT @Users1=Users FROM dbo.DepUsers WHERE Dep_ID=@Dep1 AND FromD=(SELECT MAX(x.FromD) FROM dbo.DepUsers x WHERE x.Dep_ID=@Dep1 AND x.FromD<=@Date1).

Now I need a view, where those 2 tables are combined.
v_ResourceDepUsers (Resource_ID, Dep_ID, FromD, ToD)
There must be an entry for every date when an depatrment is added to resource users (a new FromD), or when it is canceled as resource user (ToD Is Not Null). And also when the number of individual users in some of departments using the resource does change (a new FromD).

Thanks in advance
Arvi Laanemets

---
Resource_ID is varchar, Dep_ID is integer, FromD and ToD are integers, and Users is integer.

---
An Example:

ResourceUse (
"r1", 1, 01.01.2011, 31.01.2011;
"r1", 2, 01.01.2011, Null;
"r2", 1, 01,01,2011, Null)

DepUsers (
1, 01.12.2010, 10;
1, 01.02.2011, 15;
1, 01.03.2011, 5;
2, 01.02.2011, 20)

v_ResourceDepUsers (
"r1", 1, 01.01.2011, 31.01.2011, 10;
"r1", 2, 01.02.2011, Null, 20;
"r2", 1, 01.01.2011, 31.01.2011, 10;
"r2", 1, 01.02.2011, 28.02.2011, 15;
"r2", 1, 01.03.2011, Null, 5)

ArviL
Starting Member

25 Posts

Posted - 2011-03-30 : 10:05:24

Currently I have following query which seems to work at least.

---
WITH query_users AS (
SELECT
a0.Resource_ID,
a0.Dep_ID,
a0.FromD,
Null As ToD,
(SELECT Users
FROM dbo.DepUsers AS x0
WHERE
(Dep_ID = a0.Dep_ID)
AND (Users IS NOT NULL)
AND (FromD = (
SELECT MAX(FromD) AS FromD
FROM dbo.DepUsers AS y0
WHERE
(Dep_ID = a0.Dep_ID)
AND (FromD <= a0.FromD)))) AS Users
FROM dbo.ResourceUse AS a0
LEFT OUTER JOIN dbo.DepUsers AS b0 ON b0.Dep_ID = a0.Dep_ID AND b0.FromD = a0.FromD
UNION
SELECT
a1.Resource_ID,
a1.Dep_ID,
a1.ToD+1 AS FromD,
Null As ToD,
0 AS Users
FROM
dbo.ResourceUse a1
LEFT OUTER JOIN dbo.DepUsers b1 ON b1.Dep_ID = a1.Dep_ID AND b1.FromD = a1.ToD+1
UNION
SELECT
b2.Resource_ID,
a2.Dep_ID,
a2.FromD,
Null As ToD,
a2.Users
FROM dbo.DepUsers AS a2
JOIN dbo.ResourceUse AS b2 ON a2.FromD >= b2.FromD AND a2.Dep_ID = b2.Dep_ID
WHERE
(a2.FromD NOT IN (
SELECT FromD
FROM dbo.ResourceUse
WHERE
(Resource_ID = b2.Resource_ID)
AND (Dep_ID = a2.Dep_ID))))
SELECT DISTINCT
z0.Resource_ID,
z0.Dep_ID,
z0.FromD,
(SELECT MIN(z1.FromD-1) FROM query_users AS z1 WHERE z1.Resource_ID = z0.Resource_ID AND z1.Dep_ID = z0.Dep_ID AND z1.FromD > z0.FromD) AS ToD,
z0.Users
FROM query_users AS z0
WHERE (z0.Users IS NOT NULL AND z0.FromD Is Not Null)
ORDER BY z0.Resource_ID, z0.Dep_ID, z0.FromD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources