November 17, 2006 at 9:34 am
CREATE VIEW dbo.OcchEmp
AS
SELECT DISTINCT dbo.HREMP_adp.SSN,dbo.HREMP_adp.HIRE_DATE, dbo.HREMP_adp.DOB, HREMP_adp. EMPLNO , dbo.HREMP_adp.[FULL NAME], dbo.Entity.Code as Unit, dbo.Entity.Description AS [Unit Desc], dbo.Manager_List.Department as Department,dbo.Manager_List.Manager as Manager , dbo.HREMP_adp.CC as JobCCNo
FROM dbo.HREMP_adp INNER JOIN
dbo.Entity ON dbo.HREMP_adp.ENTITY = dbo.Entity.Code INNER JOIN dbo.CCAndRequiredModule ON dbo.HREMP_Adp.CC = dbo.CCAndRequiredModule.[JobCost_NBR] INNER JOIN
Manager_List ON HREMP_adp.CC = Manager_List.HOME_JOBCOST_NBR
WHERE (dbo.HREMP_adp.EMPSTATUS <> 90) OR
(DATEDIFF(dd, dbo.HREMP_adp.HIRE_DATE, dbo.HREMP_adp.TERMDATE) < 0)
the above is my data view. I want to extract the employees which the hired_date is the previous fisal year which is 07/01/05-06/30/06.
Since I do not want to hard code the date as hired_date between and .
What way is the better way to do that ? Thanks.
here is some of the vb.net code
If Now.Month > 7 Then
BDate =
New DateTime(Now.Year, 7, 1)
Else
BDate =
New DateTime(Now.Year - 1, 7, 1)
End If
Dim EDate As DateTime = BDate.AddYears(1).AddDays(-1)
I think something similiar like this
select * from occhemp where hire_date between year(getdate()-5)-07-01 and year(getdate()+1)-06-30
but I do not know how to correct format this.
November 17, 2006 at 9:59 am
If I am reading you correctly, you want to always grab the previous fiscal year. This means that your month and days will always be the same because your fiscal year does not change, but you need the year portion of your date to by dynamic so that the query will not need changed every year. Is this correct? If so, try this:
declare @occhemp table (hire_date datetime)
insert into @occhemp
select '2006-07-01' union all
select '2005-07-01' union all
select '2006-05-01' union all
select '2005-09-01' union all
select '2006-08-01' union all
select '2005-10-01'
select *
from @occhemp
where hire_date between DATEADD(yy,-1,CAST(CONVERT(varchar(4),YEAR(GETDATE())) + '-07-01' as datetime))
and CAST(CONVERT(varchar(4),YEAR(GETDATE())) + '-06-30' as datetime)
November 17, 2006 at 10:08 am
Thax. that is what I want.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply