asked on

SQL query to capture no employees per contract over a given time

Hi

Hi I have a query (please see attached code snippet) that loops through a table called CTcontracts, it Loops through each month of the Period selected i.e. with first day of the first month = Start date, last day of the first month = End date looping one month at a time to the end of the period selected.

In each loop return the number of Employees for each month from the CTContracts table which has a contract during queried Month and
which the lkcontrac type ID is = to the lkcontract ID selected. It then inserts the results by month into a temp table and does a sum on each row giving me an over number of employees of each month over the period

I would like to enhance this so the query loops through a table called CPCompany selecting the compnay ids of companies that have a lkSICodeID (industrycode) = 43. the lkSICodeID is in a table called CPSICodes which can be joined on cp company by companyid. I would then like to select the noemployess from contracts between the time period where the compainid exists in the first loop.

I basically need to end up with the number of employees per contract within a time period where the company has a indusry code of 43.

This is an example of the query

Select Distinct CPCompany.CPCompanyID from CPCompany
      Join CPSICodes on CPCompany.CPCompanyID = CPSICodes.CPCompanyID
      Join CTcontracts on CTcontracts.CPCompanyID = CPCompany.CPCompanyID
Where CPSICodes.lkSICodeID = 43
      And Fromdate between @startdate and @tmpEndDate
      Or EndDate between @startdate And @tmpEndDate

This is slightly different from the attached query which gives me the number of employees per contract within a time period where contracttype id = 8.

--Declare Start and End variables (these can also be passed into a stored procedure)
DECLARE @startdate 	smalldatetime,
	@endDate   	smalldatetime
 
--this drops the hours and minutes and sets them to 00:00
Set @startdate  = convert(varchar,'Nov 1, 2008' ,111)
Set @endDate  = convert(varchar,'Dec 31 2008' ,111)
 
--Declare EnD Date tmp variable to be used in the loop
DECLARE @tmpEndDate	smalldatetime
--Declare tmp table to act as a temporary holding place for your data
DECLARE @tmpTable	TABLE(
			StartDate	smalldatetime,
			EndDate		smalldatetime,
			noemp 		integer)
--Set @startdate to beginning of month span
Set @startdate =  DATEADD(dd,-(DAY(DATEADD(m,1,@startdate))-1),@startdate)
--set @endDate to end of month span
Set @endDate =  DATEADD(dd, -DAY(DATEADD(m,1,@endDate)), DATEADD(m,1,@endDate))
 
--loop through each month
While @startdate < @endDate
BEGIN
         --set @tmpEndDate to last minute of month
	Set @tmpEndDate = dateadd(n,-1,dateadd(m,1,@startdate))
         --insert values in table
	Insert into @tmpTable(StartDate,EndDate,noemp) 
		Select @startdate as startdate, @tmpEndDate as enddate, NoEmployees as noemp 
		      from CTContracts 
		      Where Fromdate  <= @tmpEndDate And  EndDate >= @startdate
		      and LKContractTypeID = 8
         --increment @startdate 
	Set @startdate = dateadd(m,1,@startdate)
END
 
--Now you can do what you want with the results
Select * from @tmpTable
Select Min(StartDate) as StartDate, Max(EndDate) as EndDate, Sum(noemp) as noemp from @tmpTable

Open in new window

climbingjaffa

Why don't you just create a table variable identifying companys with industry codes of 43 and then just limit your current query's scope based on the contents of the table variable ...or am i over simplify your problem? i.e. have i missed teh problem completly

Note: (depending on the number of companies you may want to use a temp table)

ac_davis2002

ASKER

sounds like what I need, are you able to give me an example of the query I have attached with a table variable in it, i have not used one before

ac_davis2002

ASKER

I have just tried using a table variable and it distortes my figures. I need to loop through i think. I need all the companies that have and inderstry type of 43 then I need all the contracts associated with the companies then I need a sum of employees from those contracts sum per month

i am probably not explaining myself very well?

ASKER CERTIFIED SOLUTION

climbingjaffa

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ac_davis2002

ASKER

Great I will, this is gonna sound very dome but can you give me the command to create a table variable. sorry to ask you to spoon feed me but I have not done one before. I am using sql2000 does that make a difference?

ac_davis2002

ASKER

Scub that request I got the syntax!

ac_davis2002

ASKER

OK I think I have done as you have suggested

--Declare Start and End variables (these can also be passed into a stored procedure)
DECLARE @startdate       smalldatetime,
      @endDate       smalldatetime

--this drops the hours and minutes and sets them to 00:00
Set @startdate = convert(varchar,'DEC 1 2007' ,111)
Set @endDate = convert(varchar,'DEC 31 2008' ,111)

--Declare EnD Date tmp variable to be used in the loop
DECLARE @tmpEndDate      smalldatetime
--Declare tmp table to act as a temporary holding place for your data
DECLARE @tmpTable      TABLE(
                  StartDate      smalldatetime,
                  EndDate            smalldatetime,
                  CPCompanyid      integer,
                  NoEmp             integer)
--Set @startdate to beginning of month span
Set @startdate = DATEADD(dd,-(DAY(DATEADD(m,1,@startdate))-1),@startdate)
--set @endDate to end of month span
Set @endDate = DATEADD(dd, -DAY(DATEADD(m,1,@endDate)), DATEADD(m,1,@endDate))

--loop through each month
While @startdate < @endDate
BEGIN

declare @industry_type_43 table (cpcompanyid int)
insert into @industry_type_43 (cpcompanyid)
select cpcompany.cpcompanyid
from cpcompany inner join cpsicodes
on cpcompany.cpcompanyid = cpsicodes.cpcompanyid
where cpsicodes.LKSICodeID = 43

--set @tmpEndDate to last minute of month
      Set @tmpEndDate = dateadd(n,-1,dateadd(m,1,@startdate))
--insert values in table
Insert into @tmpTable(StartDate,EndDate,noemp)
Select Distinct @startdate as startdate, @tmpEndDate as enddate, NoEmployees as noemp
from CTContracts
inner join @industry_type_43 on CTcontracts.CPCompanyID = @industry_type_43.CPCompanyID
Where Fromdate <= @tmpEndDate And EndDate >= @startdate
and LKContractTypeID = 8
--increment @startdate
Set @startdate = dateadd(m,1,@startdate)

--increment @startdate
      Set @startdate = dateadd(m,1,@startdate)
END

--Now you can do what you want with the results
Select * from @tmpTable
Select Min(StartDate) as StartDate, Max(EndDate) as EndDate, sum(noemp) from @tmpTable

but I am getting the error

Must declare the variable '@industry_type_43'. Have I put it in the wrong place?

ac_davis2002

ASKER

All solved climbingjaffa

Thanks a lot for your help, really useful trick to know.

Adam

ac_davis2002

ASKER

Thanks again