Results 1 to 15 of 15
  1. #1
    Join Date
    May 2013
    Posts
    8

    Unanswered: SUM column for all entries AFTER date

    I can't seem to get this working how I need. The closest I have is as follows:
    Code:
    SELECT tblEmployeeData.TechID, Sum(tblOccurrence.OccurrenceAmt) AS OccurenceAmt_SUM, tblOccurrence.OccurrenceDate, tblOccurrence.CoachingDate
    FROM tblEmployeeData LEFT JOIN tblOccurrence ON tblEmployeeData.TechID = tblOccurrence.TechID
    GROUP BY tblEmployeeData.TechID, tblOccurrence.OccurrenceDate, tblOccurrence.CoachingDate
    HAVING ([tblOccurrence].[CoachingDate] >= DateAdd('m',-6,Date()))
    The issue is that the SUM column isn't collecting all the rows needed for an accurate sum. The problem is that there might be entries after [tblOccurrence].[CoachingDate] and it seems my query isn't accounting for those. How do I make this query work the way it's supposed to? I'm destroying my brain trying to reach a solution.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Your problem is that you're filtering records using the right hand side of a left join. This destroys the benefit of using a left join, and effectively converts it to an inner join. You can get around this either by using a subquery to filter out the required records first, or by replacing the HAVING clause with this:
    Code:
    HAVING [tblOccurrence].[CoachingDate] >= DateAdd('m', -6, Date()) OR [tblOccurrence].[CoachingDate] IS NULL
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    May 2013
    Posts
    8
    I replaced my HAVING clause so now the query is:

    Code:
    SELECT tblEmployeeData.TechID, Sum(tblOccurrence.OccurrenceAmt) AS OccurenceAmt_SUM, tblOccurrence.OccurrenceDate, tblOccurrence.CoachingDate
    FROM tblEmployeeData LEFT JOIN tblOccurrence ON tblEmployeeData.TechID = tblOccurrence.TechID
    GROUP BY tblEmployeeData.TechID, tblOccurrence.OccurrenceDate, tblOccurrence.CoachingDate
    HAVING [tblOccurrence].[CoachingDate] >= DateAdd('m', -6, Date()) OR [tblOccurrence].[CoachingDate] IS NULL
    Now the results aren't 1 "TechID" per line with a sum, it's broken out the TechID's to multiple entries and it's not running the SUM.

    How would I go about doing a subquery (sorry, still pretty green at this stuff) Thanks!

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Code:
    SELECT
    	e.TechID
    ,	Sum(o.OccurrenceAmt) AS OccurenceAmt_SUM
    ,	o.OccurrenceDate
    ,	o.CoachingDate
    FROM
    	tblEmployeeData AS e
    LEFT JOIN
    	(SELECT
    		occ.TechID
    	,	occ.OccurrenceAmt
    	,	occ.OccurrenceDate
    	,	occ.CoachingDate
    	FROM
    		tblOccurrence AS occ
    	WHERE
    		occ.CoachingDate >= DateAdd('m', -6, Date())
    	) AS o
    ON
    	e.TechID = o.TechID
    GROUP BY
    	e.TechID
    ,	o.OccurrenceDate
    ,	o.CoachingDate
    If this doesn't work, please post some sample data along with what you want the results to look like.

    Just out of interest, why are you even using tblEmployeeData? You are not using any columns from it that you couldn't get from tblOccurrence.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    May 2013
    Posts
    8
    So long story for explanation. Basically what I'm attempting to do is create an Update query which takes the occurrences from tblOccurrence table (sample data) and populate a field on tblEmployeeData table. This routine will run every time the database is opened so the summary information will be readily available.

    The tricky part is that the summary actually changes depending on if the "Tech" has a coaching date within the last 6 months, I need that amount PLUS any other occurrences that have happened since that date. If the "Tech" has NO coaching date, then I need to just sum the amount in the previous 6 months.

    Here's the sample Data from tblOccurrence:

    Here is what I need the results to be:

    The OccurrenceSum results will be used to update the tblEmployeeData table for future reference.

    Hope that makes it a touch clearer.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    A few things come to mind, notably that the field CoachingDate is irrelevant. It duplicates information that can be extrapolated from OccurrenceDate and Type. For that matter, what's the difference between "ID" and "OccurenceID"?

    What would you like to see in the event that a technician has had more than one coaching date in the last six months?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    May 2013
    Posts
    8
    If a TechID has more than 1 coaching date in the last 6 months, I need the most recent coaching. The OccurrenceID is a non-used field that I included on accident. The ID is the primary key with autonumber to avoid duplicate entries.

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Hang on. Your explanation does not tie up with your example.
    The tricky part is that the summary actually changes depending on if the "Tech" has a coaching date within the last 6 months, I need that amount PLUS any other occurrences that have happened since that date. If the "Tech" has NO coaching date, then I need to just sum the amount in the previous 6 months.
    According to this, the required result for tech 9997 should be 16, that being all occurrences in the last six months including their coaching. Given the actual figures in the example results, I suspect that what you require is this:
    If a tech has had coaching in the last six months, give the total amount of coaching for that period, along with the most recent coaching date. If a tech has not had coaching in the last six months, give the total amount for all occurrences in that period.
    If this is not the case, please restate the business requirement more clearly.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  9. #9
    Join Date
    May 2013
    Posts
    8
    I apologize for not making it clear.

    When a Tech has any Coaching Dates within the last 6 months, I need to take only the most recent coaching date amount and any other occurrences that have happened after their coaching date.

    If a tech has no coaching dates within the last 6 months, I need to add all occurrences for the previous 6 months.

    So totals should be:
    2094 = 5 (no coaching amt, only total his previous 6 months)
    9997 = 8 (only coaching amount since coaching date is within last 6 months)
    9998 = 5.5 (coaching amount + 1.0 since his coaching date).
    9999 = 9

    Hopefully that helps the understanding.

  10. #10
    Join Date
    May 2013
    Posts
    8
    I'm going to attempt to do coding on a form to see if I can get it working that way. Might slow it down a bit, but at least it "should" work.

  11. #11
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Okay. Not simple. REALLY not simple.

    I originally designed this by nesting queries about four levels deep, but then broke them out again for the fun of it. Here's your query:
    Code:
    SELECT
    	uq.TechID
    ,	MAX(uq.CoachingDate) AS CoachingDate
    ,	SUM(uq.OccurrenceAmt_SUM) AS OccurrenceAmt_SUM
    FROM
    	(SELECT
    		nc.TechID
    	,	'' AS CoachingDate
    	,	SUM(nc.OccurrenceAmt) AS OccurrenceAmt_SUM
    	FROM
    		(SELECT
    			TechID
    		,	OccurrenceAmt
    		FROM
    			tblOccurrences
    		WHERE
    			TechID NOT IN
    			(SELECT
    				TechID
    			FROM
    				tblOccurrences
    			WHERE
    				OccurrenceDate >= DateAdd("m", -6, Date())
    			AND
    				Type = "CD")
    		AND
    			OccurrenceDate >= DateAdd("m",-6,Date())
    		AND
    			Type <> "CD"
    		) AS nc
    	GROUP BY
    		nc.TechID, ''
    	UNION
    	SELECT
    		c.TechID
    	,	c.OccurrenceDate AS CoachingDate
    	,	SUM(c.OccurrenceAmt) AS OccurrenceAmt_SUM
    	FROM
    		(SELECT
    			TechID
    		,	OccurrenceDate
    		,	OccurrenceAmt
    		FROM
    			tblOccurrences
    		WHERE
    			OccurrenceDate >= DateAdd("m",-6,Date())
    		AND
    			Type = "CD"
    		) AS c
    	GROUP BY
    		c.TechID
    	,	c.OccurrenceDate
    	UNION
    	SELECT
    		a.TechID
    	,	'' AS CoachingDate
    	,	SUM(a.OccurrenceAmt) AS OccurrenceAmt_SUM
    	FROM
    		(SELECT
    			TechID
    		,	OccurrenceDate
    		,	OccurrenceAmt
    		FROM
    			tblOccurrences
    		WHERE
    			OccurrenceDate >= DateAdd("m",-6,Date())
    		) AS a
    	INNER JOIN
    		(SELECT
    			TechID
    		,	OccurrenceDate
    		FROM
    			tblOccurrences
    		WHERE
    			OccurrenceDate >= DateAdd("m",-6,Date())
    		AND
    			Type="CD"
    		) AS cc 
    	ON
    		a.TechID = cc.TechID
    	AND
    		a.OccurrenceDate > cc.OccurrenceDate
    	GROUP BY
    		a.TechID
    		,	'') uq
    GROUP BY
    	uq.TechID;
    It works on the sample data that you've provided, so hopefully it should work on the full table.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  12. #12
    Join Date
    May 2013
    Posts
    8
    holy balls, that's awesome. Thanks so much for your hard work on it! I'll have to try it when I get back at the data tomorrow.

    If I could give you a virtual high five, I would!

  13. #13
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You're welcome!

    I like answering questions because it makes me think about problems in a new way. For fun, try working out what it's doing and why :P
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  14. #14
    Join Date
    May 2013
    Posts
    8
    Works like a charm! ... wow - good thing you wrote that because I never would have even come close!

    Thank you again.

  15. #15
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Happy to help
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Tags for this Thread

Posting Permissions

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