Availability As Time Goes By - 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 2000 Forums

SQL Server Development (2000)

Availability As Time Goes By

Author

Topic

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-05-31 : 15:42:52

I have two tables one that contains Employee Info and the other contains an employee availability date.

As time goes by I want to know how many total employees are available

The query below gives me employees that become available for the week.

SELECT COUNT(Employees.EmpID) AS EmployeesAvailable, DATEADD(day, - (1 * DATEPART(dw, EmployeeAvailability.DateAvailable)),
EmployeeAvailability.DateAvailable) AS Week
FROM Employees INNER JOIN
EmployeeAvailability ON Employees.EmpID = EmployeeAvailability.EmpID
GROUP BY DATEADD(day, - (1 * DATEPART(dw, EmployeeAvailability.DateAvailable)), EmployeeAvailability.DateAvailable)

I get the following

EmployeesAvailable Week
2 12/29/2001
1 1/12/2002
1 1/19/2002
1 1/26/2002
1 2/2/2002

I need to have it look like

EmployeesAvailable Week
2 12/29/2001
3 1/12/2002
4 1/19/2002
5 1/26/2002
6 2/2/2002

Tried ROLL UP AND CUBE But can't seem to get it right.
Thanks for you help.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-31 : 15:52:18

at first glance it looks to me like you just need to schedule some more employees ....

can you post the ddl and some sample data

EDIT . . . do you maybe need to count(EmployeeAvailability.EmpID) rather than Employees.EmpID

<O>

Edited by - Page47 on 05/31/2002 15:57:05

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-05-31 : 16:04:30

Employees
EmpID EmpName
36860 ****
38324 ****
29756 ****
19482 ****
26139 ****
7596 ****

EmployeeAvailability
EmpID DateAvailable
36860 1/1/2002
38324 1/4/2002
29756 1/15/2002
19482 1/22/2002
26139 1/29/2002
7596 2/5/2002

Sample Data that's all.
For right now an employee is either available or not
we're not doing measuring periods. There is only one
entry for each employee. Counting either Employees.EmpID
or EmployeeAvailability.EmpID gives same result.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-31 : 16:18:33

AHHHH, your query returns the number of employees who begin their availability that week . . . you you need is a running total . . .

<O>

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-05-31 : 16:23:33

exactly,
I tried

SELECT EmployeesAvailable, WeekAvailable
FROM (SELECT COUNT(Employees.EmpID) AS EmployeesAvailable, DATEADD(day, - (1 * DATEPART(dw, EmployeeAvailability.DateAvailable)),
EmployeeAvailability.DateAvailable) AS WeekAvailable
FROM Employees INNER JOIN
EmployeeAvailability ON Employees.EmpID = EmployeeAvailability.EmpID
GROUP BY DATEADD(day, - (1 * DATEPART(dw, EmployeeAvailability.DateAvailable)), EmployeeAvailability.DateAvailable)) A
ORDER BY WeekAvailable
COMPUTE SUM(EmployeesAvailable) BY WeekAvailable

But I only get
EmployeesAvailable WeekAvailable
2 12/29/2001

I don't know why I don't get the rest of the results

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-05-31 : 16:35:53

Thank you page47 I adapted the Guru's Solution (3)
and it works great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources