Calculation in select statment

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)

Calculation in select statment

Author

Topic

aslu1163
Starting Member

6 Posts

Posted - 2011-01-18 : 14:31:37

Hello All!
I try calculated hours by serv date for person.
For some reason order by in partition by statement is not work,
what I do wrong? Please help.

select *
, sum(cast(hours as INT)) over (partition by b_alt_id order by b_alt_id, c_li_fst_dos_dt )AS TOT_UOS
from Report4_results.
I looking for caluclation:

Person1 Serv Dt Un Total
001 1/10/2001 1 1
001 1/11/2001 1 2
001 1/16/2001 5 7

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-01-18 : 15:40:46

What difference does the order make when you are summing something?

Try removing the whole order by part.

maybe you want to group by:

select Person, ServDt, sum(hours)
from ...
group by Person, ServDt

aslu1163
Starting Member

6 Posts

Posted - 2011-01-18 : 16:34:47

I try calculate Un field and place to Total.
Example what I want see:

Person | DateServ | Un | Total
001 | 1/10/2000 | 1 | 1
001 | 1/20/2000 | 3 | 4
001 | 1/30/2000 | 5 | 9

Thank you in advanced.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-01-19 : 07:20:29

quote:
Originally posted by aslu1163

I try calculate Un field and place to Total.
Example what I want see:

Person | DateServ | Un | Total
001 | 1/10/2000 | 1 | 1
001 | 1/20/2000 | 3 | 4
001 | 1/30/2000 | 5 | 9

Thank you in advanced.

We have no idea what an 'Un' is. Please elaborate and explain and keep in mind we can't see your data and have no idea how your tables are set up!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-19 : 07:40:14

Look up the term "running total"...this is what you need.

- Lumbago

My blog-> www.thefirstsql.com

aslu1163
Starting Member

6 Posts

Posted - 2011-01-19 : 08:13:21

Un is field - Number_Units
But even I use group by still not calculated for each date.

quote:
Originally posted by denis_the_thief

quote:
Originally posted by aslu1163

I try calculate Un field and place to Total.
Example what I want see:

Person | DateServ | Un | Total Units
001 | 1/10/2000 | 1 | 1
001 | 1/20/2000 | 3 | 4
001 | 1/30/2000 | 5 | 9

Thank you in advanced.

We have no idea what an 'Un' is. Please elaborate and explain and keep in mind we can't see your data and have no idea how your tables are set up!

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-01-19 : 09:12:06

You've shown us the records you want to see as output. Can you show us what the input is for those records, from the table. Can you show us the query you said doesn't calculate. Can you show us the output from that query. We don't want to see hundred records just enough so that someone can understand what you need.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-01-19 : 09:25:22

I see, running total. Please try to make your posts clearer.

Run this:

with T as
	(select 1 RN, 2 UN union select 2, 4 union select 3, 3) 
select
	RN, UN, (select sum(UN) from T T2 where T2.RN <= T.RN ) Total
from
  T

aslu1163
Starting Member

6 Posts

Posted - 2011-01-19 : 09:30:12

I re-write query to:

select MAX(alt_id) as PERSON
, UNITS as UOS
, sum(UNITS) as TOTAL
, Date_Serv
from Report2_results
where alt_id = '111'
group by c_li_reimb_unt_num, c_li_fst_dos_dt
order by alt_id, Date_serv

Person UOS TOTAL Date_serv **Total
111 5.00 5.00 2010-04-19 5
111 2.00 2.00 2010-07-02 7
111 3.00 3.00 2010-08-06 10
**Total - this is field shoud be calculated.

quote:
Originally posted by denis_the_thief

You've shown us the records you want to see as output. Can you show us what the input is for those records, from the table. Can you show us the query you said doesn't calculate. Can you show us the output from that query. We don't want to see hundred records just enough so that someone can understand what you need.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-01-19 : 09:44:04

Did you try my example? Did it give you the idea?

Still confusing. You're showing us a query that won't work anyways.

So you want to sum the person's hours by date and then display that as a running total???

aslu1163
Starting Member

6 Posts

Posted - 2011-01-19 : 09:56:27

I get confuse with UNION select not sure what code doing?
I try calculated total units for person by date-serv.
It should work for example UOS first raw UOS 5 total 5,
second raw uos 2 total should be 7
th raw uos 3 total should be 10
Partition by statement show bellow worked in Oracle, but not in SQL server,
I try work around with SQL Server 2005 query, this is why I ask you guys help. Thanks

quote:
Originally posted by denis_the_thief

Did you try my example? Did it give you the idea?

Still confusing. You're showing us a query that won't work anyways.

So you want to sum the person's hours by date and then display that as a running total???

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-01-19 : 10:13:22

Just that your column names are confusing. You have b_alt_id represents a person, how are we supposed to know that!

But you are right about the Oracle with running total, it is a little bit more powerful than SQL Server with these functions.

Try this, straight from your original query:

select 
  *,
  (select sum(cast(hours as INT)) from Report4_results R2
	where R2.b_alt_id = R1.b_alt_id and
		R2.c_li_fst_dos_dt <= R1.c_li_fst_dos_dt) AS TOT_UOS


from 
  Report4_results R1

aslu1163
Starting Member

6 Posts

Posted - 2011-01-19 : 10:19:47

It is excellent!
Thank you very much. It is work!!!

quote:
Originally posted by denis_the_thief

Just that your column names are confusing. You have b_alt_id represents a person, how are we supposed to know that!

But you are right about the Oracle with running total, it is a little bit more powerful than SQL Server with these functions.

Try this, straight from your original query:

select
*,
(select sum(cast(hours as INT)) from Report4_results R2
where R2.b_alt_id = R1.b_alt_id and
R2.c_li_fst_dos_dt <= R1.c_li_fst_dos_dt) AS TOT_UOS

from
Report4_results R1

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-01-19 : 10:23:00

Welcome.

I just didn't realize at the beginnning it was from an Oracle function or that you were looking for a running total.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources