Random SELECT question/help - 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 2005 Forums

Transact-SQL (2005)

Random SELECT question/help

Author

Topic

Firemaple
Starting Member

14 Posts

Posted - 2010-01-07 : 10:27:45

Ok I have 2 tables.
tblAnalyst = general analyst into, contracthiredate, conversiondate
tblAttendance = daily entry of users attendance, breaks, etc

my query looks like this:

select b.currentdate, a.USW_Number, a.hiredate, a.contracthiredate from tblAnalyst a
inner join tblAttendence b on a.usw_number = b.usw_num
where b.currentDate between '12/1/08' and '1/7/2010'
and a.contracthiredate != '  /  /'
and b.currentdate < a.hiredate

and I get this error:
"Arithmetic overflow error converting expression to data type datetime."

I know WHY i'm getting the error, just not sure how to fix my query.
I'm getting the error because sometimes a.hiredate's value is ' / /' and you can't do the comparison b.currentdate < a.hiredate.

My goal is to get each day that was worked by a contractor, and that contractors name. Once they are converted, I do not want to gather that information.

Thanks in advance!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 10:34:22

then make last condition

and (a.hiredate = ' / /' or b.currentdate < a.hiredate)

Firemaple
Starting Member

14 Posts

Posted - 2010-01-07 : 10:38:34

can't do that, because the comparison still takes place and give the same error. (went ahead and attempted anyways!)

However I did fix it by doing a union.

select dateadd(month, datediff(month, 0, b.currentdate),0), a.USW_Number, count(*)  from tblAnalyst a
inner join tblAttendence b on a.usw_number = b.usw_num
where b.currentDate between '12/1/08' and '1/7/2010'
and a.contracthiredate != '  /  /'
and 
b.currentdate < a.hiredate
and a.hiredate != '  /  /'
group by dateadd(month, datediff(month, 0, b.currentdate),0),a.usw_number
Union
select dateadd(month, datediff(month, 0, b.currentdate),0), a.USW_Number, count(*)  from tblAnalyst a
inner join tblAttendence b on a.usw_number = b.usw_num
where b.currentDate between '12/1/08' and '1/7/2010'
and a.contracthiredate != '  /  /'
and a.hiredate = '  /  /'
group by dateadd(month, datediff(month, 0, b.currentdate),0),a.usw_number

order by 1, 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 10:44:07

what about this?


select dateadd(month, datediff(month, 0, b.currentdate),0), a.USW_Number, count(*)  from tblAnalyst a
inner join tblAttendence b on a.usw_number = b.usw_num
where b.currentDate between '12/1/08' and '1/7/2010'
and a.contracthiredate != '  /  /'
and 
b.currentdate < coalesce(nullif(a.hiredate,'  /  /'),'9999-12-31')  
and a.hiredate != '  /  /'
group by dateadd(month, datediff(month, 0, b.currentdate),0),a.usw_number

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources