Query Error 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)

Query Error Help

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2011-05-03 : 10:03:58

Table: Loans

LoanID, Sent, Dept, EmployeeFlag
1, 12/02/2010, A, 0
2, 12/03/2010, B, 0
3, 12/04/2010, 0

Table: Notes
NoteID, LoanID, DateTime, Category
1, 1, 01/03/2011 11:47:00, NULL
2, 1, 01/04/2011 11:50:00, C
3, 1, 01/04/2011 11:55:00, B
4, 2, 01/04/2011 11:58:00, A

Wanted Result:
LoanID, Sent, dept
1, 12/02/2010, A

Objective, Get the results of Loans if the earliest/first Note for that loan where the note category is not null

Query I am using without success:
select LoanID, sent, Dept
from Loans
where EmployeeFlag = 0 and exists
(SELECT MIN(DateTime) FROM tblNotes
Where LoanID = tblloans.loanID and category is not null )

Error Msg:
Syntax error converting character string to smalldatetime data type. The DateTime in Notes is a column of datatype smalldatetime

Any help appreciated - thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-03 : 10:27:23

[code]
select *
from
(
select l.LoanID, l.Sent, l.Dept, n.category,
row_no = row_number() over (partition by l.LoanID order by n.DateTime)
from Loans l
inner join tblNotes n on l.LoanID = n.LoanID
where l.EmployeeFlag = 0
) l
where l.row_no = 1
and l.category is null
[/code]

KH
[spoiler]Time is always against us[/spoiler]