Solved

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

Posted on 2008-06-17
13
960 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 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

12 Experts available now in Live!

Get 1:1 Help Now