December 7, 2009 at 3:42 am
Find all the employees who have joined at last day of month in last 3 years (2007,2008,2009)
December 7, 2009 at 3:51 am
Ankur Bhardwaj (12/7/2009)
Find all the employees who have joined at last day of month in last 3 years (2007,2008,2009)
Can you please supply some sample data in a readily-consumable format? The link below will show you how to do this.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 7, 2009 at 4:26 am
Ankur Bhardwaj (12/7/2009)
Find all the employees who have joined at last day of month in last 3 years (2007,2008,2009)
Hi,
First you get the last date for all month like
create table #temp
(
slno int IDENTITY(1,1),
month_name varchar(12)null,
Begin_date datetime null,
Last_date datetime null
)
GO/*insert the values 1 to 100*/
WHILE 1 = 1
BEGIN
INSERT INTO #temp DEFAULT VALUES
IF @@IDENTITY = 100
BEGIN
BREAK
END
END
GO/*insert the Month name*/
UPDATE #temp
SET month_name = STUFF(CONVERT(CHAR(11),DATEADD(mm, slno-1, '01/01/2005'),100),4,3,'')
WHERE slno <= DATEDIFF(mm,'01/01/2005','12/31/2010')
GO/*insert the begin date for that month*/
update #temp
set Begin_date = CAST(('01 '+(CAST(month_name AS CHAR)))AS DATETIME)
GO/*insert the Last date for that month*/
update #temp
set Last_date = dateadd(mm, datediff(mm, 0, Begin_date) + 1, 0)-1
then try to use this temp table like
select * from MY_EMP_TABLE a
inner join #temp b
on a.join_date = b.Last_date
December 7, 2009 at 7:17 am
You need to be careful with Arun's solution as SQL Server stores date and time, so if you used a function like GetDate() to set the Join Date you would have a time portion you need to take that into account. I would more likely to do something like this:
DECLARE @last_day_of_month TABLE(date_start DATETIME, date_end DATETIME);
WITH cteNums AS
(
SELECT TOP 36
ROW_NUMBER() OVER(ORDER BY AC.NAME) AS N
FROM
sys.all_columns AS AC CROSS JOIN
sys.all_columns AS AC2
)
INSERT INTO @last_day_of_month (
date_start,
date_end
)
SELECT
DATEADD(month, N, '12/31/2006'),
DATEADD(DAY, 1, DATEADD(MONTH, N, '12/31/2006'))
FROM
cteNums
WHERE
DATEADD(month, N, '12/31/2006') <= '12/31/2009';
SELECT
*
FROM
@last_day_of_month AS LD JOIN
emp_table AS ET ON
ET.join_date >= LD.date_start AND
ET.join_date < LD.end_date
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 7, 2009 at 9:58 am
Even this should work, say the name of table is Employees and the name of the column is DateOfJoin.
SELECT*
FROM Employees
WHEREDATEPART( DAY, DATEADD( DAY, 1, DateOfJoin ) ) = 1
ANDDATEDIFF( YEAR, DateOfJoin, GETDATE() ) IN (0,1,2)
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 7, 2009 at 12:24 pm
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE orderdate = DATEADD(month, DATEDIFF(month, '19991231', orderdate), '19991231');
--Here our orderdate field is year, month, day
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply