MIN and DATEDIFF problem - 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

SQL Server Development (2000)

MIN and DATEDIFF problem

Author

Topic

carioca
Starting Member

6 Posts

Posted - 2004-11-05 : 05:41:02

Hi, I have the following problem

I have 2 tables. Table T1 contains an employees working
timestamp with an id containing the type of work he did.
Table T2 contains records containing those worktype id's
together with an hourly rate and a starting date for the hourly
rate.

For example T1 contains a record like:

employeeid | minutesworked | worktype | dateworked
--------------------------- -------------------------
100 | 2000 | 23 | 7-1-2004
101 | 1000 | 25 | 7-1-2004

T2 contains records like

hourrateid | worktype | hourlyrate | startingdate
--------------------------------------------------------
1 | 23 | $20 | 1-1-2004
2 | 23 | $25 | 6-1-2004
3 | 23 | $30 | 8-1-2004

Now i need to know the worktypeid which is applicable
taking into account the type of work done and the
date worked. In this example hourrateid 3 is not applicable
because the starting date of the hourly rate is after the working date. The
hourrateid needed here is 2.

Now I have written the following SQL statement which returns
the following error ("Multiple columns are specified in an
aggregated expression containing an outer reference. If an
expression being aggregated contains an outer reference, then
that outer reference must be the only column referenced in the
expression").

SELECT T2.hourrateid FROM T1 LEFT JOIN T2 ON T1.worktype = T2.worktype WHERE T2.hourrateid IN (SELECT TOP 1 hourrateid FROM T2 T3 WHERE T3.worktype = T2.worktype GROUP BY hourrateid HAVING
MIN(DATEDIFF("d",T3.startingdate,T1.dateworked)) >= 0 ORDER BY
MIN(DATEDIFF("d",T3.startingdate,T1.dateworked)))

NOTE that when I replace MIN(DATEDIFF("d",T3.startingdate,T1.dateworked)) with MIN(DATEDIFF("d",T3.startingdate,GETDATE())) it works fine....

Can anybody please help me?

Kristen
Test

22859 Posts

Posted - 2004-11-05 : 08:04:21

What's the datatype of the dateworked / startingdate columns? They look to be text, rather than datatime?

If so maybe you need to CONVERT them to datetime so that DATEDIFF is presented with two datetime values - otherwise if will "guess" the DMY / YMD / ... format of the date string, which it may get wrong.

If the first parameter does not need to be a variable I would suggest dropping the quotes - my preference is to use the full name of the date part, as I think it make it more readable and avoids confusion between M=Month and Minute - or even Millisecond!

e.g. SELECT DATEDIFF(Day, '01 Jan 2004', GetDate())

Kristen

carioca
Starting Member

6 Posts

Posted - 2004-11-05 : 08:15:07

No, they are smalldatetime format, there is definitely nothing wrong with the date format... the problem is the outer reference in the DATEDIFF function back to Table T1.

Both T3.startingdate and T1.dateworked need to be variables... but if i replace T1.dateworked by GetDate() it works fine... so the problem is really the outer reference T1.dateworked.

Kristen
Test

22859 Posts

Posted - 2004-11-05 : 08:31:56

I mistook your "1 post" as being a sign of a beginner - my mistake, sorry.

Is T1 in scope in the sub select - my memory lets me down without making a test!

I had a quick look at whether you could change the subselect to a JOIN, but I can't spot that with a brief look - and I've got to go to a meeting. If someone doesn't catch it first I'lll have another look later on.

Kristen

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-05 : 09:20:17

As always, take it 1 step at a time, break it into parts.

what do you need to return first? for each row in T1, you need to get to the latest "startingDate" BEFORE the dateworked for that particular worktype:


select T1.*, 
  (select max(Startingdate) 
   from T2 
   where T2.WOrkType = T1.WorkType and 
         T2.StartingDate <= T1.DateWorked) as MaxStartDate
from 
  T1

Look at the above, make sure it makes sense, run it a few times, eyeball the results, look at a few samples and make sure it is giving you what you need.

When you are happy that it is working, then you can simply join from that SQL statement to table T2 to get the rest of the info:


select 
  A.*, T2.HourlyRate
from 
  (Above SQL) A
inner join 
  T2
on 
  A.WorkType = T2.WorkType and 
  A.MaxStartDate = T2.StartingDate

By the way -- I hope the primary key of T2 is NOT that random "hourRateID" column. It should be a composite primary key of WorkType/StartingDate since I assume you cannot have two rows in that table with the same worktype and starting date.

Does this help? Break the problem into steps -- write one layer of SQL at a time and build as you go, checking each step of the way to ensure it returns logical results.

- Jeff

carioca
Starting Member

6 Posts

Posted - 2004-11-05 : 09:35:03

Wow Jeff this helps a great deal, thanx!
BUT althought the combination in T2 WorkType/StartingDate should be unique (else it wouldn't make sense) the primary key IS the "hourRateID". Double WorkType/StartingDate could occur, but that's the inputers problem... if the query - in case of double WorkType/StartingDate combinations - returns a random result which does match the criteria that's just fine... But I don't see why that (the primary key being "hourRateID" should constite a problem?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-05 : 09:44:27

quote:

BUT althought the combination in T2 WorkType/StartingDate should be unique (else it wouldn't make sense) the primary key IS the "hourRateID". Double WorkType/StartingDate could occur, but that's the inputers problem...

!!

It's called database integrity! But what if the data entry person puts two different rates in for the same date? the query I gave you will no longer work and will actually cause duplicate rows to be generated. look at the final SQL I gave you -- it will match two MaxStartingDates to TWO rows in your rates table -- returning twice as many rows as it should.

The whole point of primary keys to not to say "oh, that's obvious the PK and since i makes no sense otherwise I won't bother enforcing it" ! It's the opposite -- since it is the primary key of the table, MAKE IT the primary key of the table. Users make mistaking during data entry, you have to expect that -- if that situation occured in this database, and I was your boss, I'd not blame the users who do the data entry I'd blame the app or the database for allowing it.

You might wish to read up on database design and normalization. Here's one link:

http://www.datamodel.org/NormalizationRules.html

- Jeff

carioca
Starting Member

6 Posts

Posted - 2004-11-05 : 09:52:32

Haha thanx Jeff, you're right... but in this case it's really no big deal... but i don't understand really... the PK is different, even for duplicate WorkType/StartingDate combinations. What now if i'd like duplicate WorkType/StartingDate combinations to exist (but of course with different HourRateID (PK)) but i'd like just the one with the highest PK-ID to be returned?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-05 : 09:56:25

then you'd have to re-write the query I gave to and make it more complicated and slower, because it won't work.

- Jeff

carioca
Starting Member

6 Posts

Posted - 2004-11-05 : 10:34:54

Ok, thanx!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources