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

    Red face Unanswered: Point in Time query >:[

    Hey guys and gals,

    I'm having a real problem with this query at the moment...
    Basically I have to produce a query which will tell me the total number of people employed by the company at any given date and the total salary for all these people.

    We have a people table and a career table.
    People(unique_identifier, known_as_and_surname, start_date, termination_date ...)
    Career(unique_identifier, parent_identifier, career_date, basic_pay ...)
    Relationship people.unique_identifier = career.parent_identifier

    Employees can be identified like so
    Code:
    SELECT *
    FROM people
    WHERE start_date <= DateSelected
    AND (termination_date > DateSelected
    OR termination_date IS NULL)
    Passing the selected date to the query is no trouble at all I am just having problems with the point in time side of this.

    All and any help is greatly appreciated
    ~George

    P.S. SQL Server 2000
    George
    Home | Blog

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    ...I am just having problems with the point in time side of this.
    could you elaborate on this a bit please?

    because your query looks fine, all you need is an INNER JOIN as well as a GROUP BY and some aggregate expressions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    george ... is People to Career a 1:1 or 1:many relationship? What if a personn's salary changes during the date range in question?

    If 1:1, then a count of unique identifiers and sum of the salary from the Career table using a join on a filter from the People table using the date criteria would do the job.

    If however, you can have a salary change during the data range, and you have two rows in the Career table, you will need someone to define the business rules for that situation.

    -- This is all just a Figment of my Imagination --

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937
    could you elaborate on this a bit please?
    I need to find out if they were an employee at any given date...
    There is no career_date_from or to fields, just a single career_date - which is part of the problem!

    Tom, One person can have many career history lines. and the problem is - how do I make this a range?

    Here's some samlpe data that may help
    Code:
    unique_id	name			start_date		termination_date
    00001		George V		01/11/2006		NULL
    00002		Tom 53			01/06/2004		01/06/2007
    00003		Rudy 937		07/07/2007		NULL
    
    unique_id	parent_id		career_date		basic_pay
    1		00001			01/11/2006		150
    2		00001			01/12/2006		165
    3		00002			01/06/2004		155
    4		00003			07/07/2007		160
    5		00003			09/07/2007		170
    If I entered 02/11/2006 as my criteria I'd want to return the sum of the following lines
    Code:
    unique_id	parent_id		career_date		basic_pay
    1		00001			01/11/2006		150
    3		00002			01/06/2004		155
    Which gives us
    2 employees : &#163;305
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Do you just want the "last" (by career_date) record in Career where the career_date is less than or equal to the DateSelected?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think that's it!
    I believe that makes it look something like this:
    Code:
    DECLARE @SelectedDate datetime
    SET @SelectedDate = '2006-11-02'
    
    SELECT	   Count(*)
    	  ,Sum(c.basic_pay)
    FROM	  people e
    LEFT JOIN career c
    	ON  c.parent_identifier = e.unique_identifier
    	AND c.career_date =	(
    				SELECT	Max(career_date)
    				FROM	career
    				WHERE	parent_identifier = c.parent_identifier
    				AND	career_date <= @SelectedDate
    				)
    That's what I couldn't get my head around
    Last edited by gvee; 07-09-07 at 11:55. Reason: Prettying it up ;)
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    george ... reference uniqueid '00002' ... you can't fire me ... I QUIT

    -- This is all just a Figment of my Imagination --

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    george, your sample data was the key to understanding the data relationship (which was not at all apparent from post #1)

    just another example of why we ask posters to show sample data

    p.s. those unique_ids are awful!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    They are aweful, but you know what...
    It allowed the original developers to make an inbuilt query designers that fools can use - which helps me a little. The only other benefit is that you know the relationships between almsot everything simply by logic.

    But yes, it was like this when I got it

    Can one of you kindly check the following code over once? It's my "final" result
    Code:
    DECLARE @SelectedDate datetime
    SET @SelectedDate = '2005-11-01'
    
    SELECT	 Count(*)
    	,Sum(c.basic_pay)
    FROM	people e
    LEFT JOIN career c
    	ON  c.parent_identifier = e.unique_identifier
    	AND c.career_date =	(
    				SELECT	Max(career_date)
    				FROM	career
    				WHERE	parent_identifier = c.parent_identifier
    				AND		career_date <= @SelectedDate
    				)
    WHERE	(e.termination_date > @SelectedDate
    OR	e.termination_date IS NULL)
    AND	e.start_date <= @SelectedDate
    Oh and Tom, you were never fired... You just didn't turn up

    And Rudy; yes it occured to me that I never mentioned that it was 1:M...
    It's Monday, I'm frazzled already!
    I came in this morning and my monitor was covered in sticky notes because of missed calls etc. So lame.
    Not a good start to the week.

    Finally - thank you all
    Last edited by gvee; 07-09-07 at 11:55.
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Once again Poots' incisive logic cuts to the very core of the problem

    Ok - this:
    Code:
    SET @SelectedDate = '2005-11-01'
    is not guarenteed to work in all system set ups. Better is:
    Code:
    SET @SelectedDate = '20051101'
    Also - is there a unique constriant on the composite key parent_identifier, career_date (assuming a person cannot have two career records in a day)? If not there could be two records for a person on a given day -> errors in the count and sum.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SET @SelectedDate = '2005-11-01'
    This query will be translated into a 3rd party program that will only run on SS 2000 with it's own run time expression builder - so this was purely for testing purposes
    You can have two career records on one day... which is something I had not thought about. Would an order by clause sort this out (if say, I ordered it by date_entered)?
    George
    Home | Blog

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Damnit - the dupes are causing me a problem.
    There are around 10 people who are being counted twice!

    How can I eliminate these?
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Extend the same logic again. You wanted the max(career_date). You now want the max(date_entered) for the max(career_date)....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yeah... Having trouble with that.
    Code:
    AND c.career_date =
    	(
    	SELECT	Max(x.career_date)
    	FROM	career x
    	WHERE	x.parent_identifier = c.parent_identifier
    	AND	x.career_date <= @SelectedDate
    	AND	x.created_by_user = 
    				(
    				SELECT	Max(created_by_user)
    				FROM	career
    				WHERE	parent_identifier = c.parent_identifier
    				AND	career_date <= @SelectedDate
    				)
    Is not what I want... I'm sorry; lack of sleep + stress =
    ...can't even think of a word suitable to complete that sentence *sigh*
    Last edited by gvee; 07-09-07 at 11:54.
    George
    Home | Blog

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Untested but worrabout:
    Code:
    ....    AND c.career_date =    (
                    SELECT    TOP 1 career_date
           FROM    MySchema.career
                    WHERE    parent_identifier = c.parent_identifier
                    AND        career_date <= @SelectedDate
    ORDER BY career_date DESC, created_by_user DESC
           )
    Last edited by pootle flump; 07-09-07 at 11:58.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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