Can we do this in a single query ??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

Transact-SQL (2000)

Can we do this in a single query ???

Author

Topic

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-02-26 : 19:25:53

Ok .. here's another problem I am facing ....
Its been a long day and my mind is really not working

I have three tables lets say

tab1
-------
boss
month
Bonus

tab2
-------
boss
employee

Each Boss is given some bonus amount ... he has to distribute it evenly among his employees. data for each boss monthly bonus is availible.

But what i need is amount per employee per day (and remember amount being recieved will vary upon bonus amount with boss aND NO OF day in month). I need to generate a tabe for the whole year containing records

taboutput
-----------
boss
employee
date
amount

Any Ideas ??

Enigma

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-02-26 : 20:01:05

Thanks for that Enigma.. gives me an excuse to stop documentation for a while....

Ok here is a start.. don't now if this is what you want....or the accuracy


create table Bonus(Boss INT, MonthD datetime, Bonus money)
go
create table Employee(Boss INT, EmployeeID INT)
go
truncate table bonus
insert Bonus
	SELECT 1, '20040123', 1000
	UNION ALL
	SELECT 1, '20040201', 2000
	UNION ALL
	SELECT 2, '20040101', 3000
go
Insert Employee
	select 1,1
	union all
	select 1,2
	union all
	select 1,3
	union all
	select 2,4
	union all
	select 2,1
go
SELECT B.boss, E.EmployeeID, (Bonus*1.0)/DATEDIFF(dd,dateadd(m,datediff(mm,0,MonthD),0) , dateadd(m,1,dateadd(m,datediff(mm,0,MonthD),0)))/(SELECT COUNT(*) FROM  Employee WHERE Boss = E.boss group by Boss) as BonusPerDay,
DATEADD(d, Number, dateadd(m,datediff(mm,0,MonthD),0)) AS DOM
from Bonus B
inner join Employee E on e.boss = B.boss
inner join test.dbo.Numbers N ON Number < DATEDIFF(dd,dateadd(m,datediff(mm,0,MonthD),0) , dateadd(m,1,dateadd(m,datediff(mm,0,MonthD),0)))

DavidM

"SQL-3 is an abomination.."

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-02-26 : 20:24:22

Its almost what i got around to ... this gives results for a month ... i need for the complete year

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-02-26 : 23:04:15

Is this any better?


SELECT Boss, EmployeeID, DOY, MAX(BonusPerDay) as BonusPerDay
FROM
(
SELECT B.boss, E.EmployeeID,
DATEADD(d, Number, dateadd(yyyy,datediff(yyyy,0,MonthD),0) ) AS DOY,
case WHEN DATEADD(d, Number, dateadd(yyyy,datediff(yyyy,0,MonthD),0) )
		BETWEEN dateadd(m,datediff(mm,0,MonthD),0) AND dateadd(m,1,dateadd(m,datediff(mm,0,MonthD),0))
		THEN (Bonus*1.0)/DATEDIFF(dd,dateadd(m,datediff(mm,0,MonthD),0) , dateadd(m,1,dateadd(m,datediff(mm,0,MonthD),0)))/(SELECT COUNT(*) FROM  Employee WHERE Boss = E.boss group by Boss)
		ELSE 0 end as BonusPerDay
from Bonus B
inner join Employee E on e.boss = B.boss
inner join test.dbo.Numbers N ON Number < DATEDIFF(dd,dateadd(yyyy,datediff(yyyy,0,MonthD),0) , dateadd(yyyy,1,dateadd(yyyy,datediff(yyyy,0,MonthD),0)))
) AS X
GROUP BY Boss, EmployeeID, DOY
ORDER BY Boss, EmployeeID, DOY

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources