Total Cost And Total Hours Worked

Question

Post reply

Total Cost And Total Hours Worked

corey.psychological

SSC Enthusiast

Points: 183
More actions
April 15, 2008 at 3:53 pm

#70168

Hey I Got 2 Probs here
1) I have to show the details of an Invoice and calculate the total cost for the Products and return the result as ONE?
HERE IS MY CODE SO FAR
SELECT (Price * Quantity) AS Total FROM InvoiceDetails, Price, Product
WHERE InvoiceDetails.ProductCode = Product.ProductCode
AND Product.PriceID = Price.PriceID
AND InvoiceID = @InvoiceCode
2) I need to calculate the total hours worked for a specific call rate(ID)
lol i am so lost here, I can return the results but it calculates for each call rate INDIVIDUALLY.
ANY HELP

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004693 More actions · Answer 1

Corey,

This looks a bit like homework, and we don't mind helping with that because you tried on question 1.

We need just a bit more information...

In the first problem you say you need to "return the result as ONE"? On what? A single result? Or do you just need to do it in a single query?

On the second problem, you've given virtually no information at all.

On both problems, it would be very helpful if you provided a bit of data, the table schema information, and a sample of the output you want. Most of that can be done fairly easily... see the URL in my signature line below.

If this is homework, has your instructor taught you about GROUP BY, COMPUTE, UNION ALL, or how to use "Aggregate" functions like SUM()?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

corey.psychological SSC Enthusiast Points: 183 More actions · Answer 2

hey hey,

Thanx for your help JEFF

I Figured out the first problem with the SUM Command

Now for the second one

I have two tables

CREATE TABLE Rate

(

RateID char(10) NOT NULL CONSTRAINT PK_Rate PRIMARY KEY NONCLUSTERED,

TimeDescription nvarchar(100) NOT NULL,

Rate char(11) NOT NULL,

)

CREATE TABLE ConsultancyDetails

(

InvoiceID int REFERENCES Invoice(InvoiceID),

RateID char(10) NOT NULL REFERENCES Rate(RateID),

Date varchar(10) NOT NULL,

StartTime DATETIME NOT NULL,

EndTime DATETIME NOT NULL,

CONSTRAINT FK_ConsultancyDetails

PRIMARY KEY NONCLUSTERED (InvoiceID, RateID)

)

INSERT Rate VALUES ('AA', '08:00-17:00', 'Normal')

INSERT Rate VALUES ('AB', '18:00-21:00', 'Over Time')

INSERT Rate VALUES ('AC', '08:00-17:00', 'Weekend')

INSERT ConsultancyDetails VALUES ('1','AA', '02/02/08', '08:45', '09:45')

INSERT ConsultancyDetails VALUES ('2','AA', '02/02/08', '11:00', '12:00')

and I am trying to crate a view

this what I ahve so far

SELECT RateID, (EndTime - StartTime) AS TotalHours

FROM ConsultancyDetails

I need to calculate the total for EACH call rate.

I am looking for the total of all the call rates individually

My Select Statement returns the results

but like this

RateID TOTAL

AA 1900-01-01 01:00:00.000

AC 1900-01-01 04:00:00.000

.... .....

I would like the result to be

RateID TOTAL

AA 1900-01-01 02:00:00.00

AC 1900-01-01 04:00:00.00

Jeff Moden SSC Guru Points: 1004693 More actions · Answer 3

SELECT RateID, CONVERT(CHAR(8),CAST(SUM(CAST(EndTime - StartTime AS FLOAT)) AS DATETIME),108) AS TotalHours

FROM ConsultancyDetails

GROUP BY RateIDYou can't sum the difference between two date times... but you can if you convert it to FLOAT first. 😉

Follow the nested parethesis in the formula I wrote...

1. Difference between two dates occurs

2. Answer converted to FLOAT so can SUM

3. SUM occurs

4. Answer converted back to datetime

5. Answer converted to CHAR and displayed in format # 108 which is hh:mi:ss on a 24 hour clock.

Only time you run into a problem is if more than 24 hours occurs... but it will still correctly let you span midnight separating two days if you don't exceed 24 hours total duration.

Things to study in Books Online...

What a date reallly is (floating point number with a fixed exponent representing the number of whole and fractional days since 1900-01-01... Books Online does lie occasionally because they don't think us mere mortals will understand :P)

CONVERT() especially date/time formats.

FLOAT datatype

SUM()

GROUP BY

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

corey.psychological SSC Enthusiast Points: 183 More actions · Answer 4

aaah SPOT ON!!

:D:D:D:D:D:D:D:D:D:D:D:D:D

THANKS ALOT FOR THE HELP!!!!!!!

Jeff Moden SSC Guru Points: 1004693 More actions · Answer 5

corey.psychological (4/16/2008)
aaah SPOT ON!!
:D:D:D:D:D:D:D:D:D:D:D:D:D
THANKS ALOT FOR THE HELP!!!!!!!

Heh... you did it... you took the time to post some simple table structures and some data. You made my day because all I had to do was test an answer for you and explain why it works instead of building a test harness. I sure wish other folks would get in that habit and that's why I carry the URL in my signature line.

Don't forget that the code will NOT report any error if the time goes above 24 hours... it will simply report the time that less than a whole increment of 24 hours be it 2 days or 2000 days. We'd have to tweek things a bit if you go over 24 hours.

Thanks again for the feedback, Corey.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)