May 14, 2008 at 9:07 am
The following is a shortened version of my script. As you can see in the Total field, its taking the values from Reg_Hrs and adding to Reg_Ern. I was wondering if there is a way to pass the values of Reg_Hrs and Reg_Ern to 2 variables so Total = @reg_hrs + @reg_ern. Of course the variables need to by dynamic as the values change for each employee.
select a.name
,reg_hrs = (select sum(al_hours) from ps_al_chk_hrs_ern
where emplid = a.emplid
and row_nbr = 1)
,reg_ern = (select sum(earnings) from ps_al_chk_hrs_ern
where emplid = a.emplid
and row_nbr = 1)
,total = ((select sum(al_hours) from ps_al_chk_hrs_ern
where emplid = a.emplid
and row_nbr = 1)
+
(select sum(earnings) from ps_al_chk_hrs_ern
where emplid = a.emplid
and row_nbr = 1))
from ps_employees a
May 14, 2008 at 9:16 am
I'm not sure I understand why you want a "variable" Are you trying to avoid performing the aggregation more than one time?
If so then ....
WITH totals
AS (SELECT
emplid
,SUM(al_hours) AS reg_hours
,SUM(earnings) AS reg_ern
FROM
ps_al_chk_hrs_ern
WHERE
row_nbr = 1
GROUP BY
emplid)
SELECT
a.name
,reg_hrs
,reg_ern
,total = reg_hrs + reg_ern
FROM
ps_employees AS a
INNER JOIN totals AS t
ON a.emplid = t.emplid
______________________________________________________________________
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 14, 2008 at 9:22 am
Correct, I am trying to avoid performing the aggregation more than once because I will be using reg_hrs and reg_ern throughout the script to do to more calculation. Plus, it will be alot easier for me to read and maintain. I will try your script out. Thanks.
May 14, 2008 at 9:24 am
Jason, I am getting this error....
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
May 14, 2008 at 9:26 am
Place a semi-colon at the end of the line immediately before the WITH statement.
______________________________________________________________________
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 14, 2008 at 9:33 am
Jason Selburg (5/14/2008)
Place a semi-colon at the end of the line immediately before the WITH statement.
Jason, can you please be more clear on where I should put the semi-colon?
May 14, 2008 at 9:40 am
is250sp (5/14/2008)
Jason Selburg (5/14/2008)
Place a semi-colon at the end of the line immediately before the WITH statement.Jason, can you please be more clear on where I should put the semi-colon?
While it's "prettier" to put the semicolon at the end of the previous line, you can technically put it immediately to the left of the word WITH. As in (copying Jason's code as is):
;WITH totals
AS (SELECT
emplid
,SUM(al_hours) AS reg_hours
,SUM(earnings) AS reg_ern
FROM
ps_al_chk_hrs_ern
WHERE
row_nbr = 1
GROUP BY
emplid)
SELECT
a.name
,reg_hrs
,reg_ern
,total = reg_hrs + reg_ern
FROM
ps_employees AS a
INNER JOIN totals 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 9:46 am
Matt Miller (5/14/2008)
is250sp (5/14/2008)
Jason Selburg (5/14/2008)
Place a semi-colon at the end of the line immediately before the WITH statement.Jason, can you please be more clear on where I should put the semi-colon?
While it's "prettier" to put the semicolon at the end of the previous line, you can technically put it immediately to the left of the word WITH. As in (copying Jason's code as is):
;WITH totals
AS (SELECT
emplid
,SUM(al_hours) AS reg_hours
,SUM(earnings) AS reg_ern
FROM
ps_al_chk_hrs_ern
WHERE
row_nbr = 1
GROUP BY
emplid)
SELECT
a.name
,reg_hrs
,reg_ern
,total = reg_hrs + reg_ern
FROM
ps_employees AS a
INNER JOIN totals AS t
ON a.emplid = t.emplid
now i get this error.....
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ';'.
May 14, 2008 at 9:56 am
is250sp (5/14/2008)
now i get this error.....Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ';'.
What version of SQL Server are you using? What's the compatibility level on the database you're running this against?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
May 14, 2008 at 9:57 am
Are you using SQL 2005?
Compatibility Level set to 90?
______________________________________________________________________
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 14, 2008 at 9:59 am
Jason Selburg (5/14/2008)
Are you using SQL 2005?Compatibility Level set to 90?
The db is on 2000, but I am using Management Studio 2005 to query. Where do I find out the Compatibility Level?
May 14, 2008 at 10:03 am
is250sp (5/14/2008)
Jason Selburg (5/14/2008)
Are you using SQL 2005?Compatibility Level set to 90?
The db is on 2000, but I am using Management Studio 2005 to query. Where do I find out the Compatibility Level?
If the database is attached to a 2000 server - the CTE is worthless. Using SSMS makes no difference - this should be on the 2000 forum.
Back to the drawing board.
----------------------------------------------------------------------------------
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:04 am
Under Properties > Options for the database. But if it's SQL 2000, the CTE will not work.
Try this instead.
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_hours
,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
______________________________________________________________________
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 14, 2008 at 10:05 am
Matt Miller (5/14/2008)
is250sp (5/14/2008)
Jason Selburg (5/14/2008)
Are you using SQL 2005?Compatibility Level set to 90?
The db is on 2000, but I am using Management Studio 2005 to query. Where do I find out the Compatibility Level?
If the database is attached to a 2000 server - the CTE is worthless. Using SSMS makes no difference - this should be on the 2000 forum.
Back to the drawing board.
My bad. Should I repost this on the 2000 forum?
May 14, 2008 at 10:08 am
Jason Selburg (5/14/2008)
Under Properties > Options for the database. But if it's SQL 2000, the CTE will not work.Try this instead.
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_hours
,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
got this error...
Msg 207, Level 16, State 3, Line 1
Invalid column name 'reg_hrs'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'reg_hrs'.
btw, what is CTE? does the WITH statement only works on 2005 db?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply