Solved

SQL query to capture 1st day of each month and last day of each month

Posted on 2008-06-17
13
973 Views
Last Modified: 2010-04-21
Hi

I would like to write on a table called ctcontract, this table contains all of our contract infomation. The table conatins a start date and an end date of all contracts and also the number of employees on each contract. What I would like to do is do a sum of the number of employees between a start date and an end date but with a twist of suming the number of employess each month between the start and end.

For example if I want to know the sum of employees between 01/12/07 and 31/12/08 I can use the following

Select CPCompanyid,NoEmployees as noemp from CTContracts where Fromdate  
<= '200081231' and  EndDate >= '20071201' and CTcontracts.LKContractTypeID in (0,8) Order by CPCompanyid,ctcontractid.

But what I would like to do is loop round each month between those dates for example 1/12/07 - to 31/12/07 and then 01/01/08 - 31/01/08 and so on until I get a sum of all the employees each month until Dec 08. This will give me a true representation of the number of employees per month for a whole year.

I hope I have explained clearly if not please let me know?
0
Comment
Question by:ac_davis2002
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
13 Comments
 
LVL 2

Accepted Solution

by:
howyue earned 240 total points
ID: 21801132
simple enough if i'm not misunderstanding u
Select COUNT(*), MONTH(Fromdate)
From CTContracts 
Group By MONTH(Fromdate)

Open in new window

0
 
LVL 2

Assisted Solution

by:howyue
howyue earned 240 total points
ID: 21801316
however i foresee 1 problem, for example, if one of the employee startdate is '2008-1-15' and enddate is '2008-2-10', which month should this employee falls into, jan or feb or both?
0
 

Author Comment

by:ac_davis2002
ID: 21801748
Thanks for that but can it be enhanced further for example

if I run the following I get the correct results for one month (Nov) = 93596

SELECT     SUM(NoEmployees) AS Expr1
FROM         CTContracts
WHERE     (FromDate < CONVERT(DATETIME, '2008-11-30 00:00:00', 102)) AND (EndDate > CONVERT(DATETIME, '2008-11-01 00:00:00', 102)) AND
                      (LKContractTypeID = 8)

if I run the same for Dec = 90520

SELECT     SUM(NoEmployees) AS Expr1
FROM         CTContracts
WHERE     (FromDate < CONVERT(DATETIME, '2008-12-31 00:00:00', 102)) AND (EndDate > CONVERT(DATETIME, '2008-12-01 00:00:00', 102)) AND
                      (LKContractTypeID = 8)

What I would like to do is enter the dates fromdate < 31/12/2008 and enddate > 01/11/2008 which would give me the total of the two months = 184116 total employees for the two month period. I guess it sums the number of employees for each month and adds them together
0
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)

 
LVL 2

Assisted Solution

by:howyue
howyue earned 240 total points
ID: 21802174
hi davis, u din't answer my question, for that case u wan the employee to be sum up in both jan and feb ?
meaning that both the total employee of jan & feb will take this employee into account. am i correct?
0
 

Author Comment

by:ac_davis2002
ID: 21802340
yes you are correct, sorry :)
0
 
LVL 2

Assisted Solution

by:howyue
howyue earned 240 total points
ID: 21803590
here is the solution. took me sometime to figure out to cater for that employee contract that last more than 1 months, for example employee fromdate 1/1/2008 enddate 1/6/2008 will add to the number of each months from jan - june.

i actually hav 2 solution, but i think this one is short n more efficient. for me it's kinda complicated, based on my given sample data and figure it out, if u find any situation that can cause incorrect data just post back the result.
CREATE TABLE CTContracts (
	CPCompanyid INT,
	FromDate DATETIME,
	EndDate DATETIME,
	LKContractTypeID INT
) 
INSERT INTO CTContracts
SELECT 1, '2008-01-05', '2008-01-30', 8 UNION
SELECT 1, '2008-02-01', '2008-02-25', 8 UNION
SELECT 1, '2008-02-01', '2008-03-30', 8 UNION
SELECT 1, '2008-03-01', '2008-03-30', 8 UNION
SELECT 1, '2008-04-25', '2008-05-25', 8 UNION
SELECT 2, '2008-01-05', '2008-01-30', 8 UNION
SELECT 2, '2008-01-01', '2008-01-30', 8 UNION
SELECT 2, '2008-02-01', '2008-04-30', 8 UNION
SELECT 2, '2008-05-05', '2008-05-30', 8 UNION
SELECT 2, '2008-06-05', '2008-06-06', 8 UNION
SELECT 1, '2008-01-05', '2008-03-06', 8
 
/* this table is for the ease of query, u can create ur own table or use the master..spt_values table */
CREATE TABLE MonthYear (
	[Month] INT,
)
INSERT INTO MonthYear
SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6 UNION
SELECT 7 UNION
SELECT 8 UNION
SELECT 9 UNION
SELECT 10 UNION
SELECT 11 UNION
SELECT 12
 
/* here comes the solution */
SELECT (SELECT COUNT(*) FROM CTContracts WHERE MONTH(FromDate) <= d.[Month] AND MONTH(EndDate) >= d.[Month]) TotalEmployee,
	DATENAME(mm,(DATEADD(mm, d.[Month] - 1, 0))) + ', 2008' [Month]
FROM MonthYear d

Open in new window

0
 

Author Comment

by:ac_davis2002
ID: 21804278
Hi howyue

Thanks for sticking with this as I am running out of ideas I have used your query (thank you) but I am getting different results.

Can I just have one more stab at this

if I run the following

SELECT ctcontractid, NoEmployees AS numemployees, CAST(MONTH(FromDate) AS varchar(2)) + '/1/' + CAST(YEAR(FromDate) AS varchar(4)) AS firstdate, DATEADD(d,
                      - 1, DATEADD(m, 1, CAST(MONTH(FromDate) AS varchar(2)) + '/1/' + CAST(YEAR(FromDate) AS varchar(4)))) AS lastdate
FROM         CTContracts
WHERE (( EndDate >= CONVERT(DATETIME, '2008-12-01 00:00:00', 102) AND FromDate <= CONVERT(DATETIME, '2008-12-31 00:00:00', 102) ))

I get the correct result for dec, if I run again I get the correct results for Nov

But what I need to do is run for dec + nov and total the results for each and then add together can this be done if If I am not undertsanding what you have sent me?


0
 
LVL 14

Assisted Solution

by:NBSO_ISS
NBSO_ISS earned 260 total points
ID: 21805288
Just a note... here is a formula to get the first and last day of the month given any day in that month...

For the last day use...

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,1,@Today))

@Today being any day in that month...

for the 1st day use...

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,0,@Today))

@Today being any day in that month...
0
 

Author Comment

by:ac_davis2002
ID: 21805455
Thanks NBSO I was just going to ask that very question!!!!

I am thinking about going about this in a different way, if I can use your string to select the first day of each month and a string to get the last day of each month

I could then (somehow!)Loop step 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
Something like this

Select NoEmployees as noemp
      from CTContracts
      Where Fromdate  <= 'DateEdStr' And  EndDate >= 'DateStStr'


Any ideas as to how i could go about this?
0
 
LVL 14

Assisted Solution

by:NBSO_ISS
NBSO_ISS earned 260 total points
ID: 21805505
Do you want to get a total or the number for each of the months?
0
 
LVL 14

Assisted Solution

by:NBSO_ISS
NBSO_ISS earned 260 total points
ID: 21805882
This should do whatever you want with the NoEmployees returned for each month span...


--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,'Jan 1, 2008' ,111)
Set @endDate  = convert(varchar,'March 1 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
         --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
 

Author Comment

by:ac_davis2002
ID: 21805985
Thanks NBSO thats great!!!

Thanks for you help also howyue!!
0
 

Author Closing Comment

by:ac_davis2002
ID: 31468092
Thanks for your time chaps

Adam
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

696 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