SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Calculate values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

collie
Constraint Violating Yak Guru

399 Posts

Posted - 11/13/2013 :  14:05:18  Show Profile  Reply with Quote
Hi,

I have 2 tables. I need to calculate the values of the rows until the month/year where a deviation is found.

In addition I need to calculate all the values where no deviation exists.
create table #Payments ( [ReceiptNo] int
      ,[LineNu] int
     
      ,[IdNo]int
      
      ,[Compensation]decimal (10,2)
      ,[Providence]decimal (10,2)
     
      ,[ForMonth]int
      ,[ForYear] int)
      
      create table #maxPremia (foryear int, maxPremia decimal (10,2))
insert into #maxPremia values (2011,1910.00)
 insert into #maxPremia values (2012,3456.00)
insert into #payments values (1,1,222345,23,45,1,2011)
insert into #payments values (1,2,222345,45,45,2,2011)
insert into #payments values (2,2,222345,678.90,1234,3,2011)
insert into #payments values (2,1,222345,124.0,8900,4,2011)
insert into #payments values (2,3,222345,45,45,5,2011)
insert into #payments values (3,1,222345,6789.09,1208.00,6,2011)
insert into #payments values (3,1,222345,1234.09,1208.00,7,2011)


I need to return 2 different results

This one sums the total values of months 1+2 for year 2011 before the month of 3 (first occurrence of month that has a deviation) when the amount exceeds the value in table MaxPremia. I need to display in one row the total for months 1+2 and the values for month 3.

IdNo TotalPayments foryear formonth DeviationValue
222345 158 2001 3 1912.90
222345
158
2001
4
9024.00
The second output sums all the months (TotalPayments) that are within the limits ie months 1,2,5 and shows the deviation rows:

IdNo
TotalPayments
foryear
formonth
DeviationValue
222345
248
2001
3
1912.90
222345
248
2001
4
9024.00
222345
248
2001
6
7997.09

How can I achieve these results?

Thanks

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 11/13/2013 :  22:49:08  Show Profile  Reply with Quote
quote:
(first occurrence of month that has a deviation)

What do you mean by deviation ?
quote:
when the amount exceeds the value in table MaxPremia

What amount ? Please tell us the column name(s)


KH
Time is always against us

Go to Top of Page

collie
Constraint Violating Yak Guru

399 Posts

Posted - 11/14/2013 :  00:45:32  Show Profile  Reply with Quote
By deviation which is probably the wrong term i mean that the total per year/month exceeds the amount in maxPremia table for that year.

I sum compensation+providence

(

23.00 45.00
45.00 45.00)

for months 1 and 2 as after that month 3 has a total per month/year that exceeds the amount in maxPremia table.

I need the total for the months prior to the first month that has the amount that exceeds the amount in maxPremia table.

In addition i also need total for months 1,2,5 as these are all the months that their sum per month/year don't exceed the amount in maxPremia table.

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 11/14/2013 :  01:55:49  Show Profile  Reply with Quote
Query 1

select	*
from	#payments p
	cross apply
	(
		select	TotalPayments = sum(Compensation + Providence)
		from	#payments x
		where	x.ForYear	= p.ForYear
		and	x.ForMonth	<= p.ForMonth
	) c
	inner join #maxPremia m	on	p.ForYear	= m.foryear
where	c.TotalPayments	<= maxPremia


Query 2

select	*
from	#payments p
	inner join #maxPremia m	on	p.ForYear	= m.foryear
where	p.Compensation + p.Providence < maxPremia



KH
Time is always against us

Go to Top of Page

collie
Constraint Violating Yak Guru

399 Posts

Posted - 11/15/2013 :  10:56:30  Show Profile  Reply with Quote
Hi,

I need to get the following results and i don't get it with the queries. I think i am not making myself clear.


ReceiptNo LineNu IdNo Compensation Providence ForMonth ForYear
TotalPayments
foryear
maxPremia
1
1
222345
23.00
45.00
1
2011
68.00
2011
1910.00
1
2
222345
45.00
45.00
2
2011
158.00
2011
1910.00

What i need is the following:

ReceiptNo
LineNu
IdNo
foryear
formonth
[Compensation]+[Providence])
Total rows where
[Compensation]+[Providence])< 1910.00
2
1
222345
2011
4
9024.00
2160.9
3
1
222345
2011
6
7997.09
2160.9

I don't calculate month 7 as the max month where [Compensation]+[Providence]>1910 is 6
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000