Solved

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

Posted on 2008-06-18
9
407 Views
Last Modified: 2010-04-21
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

0
Comment
Question by:ac_davis2002
  • 7
  • 2
9 Comments
 
LVL 2

Expert Comment

by:climbingjaffa
ID: 21813824
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
 

Author Comment

by:ac_davis2002
ID: 21814314
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
 

Author Comment

by:ac_davis2002
ID: 21814833
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
 
LVL 2

Accepted Solution

by:
climbingjaffa earned 500 total points
ID: 21815717
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:ac_davis2002
ID: 21815903
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
 

Author Comment

by:ac_davis2002
ID: 21816207
Scub that request I got the syntax!
0
 

Author Comment

by:ac_davis2002
ID: 21817227
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
 

Author Comment

by:ac_davis2002
ID: 21820842
All solved climbingjaffa

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

Adam
0
 

Author Closing Comment

by:ac_davis2002
ID: 31468351
Thanks again
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Dynamics crm 2011 8 43
Microsoft SQL Queries 11 48
MIcrosoft SQL, query with ANY joins 13 28
Server 2008 Cluster Fail-over Errors 5 43
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now