Calculate values - 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 2008 Forums

Transact-SQL (2008)

Calculate values

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-11-13 : 14:05:18

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)

17689 Posts

Posted - 2013-11-13 : 22:49:08

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
[spoiler]Time is always against us[/spoiler]

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-11-14 : 00:45:32

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.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-14 : 01:55:49

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
[spoiler]Time is always against us[/spoiler]

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2013-11-15 : 10:56:30

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources