how to correct this query? - 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

General SQL Server Forums

New to SQL Server Programming

how to correct this query?

Author

Topic

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-15 : 14:31:32

Select
A.[state],A.[vc_trimid] ,
A.[interval], c.Running
FROM (
select sum(Sum_of_total_cost) as Running from
[Maintenance_work].[dbo].[temp1] B where B.state=A.state and B.vc_trimid=A.vc_trimid and B.interval<=A.interval) C
inner join [Maintenance_work].[dbo].[temp1] A on A.State=C.State

I have state, vc_trimid,interval and sum_of_total in a table.
i need state,vc_trimid,interval,sum_total,running total of sum_of_total for that state and vc_trimid with the increment on interval

Sample data

Alabama 33950 15000 163.8
Alabama 33950 60000 281.734
Alabama 33950 105000 141.816
Alabama 33950 107500 157.248

I want the 5th col 163.8,163.8+281.734,163.8+281.734+141.816 etc

How to correct the query for this?

Sachin.Nand

2937 Posts

Posted - 2010-03-15 : 14:36:01

If using SQL 2005


Select
A.[state],A.[vc_trimid] ,
A.[interval], c.Running,
SUM(C.Running)over(partition by A.state)as 5th column
FROM (
select sum(Sum_of_total_cost) as Running from
[Maintenance_work].[dbo].[temp1] B where B.state=A.state and B.vc_trimid=A.vc_trimid and B.interval<=A.interval) C
inner join [Maintenance_work].[dbo].[temp1] A on A.State=C.State
group by A.[state],A.[vc_trimid] ,
A.[interval], c.Running,

PBUH

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-15 : 14:54:22

Select
A.[state],A.[vc_trimid] ,
A.[interval], c.Running,
SUM(C.Running)over(partition by A.state)
FROM (
select sum(Sum_of_total_cost) as Running from
[Maintenance_work].[dbo].[temp1] B where B.state=A.state and B.vc_trimid=A.vc_trimid and B.interval<=A.interval) C
inner join [Maintenance_work].[dbo].[temp1] A on A.State=C.State

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "A.state" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "A.vc_trimid" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "A.interval" could not be bound.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'State'.

I dont know why i get this. these columns are there

Sachin.Nand

2937 Posts

Posted - 2010-03-15 : 15:02:44

Oh sorry


select *,SUM(Running)over(partition by state)as 5th column 
(
	Select
	A.[state],A.[vc_trimid] ,
	A.[interval], c.Running,

	FROM (
	select sum(Sum_of_total_cost) as Running from
	[Maintenance_work].[dbo].[temp1] B where B.state=A.state and B.vc_trimid=A.vc_trimid and B.interval<=A.interval) C
	inner join [Maintenance_work].[dbo].[temp1] A on A.State=C.State
)from t
group by [state],[vc_trimid] ,
[interval], Running

PBUH

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-15 : 16:05:26

Select
A.[state],A.[vc_trimid] ,
A.[interval], c.Running
FROM [Maintenance_work].[dbo].[temp1] A inner join (
select sum(Sum_of_total_cost) as Running from
[Maintenance_work].[dbo].[temp1] B where B.state=A.state and B.vc_trimid=A.vc_trimid and B.interval<=A.interval) C
on A.State=C.State

I get error ... tried running only this... this too says a.state etc not found (just swapped the join tables to check if the order makes it valid). got it working some other way... will be good if this one works for me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 00:47:37

[code]
Select
A.[state],A.[vc_trimid] ,
A.[interval], c.Running
FROM [Maintenance_work].[dbo].[temp1] A
cross apply (
select sum(Sum_of_total_cost) as Running
from [Maintenance_work].[dbo].[temp1] B
where B.state=A.state and B.vc_trimid=A.vc_trimid and B.interval<=A.interval) C
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jet1337
Starting Member

11 Posts

Posted - 2010-03-16 : 03:03:34

thanks for sharing, working for me

ASPnix.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 04:11:22

welcome.

also see

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-16 : 12:19:42

thanks it worked. Where can i read a good article about the cross apply?
My next step is i want to convert the rows with same state vc trim id into one row...
ie state, vc_trimid, the interval number as col name and running total as value in the rows.
so
Alabama 3000 10000 250
Alabama 3000 15000 300
Alabama 3000 20000 400
will become
Alabama 3000 250 300 400

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 12:27:20

see this

http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources