Help in Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help in Query

Author

Topic

vmurali
Yak Posting Veteran

88 Posts

Posted - 2007-03-21 : 09:57:57

I have a table like
code task date1 hrs1 date2 hrs2
1 1 3/3/2007 3.00 3/4/2007 7.00
2 1 3/3/2007 4.00 3/4/2007 1.00

Now I need to get the result given 2 dates

Code totalhrs
1 xx.xx
2 yy.yy

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 10:03:42

1) Use proper datatype!
2) How do you store "7:20 AM"?

SET DATEFORMAT MDY

-- Prepare sample data
DECLARE	@Sample TABLE (Code INT, Task INT, Date1 DATETIME, Hrs1 MONEY, Date2 DATETIME, Hrs2 MONEY)

INSERT	@Sample
SELECT	1, 1, '3/3/2007', 3.00, '3/4/2007', 7.00 UNION ALL
SELECT	2, 1, '3/3/2007', 4.00, '3/4/2007', 1.00

-- Show the expected output
SELECT		Code,
		SUM(DATEDIFF(HOUR, Date1, Date2) - Hrs1 + Hrs2) AS Hours
FROM		@Sample
GROUP BY	Code
ORDER BY	Code

Output is

Code	Hours
----	-----
1	28.00
2	21.00

Peter Larsson
Helsingborg, Sweden

vmurali
Yak Posting Veteran

88 Posts

Posted - 2007-03-21 : 10:14:09

Hrs is hours spend by employee on certain task eg. 8.5

I want to know how to use case statement to achieve my result

quote:

Originally posted by Peso

1) Use proper datatype!
2) How do you store "7:20 AM"?

SET DATEFORMAT MDY

-- Prepare sample data
DECLARE	@Sample TABLE (Code INT, Task INT, Date1 DATETIME, Hrs1 MONEY, Date2 DATETIME, Hrs2 MONEY)

INSERT	@Sample
SELECT	1, 1, '3/3/2007', 3.00, '3/4/2007', 7.00 UNION ALL
SELECT	2, 1, '3/3/2007', 4.00, '3/4/2007', 1.00

-- Show the expected output
SELECT		Code,
		SUM(DATEDIFF(HOUR, Date1, Date2) - Hrs1 + Hrs2) AS Hours
FROM		@Sample
GROUP BY	Code
ORDER BY	Code

Output is

Code	Hours
----	-----
1	28.00
2	21.00

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-21 : 10:17:43

CASE? And why is that?

SELECT		Code,
		SUM(Hrs1 + Hrs2) AS Hours
FROM		@Sample
where date1 = @date1 and date2 = @date2
GROUP BY	Code
ORDER BY	Code

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 10:20:23

Ok, this took an expected turn.
What has Date2 to do with Date1?
And why are they stored in the same record?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources