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.
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!)
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
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
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
SELECT Count(s.Limit_Description) AS 'Total'
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
BY s.Limit_Description, s.Sort_Order
Last edited by gvee; 05-01-07 at 08:22.
Reason: Colouring in!