Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

summing rows of a field in a SQL Server 2005 view


Hello:

I have successfully created a view in SQL Server 2005.  But, I found out
here on the Newsgroups board that views are not allowed to have "COMPUTE" as
a means of addings rows of a field (column) together.

I need to have the view have the ability to sum.  You see, an application
that I work with is going to need to pull data from this view.  And,
unfortunately, that app does not sum either.

To be more specific, I need to have the view sum the amounts in my "PTO
hours worked" column.  Is there any way in the world to get a view to do
that?  If not, I'm hosed.

Thanks!

childofthe1980s

Please post the DDL for your table and view, sample data plus expected
results.

--
   Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON   Canada
.
"childofthe1980s" <childofthe19@discussions.microsoft.com> wrote in
message news:9ACF5F32-390C-4278-8811-AEB1835206E8@microsoft.com...
Hello:

I have successfully created a view in SQL Server 2005.  But, I found out
here on the Newsgroups board that views are not allowed to have "COMPUTE" as
a means of addings rows of a field (column) together.

I need to have the view have the ability to sum.  You see, an application
that I work with is going to need to pull data from this view.  And,
unfortunately, that app does not sum either.

To be more specific, I need to have the view sum the amounts in my "PTO
hours worked" column.  Is there any way in the world to get a view to do
that?  If not, I'm hosed.

Thanks!

childofthe1980s

-----------------------------------------------Reply-----------------------------------------------

Hello:

Below is my query.  I need to have--grouped by the check date
(UPR30300.CHEKDATE)--the view sum the total amounts of each of the two CASE
statements (Regular Hours Worked and PTO Hours Worked).  This will be a sum
of regular hours and PTO hours for all employees for each check date (pay
period).

SELECT
    UPR30300.EMPLOYID as [Employee ID], UPR30100.EMPLNAME as [Employee
Name], UPR30300.CHEKDATE as [Check Date], UPR30300.TRXBEGDT as [Beginning
Date],
UPR30300.TRXENDDT as [Ending Date], UPR00100.DEPRTMNT as [Department], CASE
UPR30300.PAYROLCD
        WHEN 'HOUR' THEN UPR30300.UNTSTOPY ELSE 0 END as [Regular Hours Worked],
CASE UPR30300.PAYROLCD WHEN 'VACN' THEN
UPR30300.UNTSTOPY ELSE 0 END as [PTO Hours Worked]
FROM
    { oj (TWO.dbo.UPR30300 UPR30300 LEFT OUTER JOIN TWO.dbo.UPR30100
UPR30100 ON
        UPR30300.CHEKNMBR = UPR30100.CHEKNMBR AND
    UPR30300.PYADNMBR = UPR30100.PYADNMBR AND
    UPR30300.CHEKDATE = UPR30100.CHEKDATE AND
    UPR30300.EMPLOYID = UPR30100.EMPLOYID AND
    UPR30300.DEPRTMNT = UPR30100.DEPRTMNT)
     INNER JOIN TWO.dbo.UPR00100 UPR00100 ON
        UPR30100.EMPLOYID = UPR00100.EMPLOYID}
WHERE
    UPR30300.CHEKDATE > {ts '2007-01-01 00:00:00.00'} AND
    (UPR30300.PAYROLCD = 'VACN' OR
    UPR30300.PAYROLCD = 'HOUR') AND
    UPR00100.DEPRTMNT = 'INST' AND
    UPR00100.Primary_Pay_Record = 'HOUR' AND
    UPR30100.VOIDED = 0
ORDER BY UPR30300.CHEKDATE
COMPUTE SUM(CASE UPR30300.PAYROLCD WHEN 'HOUR' THEN UPR30300.UNTSTOPY ELSE 0
END),
SUM(CASE UPR30300.PAYROLCD WHEN 'VACN' THEN UPR30300.UNTSTOPY ELSE 0 END)
BY UPR30300.CHEKDATE

Thanks!

childofthe1980s

As Tom requested, it would really help to have sample data and expected
results so we can really see what you're hoping to achieve.
In the meantime, you might want to look at GROUP BY with the CUBE or ROLLUP
options.

--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com

"childofthe1980s" <childofthe19@discussions.microsoft.com> wrote in
message news:0DF28B12-0CCB-4E3F-98FF-C26405F866DA@microsoft.com...

How would you propose that I get the sample data out here on the message
board, Kalen?

Just show us what a few rows of each table would look like if you did a
SELECT from the table, and then show us a few rows of the expected results.

It doesn't have to be the real data; sometimes it's easier to come up with
dummy data that allows you and us to focus on the solution.

Tom also asked for DDL which is the CREATE TABLE statements.

Nobody here can run the query that you've presented, so there is no chance
that anyone could give you a guaranteed solution. If you give us a smaller
table definition, complete with CREATE TABLE and INSERT statements, someone
could possible come up with a solution.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com

"childofthe1980s" <childofthe19@discussions.microsoft.com> wrote in
message news:2E7D9D7E-151C-41E7-BBC7-056B285365EB@microsoft.com...

To be honest with you, that's all I can give.  I mean there are no "CREATE
TABLE" statements or anything of that sort.  They're just there.  Please,
believe me.

I guess on the "dummy data", the best I can explain it is this.  Let's say
the check date (ending pay period) is 02/15/06.  And, let's say Sally worked
40 hours, Joe worked 80 hours, and Bill worked 60 hours.  This would be a
total amount of regular hours for the "group by" field of check date
(UPR30300.CHEKDATE) of 180 hours.  That "180" is what I'm looking for.

I don't have any more to give you.  I wish the stuff posted on Books Online
and google web sites did a better job of putting this in layman's terms.  I'm
a SQL Server 2005 DBA, believe it or not.  And, I can't interpret some of
this stuff.

childofthe1980s

Nope, group by and that other stuff didn't work either.  Now, I've got other
error messages that I have to figure out.

I'm going to turn this over to a co-worker.  This is more trouble than it's
worth.  I want the last 12 hours of my life back!  LOL!!!

childofthe1980s

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc