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

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 ;)

Thanks later, and in advance :p
-GeorgeVHi 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.|||Nice suggestion Poots, but I'm aiming to get this in a single query (so, CASE statements, or an array-type thing?) :p

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 :p Not sure what's the best way to my destination (or if it even is!)|||My suggestion works fine as a sinlge statement. Why wouldn't it?

Reasons not to use a case statement:
http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx|||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:

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 :)|||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?|||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...|||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

:)|||Thank you Rudy. Makes up for the couple of cock ups I made in the last SQL threads we shared in common :)|||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 :)|||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.|||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 ;)|||This is a template I use. Find & replace the placeholders. See if it helps:

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|||How's this for an attempt? :)

CREATE TABLE ServiceCategory
(
Lower_Limit numeric,
Upper_Limit numeric,
Limit_Description char(16),
Sort_Order numeric
)

INSERT
INTO ServiceCategory(Lower_Limit, Upper_Limit, Limit_Description, Sort_Order)
VALUES (0, 6, '0 to 6 months', 1)

SELECT *
FROM ServiceCategory|||Oh and my full solution:

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 :D|||That's fine. Not so sure about the description now (none key column dependancies - maybe I am just a fuss pot). I think TINYINT is probably sufficient - numeric is a bit overkill. Inner join removes anyone not accounted for in the range table. There is no end date right? Add some NOT NULLS into your table definition (all columns).

All the above is picky - good job.|||Hmm, well it did suddenly occur to me that there are a whole 3 null values in the system (which slipped through the net before the field was made mandatory...) but if I flag them up to HR it will be dealt with ;)

As for my top range, not sure what to put, I currently have a rediculous value of 1200 (100 years service... yeah, right ;))
So this covers everyone (other than the three silly nulls from above)... It's not pretty, but it works... Suggestions welcomed with open arms! :)

Should I drop the desc, just not sure what to do instead (want to be able to display a description of each range)... Hmm...|||I think a "forever" date is ok in this instance.

I might just be being too picky re your desciption field. The problem of course is second normal form and if you end up changing your bands. If you drop the desc you can create a view something like:

SELECT
Lower_Limit, Upper_Limit,
CAST(Lower_Limit AS VARCHAR()) + ' to ' + CAST(Upper_Limit AS VARCHAR()) + ' months' AS Limit_Description,Sort_Order
FROM dbo.ServiceCategory


HTH|||I tried that, but was getting conversion errors... Was thinking of Cast/convert statements as a fix :D (I was actually really close!)

I like being picky, so I want to also sort out my "forever" limit, as you put it, but I'm not exactly sure how...|||Nah - an absurdly high value in there is fine. That is what I would do. You could leave it null and then use COALESCE() in your join but I prefer having a value in there myself since there is no time when you will want it to count as a NULL.

I'll ask one last time (maybe you have spotted this and didn't bother answering) - are there leaving dates in the staff table? If not then I presume it is current staff only?

None of you lower limits are the same as your upper limits are they? (for example row two does not start at 6 but at 7).|||SELECT Limit_Description
,CAST(Lower_Limit AS VARCHAR) +
(CASE WHEN Sort_Order = (SELECT Max(Sort_Order) FROM ServiceCategory)
THEN '+'
ELSE ( + ' to ' + CAST(Upper_Limit AS VARCHAR))
END) + ' months'
AS Limit_Description
FROM ServiceCategory

:cool: hehe|||Employee table is a view on the people table, based on termination date being empty/null/blank/etc. (EDIT: and the continuous_start_date being greater than or equal to today)

As for my limits:
0 6
7 12
13 24
25 60
61 1200|||Looks to me that you are all sorted then :)|||Yippee :D
Now I have to make this into a sproc written in FoxPro and I'm done ;)
Roll on our new HR system!

Thanks for all your help mate :beer:|||Ooh, one little thing while I'm here:

ALTER TABLE ServiceCategory DROP COLUMN Limit_Description

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Limit_Description'.

Any ideas? I'm completely stumped here...|||Annoyingly, the keyword COLUMN in this case causes an error. ALTER TABLE DROP... always drops a column - no need for the key word.|||I don't think I follow - I've tried:
ALTER TABLE ServiceCategory DROP Limit_Description
Which causes errors, "not a constraint"...

ALTER TABLE DROP ServiceCategory.Limit_Description
More errors...
*eek*|||Beg your pardon - I'm thinking of add :rolleyes:

Something wot I wrote for our more junior developers:

USE tempdb
go

BEGIN TRAN
--SET UP----------------
--Make sure the db is clean
IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'delete_me') BEGIN
DROP TABLE dbo.delete_me
END

IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'delete_me_too') BEGIN
DROP TABLE dbo.delete_me_too
END

--This table is pretty irrelevent - just used for a foreign key constraint example.
--If it did not exist the code would error.
CREATE TABLE dbo.delete_me_too
(
my_other_col VARCHAR(10)
, CONSTRAINT pk_delete_me_too PRIMARY KEY CLUSTERED (my_other_col) WITH (FILLFACTOR = 80)
)
GO

--This is the table we will alter.
CREATE TABLE dbo.delete_me
(
my_col INT
)
GO
--SET UP----------------

--COLUMNS----------------
--Add a column. NOTE - no COLUMN key word.
ALTER TABLE dbo.delete_me
ADD my_new_col VARCHAR(10)

--remove a colimn
ALTER TABLE dbo.delete_me
DROP COLUMN my_new_col

--Alter a column
ALTER TABLE dbo.delete_me
ALTER COLUMN my_col VARCHAR(10) NOT NULL
Go
--COLUMNS----------------

--CONSTRAINTS----------------
--Add a primary key
ALTER TABLE dbo.delete_me
ADD CONSTRAINT pk_delete_me PRIMARY KEY CLUSTERED (my_col) WITH (FILLFACTOR = 80)

--Add a unique constraint (note - a unique constraint is enforced as an index so you can
--either add a unique constraint or create a unique index.
ALTER TABLE dbo.delete_me
ADD CONSTRAINT ix_delete_me_my_col_u_nc UNIQUE NONCLUSTERED (my_col) WITH (FILLFACTOR = 80)

--Add a check constraint
ALTER TABLE dbo.delete_me
ADD CONSTRAINT ck_delete_me_my_col CHECK (my_col NOT LIKE '%[0-9]%')

--Add a foreign key constraint
ALTER TABLE dbo.delete_me
ADD CONSTRAINT fk_delete_me_delete_me_too FOREIGN KEY (my_col) REFERENCES delete_me_too (my_other_col) ON DELETE CASCADE

--Add a default constraint
ALTER TABLE dbo.delete_me
ADD CONSTRAINT df_delete_me_my_col DEFAULT 'default val' FOR my_col

--NOTE - there is no ALTER constraint syntax. You must drop the constraint and then
--add the constraint with the new settings. You can, however, user ALTER INDEX syntax
--for changing indexes. Check BoL for ALTER INDEX for more information.
--Drop the constraints. NOTE - the syntax is the same for all of them.
--You don't *have* to use the CONSTRAINT key word when dropping constraints.
ALTER TABLE dbo.delete_me
DROP CONSTRAINT df_delete_me_my_col

ALTER TABLE dbo.delete_me
DROP fk_delete_me_delete_me_too

ALTER TABLE dbo.delete_me
DROP ck_delete_me_my_col

ALTER TABLE dbo.delete_me
DROP CONSTRAINT ix_delete_me_my_col_u_nc

ALTER TABLE dbo.delete_me
DROP CONSTRAINT pk_delete_me
--CONSTRAINTS----------------

--Clean up
IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'delete_me') BEGIN
DROP TABLE dbo.delete_me
END

IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'delete_me_too') BEGIN
DROP TABLE dbo.delete_me_too
END

ROLLBACK TRAN|||--remove a colimn
ALTER TABLE dbo.delete_me
DROP COLUMN my_new_col

Why won't this work :(|||You mean the code I posted does not work? Execute the whole thing I posted - it should work fine.|||What I meant was: Your syntax matched my syntax... and mine didn't work

*confused*|||Almost but not quite.

Qualify your table name with the schema.
Check there is nothing after the statement.
Double check you have spelt the table and column names correctly.
Check you are executing in the right database.|||You may also want to check if the column to be dropped is not in any FK relationships (the heartbreak would be too much for the database, you know), check constraint, computed columns, indexes, glee clubs, etc..|||Just came back here to reference some code

IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N't_name') BEGIN
DROP TABLE dbo.t_name
END

But when I run

SELECT NULL FROM sys.tables WHERE name = N'tServiceCategory'

I get:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.tables'.

Any ideas?|||i will never understand why people write a script that says "if this thing exists, drop this thing"

duh, just drop it

if it existed, then it won't exist after you drop it

and if it didn't exist, it still won't

easy-peasy

:cool:|||Ah, but when I run the SQl in FoxPro I get an error, which means I must have a logical test to see if it's there or not. But then again, the temp table idea has pretty much gone out the window thanks to Sir Poots (thanks!) it was just a matter of intrigue ;)|||i will never understand why people write a script that says "if this thing exists, drop this thing"

duh, just drop it

if it existed, then it won't exist after you drop it

and if it didn't exist, it still won't

easy-peasy

:cool:... but it causes an error.

sys.tables is a SQL 2005 thing. You need... erm...

...
dbo.sysobjects ...
where type = N'U'
...

if memory serves|||*tips cap* thank you kindly sir.
your memory is correct :)|||Reasons not to use a case statement:
http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx

Thx Mr Flump :)

This was so helpful and informative, it's kept me out of the forum for 2 days.
I've redisgned 4 tables so far.|||... but it causes an error. which you ignore

allow me to illustrate:

you: please drop table such-and-such
database: yes sir
you: now please create table such-and-such
database: yes sir

you: please drop table such-and-such
database: but table such-and-such doesn't exist!!! oh, woe!!!!
you: now please create table such-and-such
database: yes sir|||that tactic is fine for one-offs, but if you are automating script execution in a production environment, you don't want to have to dig through a bunch of false alarms to see if something failed. I don't anyway.|||but it isn't a "failure"

what, you can't write a procedure with try/catch logic in sql server? and choose to ignore?you should be able to write a stored proc to do pretty much whatever you want, including ignoring a message when you know it can be ignored

and a proc is the only thing that i would allow in production

if i were a dba, that is ;)|||but it isn't a "failure"

what, you can't write a procedure with try/catch logic in sql server? and choose to ignore?you should be able to write a stored proc to do pretty much whatever you want, including ignoring a message when you know it can be ignored

and a proc is the only thing that i would allow in production

if i were a dba, that is ;)

try/catch doesn't cut it, because then you'd have to filter out the errors you choose to ignore (the "object doesn't exist" error in this case) and let the others bubble up. much easier to check if it exists before dropping.

frankly I'm surprised you suggested this. using try/catch as a flow control mechanism is bad form. I don't know about sql, but on other languages it's a very expensive way to do things. catching an exception isn't cheap in C++ and C#. Exceptions should be exceptional, not the rule. That's why they are called exceptions.|||which you ignore

allow me to illustrate:

you: please drop table such-and-such
database: yes sir
you: now please create table such-and-such
database: yes sir

you: please drop table such-and-such
database: but table such-and-such doesn't exist!!! oh, woe!!!!
you: now please create table such-and-such
database: yes sirBut the red text is so unaesthetically pleasing :)

Seriously though - the suggestion of TRY ... CATCH is really just another flavour of the same solution. You need one TRY ... CATCH per object because you can't have the code skip the dropping of other objects after the first execption. The only way I could see your suggestion working without it being at least as verbose as an existance check is if SQL had the equivelent of ON ERROR RESUME NEXT - which is of course the spawn of the devil(http://weblogs.sqlteam.com/jeffs/archive/2006/06/06/10093.aspx) ;)

I don't share Jezamine's philosophical objections to using TRY ... CATCH. I'm not too fussed about efficiency with schema creation scripts and I don't have a big problem with handling errors rather than avoiding them if it makes sense to do so. EDIT: in fact you might remember a question I posted regarding a similar issue a few years back when I asked for advice on the check for existence of a record before inserting Vs insert anyway and ignore the primary key violation. I just don't see the advantage of your method over checking if it exists.

I also might add that I have a load of snippets in SQLPrompt that I can call up with a couple of key strokes so I don't even have to spend extra time typing :D|||Thx Mr Flump :)

This was so helpful and informative, it's kept me out of the forum for 2 days.
I've redisgned 4 tables so far.Thanks I... copied and pasted the link myself you know. Here is another one from my extensive library:
http://www.sqljunkies.com/WebLog/amachanic/archive/2006/02/16/18123.aspx|||I'm afraid I disagree with your methods Rudy...
Ignoring errors sounds like a real bad habit to me!
In this instance I couldn't have done that anyway - what with the 3rd party interface to come - it sees an error and it cries.

Even saying that, when I was using query analyzer it hated the fact that my tables didn't exist when it tried to drop them - the last thing you need is for a user to get scared because an error occurs - just means more support calls for me to deal with "No dear, that error doesn't matter" is not what they want to hear, and leads them to think other problems are less severe also.

Rhubar, rhubarb, rhubarb...

Where was I? Oh yeah, the extra 2 lines of code are nothing compared to the amount of hassle that is produced from leaving them out.|||Hi, this post is very informative; however I would like some specific information. If someone can help me then please send me a private message. Best Regards,|||Hi, this post is very informative; however I would like some specific information. If someone can help me then please send me a private message. Best Regards,

Hi shahzadmasih,

Why not ask your question here, so we can all continue to benefit?

Mark|||As above...
This is a public forum - why not share your questions with everyone? :)|||Speaking for this shop, we prefer having the "if exists then drop" syntax for two reasons:
1) We have recently begun employing a group of offshore folks to do the actual running of the scripts. Having fewer errors for them to deal with makes their life (and consequently ours) easier.
2) We are debating going to an automatic script deployment system. If we do that, all errors would have to be caught, and probably inspected, evaluated, and a some pages of documentation sent to the end user detailing why the deployment got mucked up halfway through the deployment.

Still, if you are sitting at the console and doing this yourself, with no other people looking over your shoulder, then the "if exists then drop" syntax is perfectly valid. It is just when you have to deal with a range of skills that it becomes a problem.|||2) We are debating going to an automatic script deployment system. If we do that, all errors would have to be caught, and probably inspected, evaluated, and a some pages of documentation sent to the end user detailing why the deployment got mucked up halfway through the deployment.

that's exactly the kind of thing I meant in my earlier post by "automating script execution in a production environment".

Throwing errors unnecessarily, or using TRY/CATCH to eat them, would be a disaster for such a system.