Tough SQL Challenge - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Tough SQL Challenge

Author

Topic

jdog
Starting Member

3 Posts

Posted - 2002-11-19 : 09:42:49

Here's what I think is a tough one. The answer I ended up using is long and convoluted and there must be an easier way. I'm only looking for elegant and clever solutions that are concise.

Warning -- you must do a LOT of testing to be sure you get the correct results in ALL POSSIBLE CASES.

One final note -- this challenge involves only 1 table! How hard can it be???

OK --

We have a table listing employees timesheet headers (lets call it "Headers"). The key is EmpID, Week and there is a field called Hours which is how many hours the employee turned in that week on the timesheet.

Our company policy is that you must work at least 80 hours in any two consectutive weeks. It doesn't matter which two weeks (i.e., it doesn't have to be a two-week payroll period), but they must be back to back.

We need a query that returns all employees and weeks that break this rule.

Example:

Employee X works :

Week Hours
1 40
2 35
3 55
4 40

He is OK, because while week 2 is < 40 hours, it is "helped" by week 3 to have a total >= 80.

Employee Y:

Week Hours
1 40
2 35
3 55
4 35
5 40

Either Week 2 or Week 4 needs to be returned here (either or, doesn't matter). The 55 hours can only be used once to help create a two week period of >= 80 hours, so either week 2 or 4 needs more hours.

It can get more confusing:

Week Hours
1 40
2 55
3 35
4 35
5 55
6 40

The above employee is fine, even though weeks 3 + 4 equal less than 80 hours. That is because they are both "Helped" by other weeks to make them >= 80.

------
Sounds easy, but there are quite a few combinations. To make it as easy as possible, lets leave the Week field as an INT instead of a date.

Try to only submit answers that are:

- CONCISE -- no cursor, temp tables, etc ... This is easily solved with cursors or various temp tables. I'm looking to see if it can be done in a single SELECT statement.
- CORRECT -- they must work for ALL possible cases. Remember that once a week is used to "help" another week, it cannot be used as a helped again.

As an example, I don't want to see:

SELECT EmpID, Week, Hours as CurrHours, PrevHours
FROM
Headers
INNER JOIN
(SELECT EmpID, Week, Hours as PrevHours) Prev ON
Headers.EmpID = Prev.EmpID AND
Headers.Week = Prev.Week +1
WHERE
CurrHours + PrevHours < 80

The above may look like a solution but it's not -- see the final example as to why.

OK, have fun, and thanks!

Edited by - jdog on 11/19/2002 09:57:05

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-19 : 10:11:00

Is this a challenge, or an assignment? (for us) It might just be me, but the tone sounds a little pushy for a "can anyone help?" kind of question. And frankly, if someone comes up with a WORKABLE solution, whether they use subqueries or temp tables, or even if it's not "elegant and concise", does not matter. Especially since you do not explain why they're unacceptable. Nor are they responsible for testing this solution on YOUR data to cover all possible combinations.

Just an FYI. None of us are getting paid to help you. If you want our help we're willing to provide it, but it's strictly voluntary. Phrasing the question with that in mind will help prevent misunderstanding, if indeed I've misunderstood your intentions here. Otherwise it sounds very much like you're just looking for some free contract work.

jdog
Starting Member

3 Posts

Posted - 2002-11-19 : 10:43:57

Sorry, I guess I misread you guys. I thought you might enjoy the challenge, these are the kinds that I love. I had been reading the posts for a few months now and enjoyed the responses and challenges and marvelled at how some of you are able to come up with 1 line solutions to problems others have pages and pages for.

A simple, elegant solution instead of 50 lines of garbled spaghetti code are things I love to dissect and learn from. I suspect there are others like me.

Is it an assignment? I won't even qualify that with an answer.

I personally like challenges like this and just wanted to post this as a brain teaser. I wanted to make sure that is was clear that it would be fun to find a clean elegant solution instead of cursors and such. I would love to have time to work on this, but a co-worker eventually solved it as a series of temp tables and cursors but I know there must be a better way. I have yet to find it. Thought you all might enjoy the challenge.

Sorry I sounded pushy. I will try to post a solution to this later on in the day, now I feel as though I have to show you guys it's possible. BUt I thought you all might enjoy working on it as I do. I simple wanted to avoid people publishing solutions that are 100 lines long -- I don't want them to waste their time.

Free consulting? What do you think 99% of the questions are here in the forum ?????????? People working on there home databases of stamp collections? People are at work, solving problems, asking for help from others.

I tried to phrase it like a fun challenge, guess I failed. If you're bored and like challenges, give it a shot. Don't even post it here. Just work on it like a jumble or a crossword puzzle if you like. I wouldn't want to cheat you out of your standard hourly rate for your consulting time.

jdog
Starting Member

3 Posts

Posted - 2002-11-19 : 12:25:22

Here's the alogorithm I had been playing with, but was inspired by Rob to actually come up with . Once I put it on paper, the query almost wrote itself. I decided to post it if anyone is curious. I think it's a pretty tough little SQL challenge...

Sorry I upset anyone by demanding that they answer my question. Again, I meant to throw a brain teaser out there. I promise to not attempt to challenge the forum again.

But the key idea is to show that you don't always need stored procs, temp tables, cursors, etc.

-----

For the sake our our query, we need to make a rule: Since each helper Week can only help one week, we will assume it is always the first or MIN week that it can possibly help. That doesn't mean it can ONLY help the week before, just that if it can help both it will only help the week before.

Thus, we join the Headers table with itself (call the other one "Helper"), by empid and also where Helper.Week is 1 before or 1 after the Headers week. We criteria the Headers table to only be hours < 40. The Headers table will be the weeks needing help, the Helper table will be the week that helps it.

GROUP by the week in Helper -- this way we guarantee 1 record per helper week, and that a helper week never helps more than 1 week !!

Next, we also need to criteria by records in which Header.Hours + Helper.Hours >= 80, or where a HELP is being performed.

And since we assume we are helping the LOWEST week # from the Headers table (remember, we are grouping by the weeks in Helper, not Headers), MIN(Headers.Week) is the week we are helping.

The final solution: All weeks in the Headers table that are <40 hours, that AREN'T in the Week Helped column in the query. That is, all weeks that need to be helped but that aren't, per employee.


SELECT EmpID, Week, Hours
FROM
  Headers
LEFT OUTER JOIN
(
SELECT 
	Headers.EmpID, 
	Helper.Week AS [HelperWeek], 
	Min(Headers.Week) AS [WeekHelped]
FROM 
	Headers 
INNER JOIN 
	Headers AS Helper 
ON 	
	Headers.EmpID = Helper.EmpID AND
	Abs([Helper].[Week]-[Headers].[Week])=1
WHERE 
	Headers.Hours)<40 AND 
	[Helper].[Hours]+[Headers].[Hours] >=80
GROUP BY 
	Headers.EmpID, H2.Week;
) GetsHelp
ON
  Headers.EmpID = GetsHelp.EmpID AND
  Headers.Week = GetsHelp.WeekHelped
WHERE
  Headers.Hours < 40 AND
  GetsHelp.Week IS NULL

Edited by - jdog on 11/19/2002 12:26:22

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources