Function To Calculate Holidays

May 31, 2006

Hello,

First things first - I have a table which holds employee information (tbl_EmployeeDetails), and another table which holds information about the holidays they have booked (tbl_Holidays).

If an employee books 5 days off, 5 rows will appear in the tbl_Holidays table, each line showing 1 under the day field and 7 under the hours field.

I'm trying to produce a function which will do the following :

1) Pass in the employee number
2) Establish whether the employee works full time or part time by looking up to tbl_employeedetails, and checking the fulltime flag
3) If full time, look up to tbl_Holidays and count number of days
4) If part time, look up to tbl_Holidays and count number of hours
5) After this, return the number of holidays booked

My code is as follows :
=============
CREATE FUNCTION [dbo].[fn_Get_Booked_Holidays_Current_Year] ( @EmpNo int )

RETURNS Float
AS
BEGIN

-- Declare fields
DECLARE @FullTime int

-- Determine if Part Time or Full Time
SET @FullTime = SELECT FullTime FROM tbl_EmployeeDetails

IF @FullTime = 1
SELECT COUNT(NumberOfDays) AS TotalHolidays, EmployeeNumber AS EERef
FROM dbo.tbl_Holidays
GROUP BY EmployeeNumber
HAVING (EmployeeNumber = @EmpNo)

IF @FullTime = 0
SELECT COUNT(NumberOfDays) AS TotalHolidays, EmployeeNumber AS EERef
FROM dbo.tbl_Holidays
GROUP BY EmployeeNumber
HAVING (EmployeeNumber = @EmpNo)

END
==========

Can someone please let me know where I'm going wrong, or what I need to do to get this function done?

Thanks,
J

View 2 Replies


ADVERTISEMENT

Calculate Date Difference Excluding Bank Holidays

Aug 28, 2013

I have a Bank holiday table (ID column, and Date column), how to exclude bank holidays in the datediff returned.

Create FUNCTION [dbo].days_diff ( @date1 datetime,@date2 datetime )
RETURNS int
AS
BEGIN
declare @i int
Declare @count int

[Code] .....

View 4 Replies View Related

SQL Server 2012 :: Calculate Number Of Days From A Date - Exclude Weekends And Holidays

Feb 2, 2014

I have already created a table name 'tblHolidays' and populated with 2014 Holidays. What I would like is be able to calculate (subtract or add) number of days from a date. For example subtract 2 days from 07/08/2014 and function should return 07/03/2014.

CREATE FUNCTION [dbo].[ElapsedBDays] (@Start smalldatetime, @End smalldatetime)
RETURNS int
AS
BEGIN
/*
Description:
Function designed to calculate the number of business days (In hours) between two dates.

[Code] ......

View 4 Replies View Related

Arranging Holidays

Feb 2, 2008

Hello
In my ASP 2.0 website, I wish to arrange holidays and specific days from database with using Sql Server. What SQL query I must add to this following code? If you can post the required codes, I'd be most thankful. Regards.private DateTime _siparis;public DateTime Siparis
{
get { return _siparis; }set { _siparis = value; }
}protected void Button1_Click(object sender, EventArgs e)
{Timer1.Enabled = true;
DateTime sonTarih = DateTime.Today.AddDays(1).AddHours(13);Session["siparis"] = sonTarih;
TimeSpan aradakiFark = sonTarih - DateTime.Now;StringBuilder sb = new StringBuilder();
sb.Append("Remaining time is ");if (aradakiFark.Days > 0)
{
sb.Append(aradakiFark.Days.ToString());sb.Append(" days ");
}if (aradakiFark.Hours > 0)
{
sb.Append(aradakiFark.Hours.ToString());sb.Append(" hours ");
}if (aradakiFark.Minutes > 0)
{
sb.Append(aradakiFark.Minutes.ToString());sb.Append(" minutes ");
}
lblSure.Text = sb.ToString();
}protected void Timer1_Tick(object sender, EventArgs e)
{DateTime sonTarih = DateTime.Today.AddDays(1).AddHours(13);
TimeSpan aradakiFark = (DateTime)Session["siparis"] - DateTime.Now;StringBuilder sb = new StringBuilder();
sb.Append("Remaining time is ");if (aradakiFark.Days > 0)
{
sb.Append(aradakiFark.Days.ToString());sb.Append(" days ");
}if (aradakiFark.Hours > 0)
{
sb.Append(aradakiFark.Hours.ToString());sb.Append(" hours");
}if (aradakiFark.Minutes > 0)
{
sb.Append(aradakiFark.Minutes.ToString());sb.Append(" minutes ");
}
lblSure.Text = sb.ToString();
}
}
 
I've tried a select query that uses getdate and returns me to the manually arranged holidays in the Holidays table, but I've failed.  I wish to do that, this query arranges holidays and adds +1 day on that holiday, if this one day added day is a holiday again, than adds one day again. How can I write this code? Thanks.

View 1 Replies View Related

Holidays In SQL Server

Jul 20, 2005

Hi!I have a large table in SQL Server 2000 with a datetime-column 'dt'. I wantto select all rows from that table, excluding days which fall on holidays orweekends. What is the best way to accomplish this? I considered creating anew table called "holidays" and then selecting all rows (sort of "where notin (select * from holidays)") , but I was looking for a better solutionsince that implies that I have to populate the "holidays" table.Suggestions are welcome!Sincerely,Nils Magnus Englund

View 2 Replies View Related

Arranging Holidays

Feb 1, 2008

Hello

In my ASP 2.0 website, I wish to arrange holidays and specific days from database with using Sql Server. What SQL query I must add to this following code? If you can post the required codes, I'd be most thankful. Regards.


private DateTime _siparis;

public DateTime Siparis

{

get { return _siparis; }

set { _siparis = value; }

}

protected void Button1_Click(object sender, EventArgs e)

{

Timer1.Enabled = true;

DateTime sonTarih = DateTime.Today.AddDays(1).AddHours(13);

Session["siparis"] = sonTarih;

TimeSpan aradakiFark = sonTarih - DateTime.Now;

StringBuilder sb = new StringBuilder();

sb.Append("Remaining time is ");

if (aradakiFark.Days > 0)

{

sb.Append(aradakiFark.Days.ToString());

sb.Append(" days ");

}

if (aradakiFark.Hours > 0)

{

sb.Append(aradakiFark.Hours.ToString());

sb.Append(" hours ");

}

if (aradakiFark.Minutes > 0)

{

sb.Append(aradakiFark.Minutes.ToString());

sb.Append(" minutes ");

}

lblSure.Text = sb.ToString();

}

protected void Timer1_Tick(object sender, EventArgs e)

{

DateTime sonTarih = DateTime.Today.AddDays(1).AddHours(13);

TimeSpan aradakiFark = (DateTime)Session["siparis"] - DateTime.Now;

StringBuilder sb = new StringBuilder();

sb.Append("Remaining time is ");

if (aradakiFark.Days > 0)

{

sb.Append(aradakiFark.Days.ToString());

sb.Append(" days ");

}

if (aradakiFark.Hours > 0)

{

sb.Append(aradakiFark.Hours.ToString());

sb.Append(" hours");

}

if (aradakiFark.Minutes > 0)

{

sb.Append(aradakiFark.Minutes.ToString());

sb.Append(" minutes ");

}

lblSure.Text = sb.ToString();

}

}

View 3 Replies View Related

Query To Count Holidays

Aug 9, 2004

Hi,
I'm working on a helpdesk project and I require the calculation of the holidays.
I need to get the time difference of the assigned date and the solved date of the helpdesk tickets considering the week-end holidays and statutory holidays. Is there any possible way to do this. I need something similar to the NetworkDays function in excel.
Thanks.
Madhavi.

View 2 Replies View Related

SQL Help - How To Figure Out Consecutive Workdays With Holidays

Jul 18, 2006

Hi everyone,
I'm hoping someone can help me with some sql statements.  I have a temp table that contains 30 dates that a student has missed in the last year.  I also have a holiday table of when training was not available.  I want to find out if there are 6 consecutive days missed excluding weekends and holidays (from the holiday table).  I know this is some nasty looping statement but I can't get my brain around it.
I would like do this in a stored proc but I could use C# if necessary.
Thanks in advance,  Jessica
 

View 7 Replies View Related

Best Way To Handle Excluding Holidays From Report

May 18, 2015

I inherited a report that counts patient visits per month. Holidays are hard coded into the query. What is the best way to handle holidays without hardcoding?

View 4 Replies View Related

'NETWORKDAYS(start_date,end_date,holidays)' In SQL ?

Jul 23, 2005

Is there an equivalent function in SQL to the'NETWORKDAYS(start_date,end_date,holidays)' function in Excel ?NetworkDays in Excel returns the number of whole working days betweenstart_date and end_date. Working days exclude weekends and any datesidentified in holidays. This part isn't vital but would be nice tohave. The weekend stuff is more important.I realise this will most likely need to be some method I write myself(possibly based on the name of the day - Mon-Fri), but any pointerswould be appreciated if anyone has done this before.ThanksRyan

View 2 Replies View Related

Calc DateDiff In Workdays Only, No Holidays Either

Mar 26, 2008

I use the NETWORKDAYS(start_date, end_date,holidays) function in Excel and Access regularly. I'm trying to find a similar function in TSQL to use on Server2005. NetWorkDays allows me to calculate the difference between my ticket open and close dates while excluding weekends and holidays, (we are nice to our staff and let them off).

I'm trying to find an easy way to do this in TSQL without writing a large amount of code.

I will greatly appreciate any help.

View 1 Replies View Related

Date Difference Measurement And Weekends / Holidays

Jun 4, 2008

The below code works fine to measure the difference in days between two dates.
However, there is an additional business requirement to subtract week-ends, and holidays, from the equation. 
Any ideas on how to accomplish this task, and leverage the below, existing code?  Thanks in advance! 
(SELECT ABS((TO_DATE(TO_CHAR(" & ToFieldDate & "),'yyyymmdd') - TO_DATE(TO_CHAR(" & FromFieldDate & "),'yyyymmdd'))) FROM DUAL) AS Measurement "

View 2 Replies View Related

Create Date Table With UK & Easter Bank Holidays

May 12, 2005

Also includes ISOWeek & Weekends

Dont know whether this is of any use to anyone or it has been done before but there are a lot of posts on here regarding date calculation issues & usually the most straight forward answer is to compare against a table of dates.

So while looking at Bretts blog and another post on here, i thought i'd post this on here
http://weblogs.sqlteam.com/brettk/archive/2005/05/12/5139.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49698

Special thanks to rockmoose & BOL (as always)

Edit: It also moves bank holidays to the following Monday (and Tuesday - xmas) if the bank holiday(s) falls on the weekend

SET DATEFIRST 1
SET NOCOUNT ON
GO

--Create ISO week Function (thanks BOL)
CREATE FUNCTION ISOweek (@DATE datetime)
RETURNS int
AS
BEGIN
DECLARE @ISOweek int
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1
RETURN(@ISOweek)
END
GO
--END ISOweek

--CREATE Easter algorithm function
--Thanks to Rockmoose (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45689)
CREATE FUNCTION fnDLA_GetEasterdate(@year INT)
RETURNS CHAR (8)
AS
BEGIN
-- Easter date algorithm of Delambre
DECLARE @A INT,@B INT,@C INT,@D INT,@E INT,@F INT,@G INT,
@H INT,@I INT,@K INT,@L INT,@M INT,@O INT,@R INT

SET @A = @YEAR%19
SET @B = @YEAR / 100
SET @C = @YEAR%100
SET @D = @B / 4
SET @E = @B%4
SET @F = (@B + 8) / 25
SET @G = (@B - @F + 1) / 3
SET @H = ( 19 * @A + @B - @D - @G + 15)%30
SET @I = @C / 4
SET @K = @C%4
SET @L = (32 + 2 * @E + 2 * @I - @H - @K)%7
SET @M = (@A + 11 * @H + 22 * @L) / 451
SET @O = 22 + @H + @L - 7 * @M

IF @O > 31
BEGIN
SET @R = @O - 31 + 400 + @YEAR * 10000
END
ELSE
BEGIN
SET @R = @O + 300 + @YEAR * 10000
END

RETURN @R
END
GO
--END fnDLA_GetEasterdate

--Create the table
CREATE TABLE MyDateTable
(
FullDate datetime NOT NULL CONSTRAINT PK_FullDate PRIMARY KEY CLUSTERED,
Period int,
ISOWeek int,
WorkingDay varchar(1) CONSTRAINT DF_MyDateTable_WorkDay DEFAULT 'Y'
)
GO
--End table create

--Populate table with required dates
DECLARE @DateFrom datetime, @DateTo datetime, @Period int
SET @DateFrom = CONVERT(datetime,'20000101') --yyyymmdd (1st Jan 2000) amend as required
SET @DateTo = CONVERT(datetime,'20991231') --yyyymmdd (31st Dec 2099) amend as required
WHILE @DateFrom <= @DateTo
BEGIN
SET @Period = CONVERT(int,LEFT(CONVERT(varchar(10),@DateFrom,112),6))
INSERT MyDateTable(FullDate, Period, ISOWeek)
SELECT @DateFrom, @Period, dbo.ISOweek(@DateFrom)
SET @DateFrom = DATEADD(dd,+1,@DateFrom)
END
GO
--End population


/* Start of WorkingDays UPDATE */
UPDATE MyDateTable
SET WorkingDay = 'B' --B = Bank Holiday
--------------------------------EASTER---------------------------------------------
WHERE FullDate = DATEADD(dd,-2,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate)))) --Good Friday
OR FullDate = DATEADD(dd,+1,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate)))) --Easter Monday
GO

UPDATE MyDateTable
SET WorkingDay = 'B'
--------------------------------NEW YEAR-------------------------------------------
WHERE FullDate IN (SELECT MIN(FullDate) FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 1 AND DATEPART(dw,FullDate) NOT IN (6,7)
GROUP BY DATEPART(yy,FullDate))
---------------------MAY BANK HOLIDAYS(Always Monday)------------------------------
OR FullDate IN (SELECT MIN(FullDate) FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1
GROUP BY DATEPART(yy,FullDate))
OR FullDate IN (SELECT MAX(FullDate) FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1
GROUP BY DATEPART(yy,FullDate))
--------------------AUGUST BANK HOLIDAY(Always Monday)------------------------------
OR FullDate IN (SELECT MAX(FullDate) FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 8 AND DATEPART(dw,FullDate) = 1
GROUP BY DATEPART(yy,FullDate))
--------------------XMAS(Move to next working day if on Sat/Sun)--------------------
OR FullDate IN (SELECT CASE WHEN DATEPART(dw,FullDate) IN (6,7) THEN
DATEADD(dd,+2,FullDate) ELSE FullDate END
FROM MyDateTable
WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (25,26))
GO

---------------------------------------WEEKENDS--------------------------------------
UPDATE MyDateTable
SET WorkingDay = 'N'
WHERE DATEPART(dw,FullDate) IN (6,7)
GO
/* End of WorkingDays UPDATE */

--SELECT * FROM MyDateTable ORDER BY 1
DROP FUNCTION fnDLA_GetEasterdate
DROP FUNCTION ISOweek
--DROP TABLE MyDateTable

SET NOCOUNT OFF


Andy

Beauty is in the eyes of the beerholder

View 4 Replies View Related

Help Convert MS Access Function To MS SQL User Defined Function

Aug 1, 2005

I have this function in access I need to be able to use in ms sql.  Having problems trying to get it to work.  The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String   Dim strReturn As String   If IsNull(strField) = True Then      strReturn = ""   Else      strReturn = strField      Do While Left(strReturn, 1) = "0"         strReturn = Mid(strReturn, 2)      Loop   End If  TrimZero = strReturnEnd Function

View 3 Replies View Related

In-Line Table-Valued Function: How To Get The Result Out From The Function?

Dec 9, 2007

Hi all,

I executed the following sql script successfuuly:

shcInLineTableFN.sql:

USE pubs

GO

CREATE FUNCTION dbo.AuthorsForState(@cState char(2))

RETURNS TABLE

AS

RETURN (SELECT * FROM Authors WHERE state = @cState)

GO

And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.

I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:

shcInlineTableFNresult.sql:

USE pubs

GO

SELECT * FROM shcInLineTableFN

GO


I got the following error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'shcInLineTableFN'.


Please help and advise me how to fix the syntax

"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.

Thanks in advance,
Scott Chang

View 8 Replies View Related

A Function Smilar To DECODE Function In Oracle

Oct 19, 2004

I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...


ali

View 1 Replies View Related

Using RAND Function In User Defined Function?

Mar 22, 2006

Got some errors on this one...

Is Rand function cannot be used in the User Defined function?
Thanks.

View 1 Replies View Related

Pass Output Of A Function To Another Function As Input

Jan 7, 2014

I need to be able to pass the output of a function to another function as input, where all functions involved are user-defined in-line table-valued functions. I already posted this on Stack Exchange, so here is a link to the relevant code: [URL] ...

I am fairly certain OUTER APPLY is the core answer here; there's *clearly* some way in which does *not* do what I need, or I would not get the null output you see in the link, but it seems clear that there should be a way to fool it into working.

View 5 Replies View Related

I Want A Function Like IfNull Function To Use In Expression Builder

Jul 24, 2007

Hi,

I wonder if there a function that i can use in the expression builder that return a value (e.g o) if the input value is null ( Like ifnull(colum1,0) )



i hope to have the answer because i need it so much.



Maylo

View 7 Replies View Related

Calculate % Qry

Feb 13, 2007

Hi,
 I am trying to execute a query which calculate % on group by. here is the query
SELECT     C.USR_HIGHEST_DEGREE,COUNT(DISTINCT C.MASTER_CUSTOMER_ID) AS [# MEM],(convert(numeric(5,2),COUNT(DISTINCT C.MASTER_CUSTOMER_ID))
/    (
 
SELECT convert(numeric(5,2),COUNT(CC.MASTER_CUSTOMER_ID))    FROM         MBR_PRODUCT AS MPP WITH (NOLOCK) INNER JOIN                      PRODUCT AS PP WITH (NOLOCK) ON MPP.PRODUCT_ID = PP.PRODUCT_ID INNER JOIN                      ORDER_MASTER AS AA WITH (NOLOCK) INNER JOIN                      CUSTOMER AS CC WITH (NOLOCK) ON AA.SHIP_MASTER_CUSTOMER_ID = CC.MASTER_CUSTOMER_ID INNER JOIN                      ORDER_DETAIL AS BB WITH (NOLOCK) ON AA.ORDER_NO = BB.ORDER_NO ON MPP.PRODUCT_ID = BB.PRODUCT_ID              WHERE (CC.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (BB.CYCLE_END_DATE >= GETDATE()) AND (AA.ORDER_STATUS_CODE = 'A') AND                       (BB.LINE_STATUS_CODE = 'A') AND (BB.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MPP.LEVEL1 IN ('NATIONAL'))
 
 
)*100 AS [%]
 
FROM         MBR_PRODUCT AS MP WITH (NOLOCK) INNER JOIN                      PRODUCT AS P WITH (NOLOCK) ON MP.PRODUCT_ID = P.PRODUCT_ID INNER JOIN                      ORDER_MASTER AS A WITH (NOLOCK) INNER JOIN                      CUSTOMER AS C WITH (NOLOCK) ON A.SHIP_MASTER_CUSTOMER_ID = C.MASTER_CUSTOMER_ID INNER JOIN                      ORDER_DETAIL AS B WITH (NOLOCK) ON A.ORDER_NO = B.ORDER_NO ON MP.PRODUCT_ID = B.PRODUCT_IDWHERE     (C.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (B.CYCLE_END_DATE >= GETDATE()) AND (A.ORDER_STATUS_CODE = 'A') AND                       (B.LINE_STATUS_CODE = 'A') AND (B.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MP.LEVEL1 IN ('NATIONAL')) GROUP BY C.USR_HIGHEST_DEGREE
My problem here it , query gives error. I would appreciate if someone can give me any idea how to go about this. I tried calculating % outside sql but still doesn't work.
Any help would be appreciate.

View 3 Replies View Related

Calculate Sum Of SQL Top 10

Mar 30, 2007

I have an SQL statement which returns the Top 10 states with the number of visitorsSELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors    FROM [Customer] WHERE Customer.year = '2006'    GROUP BY Customer.state    ORDER BY COUNT(Customer.state) DESCSo far this is what I havestate| visitorsMD341527.2PA215417.2NJ127510.2NY10258.2VA8136.5MA2922.3FL2562DE2431.9OH2411.9CA2381.9But what i need is to calculate the total for the Visitors column in my SQL so that is like soMD341527.2PA215417.2NJ127510.2NY10258.2VA8136.5MA2922.3FL2562DE2431.9OH2411.9CA2381.9Total Top 10995279.3Total for All Years12555100I tried using the sum but I was only getting one value and not the rest...So how can i accomplish this?Thank you  

View 8 Replies View Related

How To Calculate?

Feb 13, 2008

 
Hi all,
 
  I'm having the table in the following structure,
  UId  - int
  Pct - int
  Amt - money
  Example
  UId      Pct     Amt
   12      25       1500
   12      30       2500
   12      45       2000
 
  i want to calculate to calculate the sum of amount for the UId 12 in the following manner,
  sum of ( Amt * (Pct/100)) for UId = 12
   how to write query in sql server 2000?
   when i calculate if the value (Pct/100) < 0 that is 0.25 then it will take it as 0 so i'm getting all the values as 0.
Thanks!         
 

View 2 Replies View Related

Calculate Mtd And Ytd.

Mar 7, 2001

I have a table that has a providername and totals field.
I want to be able to calculate the MTD and YTD totals for each provider.

EX. Provider MTD YTD

Bob 100.00 300.00

But I am not sure how. I can get both totals seperately, but not n the same line....
Thanks
Ron

View 3 Replies View Related

How To Calculate Age

Nov 10, 2006

Can anyone help me with how to calculate the exact age.

Thanks in advance.

View 1 Replies View Related

Calculate Age?

Jan 25, 2004

I have a DOB field in my sql table, does anyone know how to display that field as an actual age in a view or a SP?

View 8 Replies View Related

Calculate %

Apr 15, 2008

I want to calculate rate as percentage using code below:

SELECT OrderBy, NumShiped/Total AS Rate FROM Order

I always got Rate = 0 even thought NumShiped = 80 and Total = 100

What is problem?

View 3 Replies View Related

Calculate Age From DOB

May 30, 2008

Hello,

I have a date of birth column in my table and I want to calculate age based on this date. Here is the code I am trying to use in my select statement.

DateDiff ("yyyy",(a.date_of_birth),CurrentDate)

Apparently CurrentDate is not recognized by SQL. What is the best way to do this?

View 9 Replies View Related

How To Calculate Age Using The ID NO

Jan 4, 2008

I have the table with list of 1500 employees with the following headings.
Surname, Ini,title,gender,Race,IdNo,FirstName

Then I need to calculate the age of each employee but I am stuk.
Please assist me.

View 8 Replies View Related

SQL Calculate Percentage

May 6, 2008

Hello every one, I am trying to get the total percentage of a column









Regions
Workbooks Required
Workbooks Sent 
Workbooks Returned
Workbooks Complete

A
20
21
20
18

B
33
33
33
30

C
19
29
18
16

D
9
18
8
8

Totals




Thanks in advance for any tips/solutions.

View 5 Replies View Related

Need To Calculate Grade Age

Mar 7, 2004

Need to calculate the Grade age based on the birthdate and Nov month and 30th Day of the current year.
I have a working datediff statement but I need to always but in the current year. I would like to have the statement get the current year. Then if the age is greater than xx and less than xx your age level is "xyz"

This works DateDiff("d" [Birthdate], 11/30/2004) /365.25 will return the age.

I want to replace the 2004 with a getdate yyyy so I do not need to maintain this statement.

Thanks in advance of a reply
Gary

View 2 Replies View Related

Calculate Date

Nov 12, 1999

Hi All!
I need a query to find all dates from today to one-year back.
If I start from today day I need find all dates until 11/12/98.
Thanks a lot.
Greg.

View 3 Replies View Related

Calculate Age Based On Dob

Aug 26, 2004

hi all;

I have seen posts on numerous websites to get the age from a dob column I am faced with the same problem but get this error:

Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals divide, type equals datetime.

when I run the following query:

SELECT ROUND((currentdate - DOB)/365.24,0) FROM FL1_A_Backup

All I need is a simple statement that calcualtes the age from the dob column(smalldatetime) then creates a new row which it puts the data into.
I have already created a CurrentDate column which has a (getdate()) value attached to it.

This is nothing fancy or complicated and I just need to figure out from the year I'm not really concerned about if the person has a bday tomorrow or so on.

I know I am probably doing something wrong with this query and am not shy to say that I have only been using ms sql for a couple of months (basic select statements).

View 14 Replies View Related

Calculate Difference?

Dec 30, 2003

I am having trouble creating a sp for the following situation:

The database contains a record of the mileage of trucks in the fleet. At the end of every month, a snapshot is collected of the odometer. The data looks like this:


Truck Period Reading
1 1/31/03 55102
2 1/31/03 22852
1 2/28/03 62148
2 2/28/03 32108
1 3/31/03 69806
2 3/31/03 52763

How can I calculate the actually miles traveled during the month in a query?

TIA,

Rob

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved