May 14, 2008 at 10:09 am
You call. I'd say no since that "resets" this conversation - but perhaps we can get Steve's attention to move it.
If you do - at least point to this as a continuation....
The WITH syntax is a Common Table Expression (or CTE), a new SQL 2005 syntax option.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 14, 2008 at 10:13 am
By the way - Jason has a small typo (the source of your errors) in his 2000-compatible version:
SELECT
a.name
,reg_hrs
,reg_ern
,total = reg_hrs + reg_ern
FROM
ps_employees AS a
INNER JOIN (SELECT
emplid
,SUM(al_hours) AS reg_hrs --<--change here to match the references to it.
,SUM(earnings) AS reg_ern
FROM
ps_al_chk_hrs_ern
WHERE
row_nbr = 1
GROUP BY
emplid) AS t
ON a.emplid = t.emplid
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 14, 2008 at 10:18 am
That worked! Thanks everyone!
May 14, 2008 at 10:40 am
Matt Miller (5/14/2008)
By the way - Jason has a small typo (the source of your errors) in his 2000-compatible version:
SELECT
a.name
,reg_hrs
,reg_ern
,total = reg_hrs + reg_ern
FROM
ps_employees AS a
INNER JOIN (SELECT
emplid
,SUM(al_hours) AS reg_hrs --<--change here to match the references to it.
,SUM(earnings) AS reg_ern
FROM
ps_al_chk_hrs_ern
WHERE
row_nbr = 1
GROUP BY
emplid) AS t
ON a.emplid = t.emplid
You guys are so picky .... LOL 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 15, 2008 at 12:36 pm
One more thing, if either reg_hrs or reg_ern is NULL, I want to display zero. I used ISNULL but it still output NULL. How can I accomplish this?
SELECT
a.name
,reg_hrs
,reg_ern
,total = reg_hrs + reg_ern
FROM
ps_employees AS a
left outer JOIN (SELECT
emplid
,isnull(SUM(al_hours),0) AS reg_hrs
,isnull(SUM(earnings),0) AS reg_ern
FROM
ps_al_chk_hrs_ern
WHERE
row_nbr = 1
GROUP BY
emplid) AS t
ON a.emplid = t.emplid
May 15, 2008 at 12:40 pm
You're getting nulls when the employee doesn't have any hours in the secondary table ps_al_chk_hrs_ern.
You need to put an isnull in the main SELECT as well....
SELECT
a.name
,reg_hrs
,reg_ern
,total = isnull(reg_hrs + reg_ern,0)
FROM
ps_employees AS a
left outer JOIN (SELECT
emplid
,isnull(SUM(al_hours),0) AS reg_hrs
,isnull(SUM(earnings),0) AS reg_ern
FROM
ps_al_chk_hrs_ern
WHERE
row_nbr = 1
GROUP BY
emplid) AS t
ON a.emplid = t.emplid
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 15, 2008 at 12:44 pm
Thanks Matt!
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply