# Thread: Calculating Length of Service Categories based on Start Dates

1. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445

## Unanswered: Calculating Length of Service Categories based on Start Dates

Ok, so I've been struggling with the logic behind this one for a while, so I thought I'd ask you guys for some ideas

Basically, I have the following table structure
Employee(employee_number, continuous_start_date, ...)

The problem lies in working out a summary of service categories (0-6months, 7-12months, 13-24, 25+).
I can work out the length of service in months with the following code
Code:
```SELECT DateDiff(mm, continuous_start_date, GetDate()) AS 'Service in months'
FROM employee```
So the first stage is to summarise the length of service into groups as mentioned above.

Then the final stage is working out how many people are in each group!

Hope I have given enough information - and please do not post a full solution - just some hints on how to get the desired result

-GeorgeV

2. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Hi George

I would create a table with upper and lower month limits for each of the bands, and proobably a name for each band. Create an join to this table on your calculated "monthsInService" field using the between operator.

Robert would then be your mothers brother.

3. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Nice suggestion Poots, but I'm aiming to get this in a single query (so, CASE statements, or an array-type thing?)

I am struggling with getting the results into groups at the moment - I've kind hit a brick wall - wondering if it's quicker to climb, go under, walk round, etc Not sure what's the best way to my destination (or if it even is!)

4. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
My suggestion works fine as a sinlge statement. Why wouldn't it?

Reasons not to use a case statement:
http://weblogs.sqlteam.com/jeffs/arc...2/10/9002.aspx

5. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Hmm, you make a good point!
So, let's asy I make a table ServiceCategory(Unique_Identifier, Lower_Limit, Upper_Limit, Limit_Description (and possibly Sort_Order?))

Would I join to my table like this:
Code:
```FROM Employee e
INNER JOIN ServiceCategory s
ON DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit```
Apologies, I'm trying to get the logic right before creating a new table (they don't really like people creating new tables - hence my single query approach)

Thanks Poots

6. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Yes.

Personally I wouldn't bother with a surrogate key because I can't see it ever serving any purpose. But that's just me - a minor matter.

I presume you are comfy with the sums part?

7. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
BTW - I presume there is no end of service date in the table? If not you will need to account for that in your expression....

8. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
two stars for poots on this thread -- one for suggesting the join to a range table, and another for advising against the surrogate key

nice job

9. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Thank you Rudy. Makes up for the couple of cock ups I made in the last SQL threads we shared in common

10. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Surrogate key is automatically generated by our HR system, I know it's not needed But still, good catch!

As for the sums bit - I'll have to get back to you after I've played some more - thanks for the suggestions, going to ask (yes, sadly I have to ask when I'm playing with the HR system... :s) to create my new table now

11. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Ah - I see. You can develop this using a table variable until you are happy it works great then submit the create table script to them once you are 100% confident.

12. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Table variable?
And I have still never written a create table script - I have only ever used Enterprise Manager (HR system is on 2K)...

This might give me an oppurtunity to have a go - I'll be back with an attempt soon

13. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
This is a template I use. Find & replace the placeholders. See if it helps:
Code:
```
IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N't_name') BEGIN
DROP TABLE dbo.t_name
END

CREATE TABLE dbo.t_name
(
c_name INT NOT NULL CONSTRAINT df_t_name_c_name DEFAULT 0
, CONSTRAINT pk_t_name PRIMARY KEY CLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ix_t_name_c_name_u_nc UNIQUE NONCLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ck_t_name_c_name CHECK (c_name BETWEEN 1 AND 10)
, CONSTRAINT fk_t_name_other_t_name FOREIGN KEY (c_name) REFERENCES other_t_name (c_name) ON DELETE CASCADE
)
GO```

14. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
How's this for an attempt?
Code:
```CREATE TABLE ServiceCategory
(
Lower_Limit		numeric,
Upper_Limit		numeric,
Limit_Description	char(16),
Sort_Order		numeric
)```
Code:
```INSERT
INTO	ServiceCategory(Lower_Limit, Upper_Limit, Limit_Description, Sort_Order)
VALUES	(0, 6, '0 to 6 months', 1)```
Code:
```SELECT	*
FROM	ServiceCategory```

15. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Oh and my full solution:
Code:
```SELECT	 Count(s.Limit_Description) AS 'Total'
,s.Limit_Description
FROM	pwa_master.Employee e
INNER JOIN ServiceCategory s
ON	DateDiff(mm, e.continuous_start_date, GetDate()) BETWEEN s.Lower_Limit AND s.Upper_Limit
GROUP
BY	s.Limit_Description, s.Sort_Order
ORDER
BY	s.Sort_Order```
Thank you
Last edited by gvee; 05-01-07 at 08:22. Reason: Colouring in!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•