sum() multiply() - 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

sum() multiply()

Author

Topic

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-15 : 04:19:15

Hi all. I have 2 tables to join with.

table1 - leavecredits
empdcno type dateassigned earnedDay availedDay
222 SL 3/4/2007 8.0 2.0
222 PL 4/4/2007 8.0 2.0
222 ML 5/4/2007 8.0 0.0
333 PL 6/4/2007 8.0 3.0
444 SL 4/6/2007 8.0 1.0

table2 - leaveledger
empdcno leavedate hrsleave hrswork type
222 6/4/2007 2.0 8.0 SL
222 6/5/2007 8.0 8.0 SL
222 6/6/2007 8.0 8.0 SL
222 6/7/2007 8.0 8.0 SL
222 6/8/2007 8.0 8.0 Sl
222 6/9/2007 8.0 8.0 SL
333 6/7/2007 8.0 8.0 PL

I am creating a report for leaveledger of all employees.

Expected Result:

empdcno type dateassigned earnedDay earnedHrs availedDay availedHrs balDay balHours

222 SL 3/4/2007 8.0 42.0 2.0 2.0 16.0 6.0 48.0
333 PL 6/4/2007 8.0 8.0 1.0 3.0 24.0 5.0 40.0

formula:
earnedHrs = in leaveleadger table 8 x 5 + 2 = 42.0
availedHrs = availedDay x hrswork
balday = earnedDay - availedDay
balHours = (earnedDay - availedDay) x hrswork

thanks
-Ron-

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 04:43:11

What are the two missing SUMs?

empdcno	type	earnedDay	earnedHrs	availedDay	availedHrs	balDay	balHours
222	SL	3/4/2007	8.0		42.0		2.0		2.0	16.0		6.0	48.0
333	PL	6/4/2007	8.0		8.0		1.0		3.0	24.0		5.0	40.0

Peter Larsson
Helsingborg, Sweden

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-15 : 04:47:21

empdcno type dateassigned earnedDay earnedHrs availedDay availedHrs balDay balHours

222 SL 3/4/2007 8.0 42.0 2.0 16.0 6.0 48.0
333 PL 6/4/2007 8.0 8.0 3.0 24.0 5.0 40.0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 05:00:07

[code]-- prepare sample data
declare @leavecredits table (empdcno int, type varchar(2), dateassigned datetime, earnedDay money, availedDay money)

insert @leavecredits
select 222, 'SL', '3/4/2007', 8.0, 2.0 union all
select 222, 'PL', '4/4/2007', 8.0, 2.0 union all
select 222, 'ML', '5/4/2007', 8.0, 0.0 union all
select 333, 'PL', '6/4/2007', 8.0, 3.0 union all
select 444, 'SL', '4/6/2007', 8.0, 1.0

declare @leaveledger table (empdcno int, leavedate datetime, hrsleave money, hrswork money, type varchar(2))

insert @leaveledger
select 222, '6/4/2007', 2.0, 8.0, 'SL' union all
select 222, '6/5/2007', 8.0, 8.0, 'SL' union all
select 222, '6/6/2007', 8.0, 8.0, 'SL' union all
select 222, '6/7/2007', 8.0, 8.0, 'SL' union all
select 222, '6/8/2007', 8.0, 8.0, 'SL' union all
select 222, '6/9/2007', 8.0, 8.0, 'SL' union all
select 333, '6/7/2007', 8.0, 8.0, 'PL'

-- Show the expected result
SELECT EmpDcNo,
Type,
DateAssigned,
EarnedDay,
SUM(EarnedHrs) AS EarnedHrs,
AvailedDay,
AvailedHrs,
BalDay,
BalHours
FROM (
SELECT c.EmpDcNo,
c.Type,
c.DateAssigned,
c.EarnedDay,
l.HrsLeave AS EarnedHrs,
c.AvailedDay AS AvailedDay,
c.AvailedDay * l.HrsWork AS AvailedHrs,
c.EarnedDay - c.AvailedDay AS BalDay,
(c.EarnedDay - c.AvailedDay) * l.HrsWork AS BalHours
FROM @LeaveCredits AS c
INNER JOIN @LeaveLedger AS l ON l.EmpDcNo = c.EmpDcNo AND l.Type = c.Type
) AS x
GROUP BY EmpDcNo,
Type,
DateAssigned,
EarnedDay,
AvailedDay,
AvailedHrs,
BalDay,
BalHours
ORDER BY EmpDcNo,
Type[/code]
Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources