Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2008-06-17
13
Medium Priority
?
980 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 960 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 960 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Assisted Solution

by:howyue
howyue earned 960 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 960 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 1040 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 1040 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 1040 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

610 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