Sql help for min, max function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Sql help for min, max function

Author

Topic

Corden
Starting Member

9 Posts

Posted - 2005-07-05 : 14:33:15

Hope someone can help...

I have a table with Empid, jobcode, jobStartDate,JobEndDate, Job Reason. An example of the data is as follows:

123 GKJ79 01/01/2004 12/31/2004 Promo
123 RFTYD 01/01/2005 04/01/2005 JobRename
123 GKJ79 04/02/2005 06/20/2005 Promo
123 GKJ79 06/21/05 Realignment

I need to run a report of how long the person has been in the current JOB. But I need to ignore all 1/1/05 records in the process with a reason code of "JobRename". Therefore, in this example ,employee 123 has been in Job code GKJ79 for 551 days.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-05 : 14:47:31

I'm not sure how you calculated 551 days or which date column 06/21/05 belongs to and what to do if the other column is null. But maybe this will give you some ideas:


set nocount on

--DDL
declare @tb table (Empid int, jobcode varchar(5), jobStartDate datetime, JobEndDate datetime, JobReason varchar(20))

--DML
insert @tb
select 123, 'GKJ79', '01/01/2004', '12/31/2004', 'Promo' union all
select 123, 'RFTYD', '01/01/2005', '04/01/2005', 'JobRename' union all
select 123, 'GKJ79', '04/02/2005', '06/20/2005', 'Promo' union all
select 123, 'GKJ79', '06/21/2005', null, 'Realignment'

--select statement
select	empid
	,jobcode
	,sum(datediff(day, jobStartDate, isnull(JobEndDate,getdate()))) totalhours
from	@tb
--where	jobReason <> 'JobRename'
--and	jobcode = 'GKJ79'
--and	jobstartDate <> '1/1/2005'
group by empid
	,jobcode

Be One with the Optimizer
TG

Corden
Starting Member

9 Posts

Posted - 2005-07-05 : 14:55:09

Thank you for help.

But, I need to know how long in a particular job. I can't hardcode the job code into the statement since there are thousands. I know I have to do a min max and group it by job code but I am new to SQL so I need help in writing it.

Thanks in advance

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-05 : 15:08:06

That's why I commented out the criteria and included a group by clause in the sample statement above. Do you need a SUM of all hours grouped by empid/jobcode or do you need the stated hours for the "current" record by empid/jobcode? Please run the code I provided above and if the output is not what you want, post the desired results.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources