Loop, auto update - 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

General SQL Server Forums

New to SQL Server Programming

Loop, auto update

Author

Topic

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-16 : 05:19:04

Hi,

I am back with some more questions.

The structure is something like this -

Table 1 - dbo.DimHC_Team - This stores TeamID and TeamName
Table 2 - dbo.DimHC_Team_Config - This has TeamID TeamDate (not really used anywhere) TeamHeadCount (Max strength of a team)

Table 3 - dbo.DimHC_Team_Agg - This has TeamID, Team_Date (1st of every month, so that when I see the data for past months I use these dates) , Permanent Employees, Contracted employees, Open positions, Total, HeadCount.

Now, I have a stored procedure which goes like this -

declare @datevar datetime
declare @datevar2 datetime
declare @datevar3 datetime
declare @datevar4 datetime
select @datevar = DATEADD(dd, -(Day(getdate()) -1) ,getdate())
select @datevar = dateadd(dd, datediff(d,0,@datevar),0)
select @datevar2 = Dateadd(m , 1 , @datevar)
select @datevar4 = Dateadd(dd , 9 , @datevar2)
select @datevar3 = dateadd(m,2,@datevar)
select @datevar3 = dateadd(dd, -2 , @datevar3)

if (not exists (select * from dbo.DimHC_Team_Agg where Team_Date = @datevar2))
begin

insert into dbo.DimHC_Team_Agg
(TeamID, Team_Date)
select TeamID , @datevar2 from dbo.DimHC_Team_Config
end

update dbo.DimHC_Team_Agg
set Team_Head_Count = dbo.DimHC_Team_Config.Team_Head_Count
from dbo.DimHC_Team_Config, dbo.DimHC_Team_Agg
where dbo.DimHC_Team_Agg.Team_Date = @datevar2
and dbo.DimHC_Team_Config.TeamID = dbo.DimHC_Team_Agg.TeamID

update dbo.DimHC_Team_Agg
set
Perm_Posn = (select count(EmpType) from dbo.uview_DimHC_Emp_view1
where EmpStartDate < = @datevar4 and EmpType = 'Permanent'
and (EmpEndDate = '01/01/1753' or EmpEndDate > @datevar3)
and TeamID = '1'),
Contract_Posn = (select count(EmpType) from dbo.uview_DimHC_Emp_view1
where EmpStartDate < = @datevar4 and EmpType = 'Contractor'
and (EmpEndDate = '01/01/1753' or EmpEndDate > @datevar3)
and TeamID = '1')
......

What this block is doing is converting today's date to the first of this month. Going one month and 2 months in advance. Then it checks if any employee's start date < 10th of the next month and end date > 31st of the next month and if both the conditions are met then increments the employee count for that team as 1.

This TeamID is then repeated for 13 teams that I have right now. So that every month I will run it and it will update.

Now, I have 2 problems - First being that I had to write this block for 13 teams which isn't really a smart thing to do. Let's say I add a team tomorrow then I will need to modify this stored procedure and then tomorrow I may not be here, so the person who uses it should be able to do it easily. So, I wanted to know if I can loop or something through this with the max limit of TeamID coming from some external source and not like fix it to 20 or something.

Second, let's say in the beginning of this month I add a new team. The way I will do it is I will add it to dbo.DimHC_Team and dbo.DimHC_Team_Config table. Now, if I run this stored procedure then

if (not exists (select * from dbo.DimHC_Team_Agg where Team_Date = @datevar2))

is not going to return NULL as there will be entries with some teams right now. So this new Team won't be inserted in the dbo.DimHC_Team_Agg table and I will have to manually do it.

Can somebody help me as to how can I get over with these two problems?

Sorry for being really long but I thought it best to give all the details.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 05:23:05

Can you explain what you want by means of some data from your tables and reqd output?

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-16 : 05:26:18

I basically want to generate reports in table form with rows as Team Names and Columns as Months and team strength as
entries for each month.
Then I want things like Team's total permanent employees in a particular month. Open positions for a period etc.

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-16 : 05:28:15

Jun-07 Jul-07 Aug-07 Sep-07
GSC TEAMS
DBA 12 11 11 10
Messaging 8 8 8 8
EM Ops 12 13 13 13
Unix 15 15 15 15
Web 17 17 17 15
Windows 20 20 21 20
GSC Connectivity 0 0 0 0
Ops Mgt & Info Analyst 5 6 6 5

Then something like -

Team Name Open Positions Permanent Position Contract Positions Team Total Head Count
DBA 1 12 0 12 13
EmOps 1 11 0 11 12
Management 2 10 0 10 12
Messaging 0 9 0 9 9

Something like -

No of Executives No of Managers No of ADs Total No of Employees
84 4 4 92

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 05:30:04

How will be your table data?

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-16 : 05:34:15

My dbo.DimHC_Team_Agg table has exactly all this information which I will update at the beginning of every month.
I read from the employee and emp-team database about all the employees, their corresponding teams and their start dates and end dates determine whether they are still going to be working or net. Correspondingly I update all the counts and put it in this table.

So this table has the following fields -

TeamID Team_Date (1st of every month) Perm_Posn Contract_Posn Open_Posn Team_Total Team_Head_Count

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-16 : 05:41:08

If you were asking about the reports table data then it will be something like the one I posted before. Month names, Team Names and number etc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 06:00:13

instead of harcoding the Team IDs is nt if enough if you do like this?

update ta
set
ta.Perm_Posn = COUNt(case when ta.EmpType = 'Permanent' THEN EmpType ELSE NULL END),
ta.Contract_Posn =COUNt(case when ta.EmpType = 'Contractor' THEN EmpType ELSE NULL END),.... for others
from dbo.DimHC_Team_Agg ta
dbo.uview_DimHC_Emp_view1 ev
ON ev.TeamID=ta.TeamID
where ta.EmpStartDate < = @datevar4 
and (ta.EmpEndDate = '01/01/1753' or ta.EmpEndDate > @datevar3)

Also i think the initial insert should be like this

insert into dbo.DimHC_Team_Agg 
(TeamID, Team_Date)
select TeamID , @datevar2 
from dbo.DimHC_Team_Config  tc
LEFT JOIN dbo.DimHC_Team_Agg  ta
ON ta.TeamID=tc.TeamID
AND Team_Date=@datevar2 
WHERE TeamID IS NULL

then you wont require that if condition at all

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-17 : 02:25:54

I did the following -

update ta
set
ta.Perm_Posn = (select COUNt(case when ta.EmpType = 'Permanent' THEN EmpType ELSE NULL END)),
ta.Contract_Posn =(select COUNt(case when ta.EmpType = 'Contractor' THEN EmpType ELSE NULL END))
from dbo.DimHC_Team_Agg ta,
dbo.uview_DimHC_Emp_view1 ev
LEFT JOIN ta
ON ev.TeamID=ta.TeamID
where tb.EmpStartDate < = @datevar4
and (ta.EmpEndDate = '01/01/1753' or ta.EmpEndDate > @datevar3)

It is giving me the following error -
Msg 208, Level 16, State 1, Line 25
Invalid object name 'ta'.

Any idea why?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-17 : 02:34:45

quote:
Originally posted by avkuvalekar

I did the following -

update ta
set
ta.Perm_Posn = (select COUNt(case when ta.EmpType = 'Permanent' THEN EmpType ELSE NULL END)),
ta.Contract_Posn =(select COUNt(case when ta.EmpType = 'Contractor' THEN EmpType ELSE NULL END))
from dbo.DimHC_Team_Agg ta,
dbo.uview_DimHC_Emp_view1 ev
LEFT JOIN ta
ON ev.TeamID=ta.TeamID
where tb.EmpStartDate < = @datevar4
and (ta.EmpEndDate = '01/01/1753' or ta.EmpEndDate > @datevar3)

It is giving me the following error -
Msg 208, Level 16, State 1, Line 25
Invalid object name 'ta'.

Any idea why?

Thanks

ta is unknown to subquery . Avoid using subqueries and use join like the code i posted earlier.

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-17 : 02:37:19

And I saw your post again and I am not quite sure if that would work.

To explain in a bit more detail, my view uview_DimHC_Emp_view1 has details like Employee Name, Team, Level (Executive, Manager, AD etc), Type (Permanent, Contractor), Start Date, End Date etc.
Now, I want to calculate the number of permanent employees for a particular month and put them in the team aggregate (dbo.DimHC_Team_Agg ) table so that I can generate reports based on this.

The statement that you have given counts permanent employees from the view but doesn't check for the TeamID, does it?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-17 : 02:50:28

quote:
Originally posted by avkuvalekar

And I saw your post again and I am not quite sure if that would work.

To explain in a bit more detail, my view uview_DimHC_Emp_view1 has details like Employee Name, Team, Level (Executive, Manager, AD etc), Type (Permanent, Contractor), Start Date, End Date etc.
Now, I want to calculate the number of permanent employees for a particular month and put them in the team aggregate (dbo.DimHC_Team_Agg ) table so that I can generate reports based on this.

The statement that you have given counts permanent employees from the view but doesn't check for the TeamID, does it?

Thanks!

Thats done by Join on TeamID, it will ensure only counts of concerned team are put against teamid so you dont require an explicit check for TeamID. Join will take only matching records corresponding to each team id and then take count.

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-17 : 03:02:06

select count(case when dbo.uview_DimHC_Emp_view1.EmpType = 'Permanent' THEN EmpType ELSE NULL END) from dbo.DimHC_Team_Agg
LEFT OUTER JOIN dbo.uview_DimHC_Emp_view1
on dbo.uview_DimHC_Emp_view1.TeamID = dbo.DimHC_Team_Agg.TeamID

This gives me the count as 420! I am clueless! :(

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-17 : 03:05:01

Or even this returns 420, shouldn't I be getting a column of values for each team ID?

select count(case when dbo.uview_DimHC_Emp_view1.EmpType = 'Permanent' THEN EmpType ELSE NULL END) from dbo.uview_DimHC_Emp_view1
LEFT OUTER JOIN dbo.DimHC_Team_Agg
on dbo.uview_DimHC_Emp_view1.TeamID = dbo.DimHC_Team_Agg.TeamID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-17 : 03:34:57

quote:
Originally posted by avkuvalekar

Or even this returns 420, shouldn't I be getting a column of values for each team ID?

select count(case when dbo.uview_DimHC_Emp_view1.EmpType = 'Permanent' THEN EmpType ELSE NULL END) from dbo.uview_DimHC_Emp_view1
LEFT OUTER JOIN dbo.DimHC_Team_Agg
on dbo.uview_DimHC_Emp_view1.TeamID = dbo.DimHC_Team_Agg.TeamID

What you've done is taking entire count from table. If you're looking at team wise counts you should group by team id.

check this out:-

select dbo.uview_DimHC_Emp_view1.TeamID ,count(case when dbo.uview_DimHC_Emp_view1.EmpType = 'Permanent' THEN EmpType ELSE NULL END) 
from dbo.uview_DimHC_Emp_view1 
LEFT OUTER JOIN dbo.DimHC_Team_Agg 
on dbo.uview_DimHC_Emp_view1.TeamID = dbo.DimHC_Team_Agg.TeamID
GROUP BY dbo.uview_DimHC_Emp_view1.TeamID

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-17 : 04:08:32

Yeah, I figured that out but how do I update teamwise??

I did this -

select dbo.DimHC_Team_Agg.TeamID ,
count(case when dbo.uview_DimHC_Emp_view1.EmpType = 'Permanent'
THEN dbo.uview_DimHC_Emp_view1.EmpType ELSE NULL END) as Perm_Posn
from dbo.DimHC_Team_Agg LEFT OUTER JOIN dbo.uview_DimHC_Emp_view1
on dbo.uview_DimHC_Emp_view1.TeamID = dbo.DimHC_Team_Agg.TeamID
where dbo.DimHC_Team_Agg.Team_Date = '01/07/2008'
group by dbo.DimHC_Team_Agg.TeamID

which gives me a team wise counts but I just don't seem to be able to update! :(

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-17 : 04:45:36

1 12
2 9
3 12
4 16
5 14
6 21
7 10
8 11
9 0
10 0
11 0
12 0
13 0

I do get something like this but how to update?

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-17 : 04:52:05

The trouble is in update statement I won't be able to select dbo.DimHC_Team_Agg.TeamID as I did above.
And if I remove the dbo.DimHC_Team_Agg.TeamID from select then I get the total that is 105! Total number of employees.

This is the statement that, if I use in update, will be equated to Perm_Posn and this returns 105.

select count(case when dbo.uview_DimHC_Emp_view1.EmpType = 'Permanent'
THEN dbo.uview_DimHC_Emp_view1.EmpType ELSE NULL END) as Perm_Posn
from dbo.DimHC_Team_Agg LEFT OUTER JOIN dbo.uview_DimHC_Emp_view1
on dbo.uview_DimHC_Emp_view1.TeamID = dbo.DimHC_Team_Agg.TeamID
where dbo.DimHC_Team_Agg.Team_Date = '01/07/2008'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-17 : 04:55:25

Can you see what i've done below?

update ta
set
ta.Perm_Posn = COUNt(case when ta.EmpType = 'Permanent' THEN EmpType ELSE NULL END),
ta.Contract_Posn =COUNt(case when ta.EmpType = 'Contractor' THEN EmpType ELSE NULL END),.... for others
from dbo.DimHC_Team_Agg ta
dbo.uview_DimHC_Emp_view1 ev
ON ev.TeamID=ta.TeamID
where ta.EmpStartDate < = @datevar4 
and (ta.EmpEndDate = '01/01/1753' or ta.EmpEndDate > @datevar3)

avkuvalekar
Starting Member

38 Posts

Posted - 2008-06-17 : 04:59:08

Incorrect syntax near the keyword ON

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-17 : 05:01:12

quote:
Originally posted by avkuvalekar

Incorrect syntax near the keyword ON

sorry missed a join

update ta
set
ta.Perm_Posn = COUNt(case when ev.EmpType = 'Permanent' THEN ev.EmpType ELSE NULL END),
ta.Contract_Posn =COUNt(case when ev.EmpType = 'Contractor' THEN ev.EmpType ELSE NULL END),.... for others
from dbo.DimHC_Team_Agg ta
INNER JOIN dbo.uview_DimHC_Emp_view1 ev
ON ev.TeamID=ta.TeamID
where ta.EmpStartDate < = @datevar4 
and (ta.EmpEndDate = '01/01/1753' or ta.EmpEndDate > @datevar3

)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources