Joining fields

Hello,
I Hope one of you can help me with the following:
I try to join three fields from a table into one output field: month,
day, year ==> date.
And I can't get it tow work right. The datatype of the fields is
numeric. Since this query is nested into another one the datatype of
the outputfield should be DATETIME.
I already tried google but it could not help me,
Thanks in advance
Jean-Paul Rijnsburger (Netherlands)Try:
declare
@.year numeric
, @.month numeric
, @.day numeric
select
@.year = 2006
, @.month = 1
, @.day = 2
select
convert (datetime, str (@.year) +'/' + str (@.month) + '/' + str (@.day))
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Jean-Paul Rijnsburger" <Jeepee75@.gmail.com> wrote in message
news:1136207889.492149.133070@.o13g2000cwo.googlegroups.com...
Hello,
I Hope one of you can help me with the following:
I try to join three fields from a table into one output field: month,
day, year ==> date.
And I can't get it tow work right. The datatype of the fields is
numeric. Since this query is nested into another one the datatype of
the outputfield should be DATETIME.
I already tried google but it could not help me,
Thanks in advance
Jean-Paul Rijnsburger (Netherlands)|||It works,
Thanks Tom|||Tom Moreau (tom@.dont.spam.me.cips.ca) writes:
> Try:
> declare
> @.year numeric
> , @.month numeric
> , @.day numeric
> select
> @.year = 2006
> , @.month = 1
> , @.day = 2
> select
> convert (datetime, str (@.year) +'/' + str (@.month) + '/' + str (@.day))
This may produce different result depending on language and dateformat
settings. Add a third parameter to control the interpreration:
select
convert (datetime, str (@.year) +'/' + str (@.month) + '/' + str (@.day),
111)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Joining and Conditional Column Data Help

I have 2 table that I want to join and output a row on a condition that one of the records have a null in the field. Heres what I have.

employee table (empid, name)
tasks table (taskid, empid, taskname, resolution)

If the resolution is null than I want it to be accounted for in each employee record. Heres my query so far that joins the 2 tables and accounts for each employee and counts each task they have. I need another column that counts the tasks.resolution's null values for each employee but cant figure it out. Thanks for any help!

SELECT e.empid,
e.name,
COUNT(t.ID) as 'tcount'
FROM tasks t
RIGHT JOIN employee e ON c.empid = t.empid
GROUP BY e.empid, e.name
order by 'tcount' desc

SELECT

e.empid,

e

.empname,

COUNT

(t.taskid)as'tcount'

FROM

tasks t

LEFT

JOIN employee eON e.empid= t.empid

WHERE

t.resolutionISNULL

GROUP

BY e.empid, e.empname

Order

by tcountDesc|||

limno's query doesn't quite work the way you'd expect. Because it is being filtered by where the resolution is null from within the WHERE clause, it will eliminate employees that have no records where resolution is null from the output. If you want the employees listed even if they have no tasks, then use this instead:

SELECT empid, empname, (SELECTCOUNT(*)FROM tasksWHERE tasks.empid=employee.empidAND resolutionISNULL)as'tcount'FROM employeeOrder by tcountDesc

|||

Thanks. That helped me put together someting else

selecte.empid, e.ename, count(*) as 'tcount', sum(case when t.resolution isnull and t.empid is not null then 1 else 0 end) as 'NULL resolution'
from employee as e
left join tasks as t on t.empid = e.empid
group by e.empid, e.ename
order by 3 desc