Solved

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

Posted on 2008-06-18
9
414 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server maintenance plan 8 53
SQL Query incorrect syntax near the keyword ON any help would be appreciated 18 27
SQL eating up memory? 16 37
Need help with a query 14 34
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

735 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