Page 1 of 4 123 ... LastLast
Results 1 to 15 of 51
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Smile 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

    Thanks later, and in advance
    -GeorgeV
    George
    Home | Blog

  2. #2
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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!)
    George
    Home | Blog

  4. #4
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  6. #6
    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?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    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....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  11. #11
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  13. #13
    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

    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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!
    George
    Home | Blog

Posting Permissions

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