Datediff

[Lucinda]

Hello Access Gurus:

I have an Access 2003 database that I am trying to calculate datepart and
datediff; I need assistance with the datediff portion. Here is the query I
have created so far:
SELECT CASE WHEN datepart(mm, dbo.PS_EMPLOYMENT.HIRE_DT) < 07 THEN
SUM(16 - 0) ELSE SUM(8 - 0) END AS Starter_Floater,
dbo.[2009_Vac_Balances12-23].EMPLID,
dbo.[2009_Vac_Balances12-23].NAME
FROM dbo.PS_EMPLOYMENT LEFT OUTER JOIN
dbo.[2009_Vac_Balances12-23] ON
dbo.PS_EMPLOYMENT.EMPLID = dbo.[2009_Vac_Balances12-23].EMPLID
GROUP BY dbo.PS_EMPLOYMENT.HIRE_DT, dbo.[2009_Vac_Balances12-23].EMPLID,
dbo.[2009_Vac_Balances12-23].NAME

I used Case to give me part of the date <07 the month of July to run a
calculation; now I need to know 2nd year employees; the calculation after one
year of employment will always start with 16. How do I calculate the
datediff: I started with this:
HAVING (DATEDIFF(YY, dbo.PS_EMPLOYMENT.HIRE_DT, GETDATE()) = 0)

Unfortunately it returns nothing. Any ideas?

 

[Duane Hookom]

Without reviewing your entire logic, it is important for you to understand
how DateDiff() works:
SELECT DateDiff(yy,'12/31/2009','1/1/2010') = 1
SELECT DateDiff(d,'12/31/2009','1/1/2010') = 1

If you want to find out if something is a year old, you must use another
method.

Your expression "SUM(16 - 0)" really has me confused.

 

[Lucinda]

Yes, that is what I had wrong. This is a starting point for a brand new
database. I am trying to caclulate 2009 balances, which is why there is
16-0;16 being the amount given at the beginning of the year for employees
hired in the months 1-7
and 8-0; 8 for the employees hired after month 7. When we go live that will
read 16 or 8-floaterused as balance.

Thanks for your help.

Without reviewing your entire logic, it is important for you to understand
how DateDiff() works:
SELECT DateDiff(yy,'12/31/2009','1/1/2010') = 1
SELECT DateDiff(d,'12/31/2009','1/1/2010') = 1

If you want to find out if something is a year old, you must use another
method.

Your expression "SUM(16 - 0)" really has me confused.

--
Duane Hookom
Microsoft Access MVP

Hello Access Gurus:

I have an Access 2003 database that I am trying to calculate datepart and
datediff; I need assistance with the datediff portion. Here is the query I
have created so far:
SELECT CASE WHEN datepart(mm, dbo.PS_EMPLOYMENT.HIRE_DT) < 07 THEN
SUM(16 - 0) ELSE SUM(8 - 0) END AS Starter_Floater,
dbo.[2009_Vac_Balances12-23].EMPLID,
dbo.[2009_Vac_Balances12-23].NAME
FROM dbo.PS_EMPLOYMENT LEFT OUTER JOIN
dbo.[2009_Vac_Balances12-23] ON
dbo.PS_EMPLOYMENT.EMPLID = dbo.[2009_Vac_Balances12-23].EMPLID
GROUP BY dbo.PS_EMPLOYMENT.HIRE_DT, dbo.[2009_Vac_Balances12-23].EMPLID,
dbo.[2009_Vac_Balances12-23].NAME

I used Case to give me part of the date <07 the month of July to run a
calculation; now I need to know 2nd year employees; the calculation after one
year of employment will always start with 16. How do I calculate the
datediff: I started with this:
HAVING (DATEDIFF(YY, dbo.PS_EMPLOYMENT.HIRE_DT, GETDATE()) = 0)

Unfortunately it returns nothing. Any ideas?