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

ac_davis2002Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

climbingjaffaCommented:
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)
0
ac_davis2002Author Commented:
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
0
ac_davis2002Author Commented:
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?
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

climbingjaffaCommented:
Ok first create a table variable called @industry_type_43 insert into that the company identifiers of all the companies with industry 43.

then use the table variable like below ...this is from the loop in the table sample in the
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)
Notice the distinct in the select cause ...this is a bit hacky but i don't understand your tablle structure and it sounds like you are getting duplicats in the results table maybe...

also i join against the contracts table to limit our search to companies that we care about i.e. industry code 43

try that set up a small test case and work you way towards the solution ...i'll be online later today but only breifly tomorrow and friday (wedding..not mine) so best of luck
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ac_davis2002Author Commented:
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?
0
ac_davis2002Author Commented:
Scub that request I got the syntax!
0
ac_davis2002Author Commented:
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?
0
ac_davis2002Author Commented:
All solved climbingjaffa

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

Adam
0
ac_davis2002Author Commented:
Thanks again
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.