?
Solved

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

Posted on 2008-06-17
13
Medium Priority
?
979 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

764 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