Need to calculate Amount.... - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Need to calculate Amount....

Author

Topic

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-07-01 : 05:23:36

Hello,

i am having one table with the following structure

Emp_No Version Amount Date
1000 0 500 2008-05-01
1000 1 9000 2008-05-15
1000 2 700 2008-05-20
1000 3 4000 2008-05-28
1000 4 1500 2008-06-04
1000 5 8000 2008-06-14
1000 6 10000 2008-06-25
1000 7 6000 2008-06-30

Emp_No And Version is my composite key.

Now i need to display Amount with below calculation.

I want (amount of maximum version employee of current month ) -
(amount of maximum version employee of previous month )

For Example.

Current Month Amount of Employee 1000 With Maximum Version = 6000
Previous Month Amount of Employee 1000 With Maximum Version = 4000

I need to calculate amount for each and every employee.

Can any one provide me any way to fulfill mine above requirenment.

-- Regards
Prashant Hirani

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-01 : 05:43:49

How do you want to present the result ?

KH
[spoiler]Time is always against us[/spoiler]

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-07-01 : 06:02:51

i think function will work..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-01 : 06:22:52

[code]SELECT e.Emp_No, c.Version, c.Amount, c.[Date], p.Version, p.Amount, p.[Date]
FROM emp e
left JOIN
(
SELECT Emp_No, Version, Amount, [Date],
row_no = row_number() OVER (PARTITION BY Emp_No ORDER BY Version DESC)
FROM TABLE
WHERE [Date] >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND [Date] < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
) c ON e.Emp_No = c.Emp_No
AND c.row_no = 1
left JOIN
(
SELECT Emp_No, Version, Amount, [Date],
row_no = row_number() OVER (PARTITION BY Emp_No ORDER BY Version DESC)
FROM TABLE
WHERE [Date] >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
AND [Date] < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
) p ON e.Emp_No = p.Emp_No
AND p.row_no = 1[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 06:47:50

[code]select t.emp_no,isnull(max(case when seq=1 and month(date)=month(getdate()) then Amount else null end),0)-isnull(max(case when seq=1 and month(date)=month(getdate())-1 then Amount else null end),0)
from
(select row_number() over(partition by emp_no,dateadd(mm,datediff(mm,0,date),0) order by version desc) as seq,
*
from @test)t
group by t.emp_no[/code]

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-07-01 : 06:53:12

Thanks..

It's working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources