select 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

SQL Server 2005 Forums

Transact-SQL (2005)

select query

Author

Topic

akas
Starting Member

42 Posts

Posted - 2008-09-25 : 09:54:07

hi all,

i have one column total cost in which the values for example are:

Totalcost
------------
17550.000000000
17550.000000000
17550.000000000
0.000000000
0.000000000

i want cumulative total cost for this column

like:
total cost
---------------
17550.000000000
35100.000000000
52650.000000000
52650.000000000
52650.000000000

my select query is this:

select op 5 convert(varchar,s2.docdate,101) as Date, s3.itemnmbr as ItemNumber,
sy.inet8 as ItemTitle, sum(s3.quantity) as Quantity, sum(s3.quantity * s3.unitprce) as TotalCost,
s2.custname as [Customer Name],
r.custclas as [Customer Class], s2.address1 as Add1, s2.City,
s2.State,s2.zipcode as Zip, s2.CNTCPRSN as Contact, '(' + SUBSTRING(s2.phnumbr1, 1, 3) + ')' +
SUBSTRING(s2.phnumbr1, 4, 3) + '-' +
SUBSTRING(s2.phnumbr1, 7, 4) as Phone

from sop30200 s2

join sop30300 s3
on s2.sopnumbe=s3.sopnumbe

join sy01200 sy
on s3.itemnmbr=sy.master_id

join rm00101 r
on r.custnmbr=s2.custnmbr

where

s2.sopnumbe like 'INV%'
and s2.docdate between '1/1/2005' and '12/31/2005'
and sy.master_type ='itm'

group by s3.itemnmbr, s2.Custnmbr,
s2.custname, r.custclas , s2.City, s2.State, s2.zipcode, s3.quantity,
s2.docdate,s2.sopnumbe,sy.inet8,s2.address1,s2.cntcprsn,s2.phnumbr1

Order by s3.quantity desc

can anyone help me to get the correct results.

Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-25 : 09:55:25

Search for Running Total+SQL Server in google

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 09:55:33

Add them accumulative according to what sorting parameter?
ID? Date?

E 12°55'05.63"
N 56°04'39.26"

akas
Starting Member

42 Posts

Posted - 2008-09-25 : 10:15:12

peso,

parameter is date.

thanks madhivanan and peso for replying.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 10:45:24

may be this

SELECT m.*,b.TotalRunningCost
FROM
(
select  s2.sopnumbe,convert(varchar,s2.docdate,101) as Date, s3.itemnmbr as ItemNumber,
sy.inet8 as ItemTitle, sum(s3.quantity) as Quantity, sum(s3.quantity * s3.unitprce) as TotalCost,
s2.custname as [Customer Name],
r.custclas as [Customer Class], s2.address1 as Add1, s2.City, 
s2.State,s2.zipcode as Zip, s2.CNTCPRSN as Contact, '(' + SUBSTRING(s2.phnumbr1, 1, 3) + ')' + 
SUBSTRING(s2.phnumbr1, 4, 3) + '-' + 
SUBSTRING(s2.phnumbr1, 7, 4) as Phone

from sop30200 s2 

join sop30300 s3
on s2.sopnumbe=s3.sopnumbe

join sy01200 sy
on s3.itemnmbr=sy.master_id 

join rm00101 r
on r.custnmbr=s2.custnmbr 

where 

s2.sopnumbe like 'INV%'
and s2.docdate between '1/1/2005' and '12/31/2005'
and sy.master_type ='itm'

group by s3.itemnmbr, s2.Custnmbr, 
s2.custname, r.custclas , s2.City, s2.State, s2.zipcode, s3.quantity,
s2.docdate,s2.sopnumbe,sy.inet8,s2.address1,s2.cntcprsn,s2.phnumbr1
)m
OUTER APPLY
(
SELECT  sum(quantity * unitprce) as TotalRunningCost
FROM sop30300 t1
join sop30200 t2 
on t2.sopnumbe=t1.sopnumbe
join sy01200 sy1
on s1.itemnmbr=sy1.master_id 
WHERE t1.sopnumbe=m.sopnumbe
AND t2.docdate <=m.Date 
and
t1.sopnumbe like 'INV%'
and t2.docdate between '1/1/2005' and '12/31/2005'
and sy1.master_type ='itm'
)b

akas
Starting Member

42 Posts

Posted - 2008-09-25 : 11:51:05

thanks visakh16 but not getting the correct results it gives me the column - totalcost like:

total cost
---------------
52650.000000000
52650.000000000
52650.000000000
52650.000000000
52650.000000000

same amount in all top 5 rows. as i want

total cost
---------------
17550.000000000
35100.000000000
52650.000000000
52650.000000000
52650.000000000

can you help me out of this? thanks a lot!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 11:54:35

change like below and see if it works

SELECT m.*,b.TotalRunningCost
FROM
(
select  s2.sopnumbe,s2.docdate,convert(varchar,s2.docdate,101) as Date, s3.itemnmbr as ItemNumber,
sy.inet8 as ItemTitle, sum(s3.quantity) as Quantity, sum(s3.quantity * s3.unitprce) as TotalCost,
s2.custname as [Customer Name],
r.custclas as [Customer Class], s2.address1 as Add1, s2.City, 
s2.State,s2.zipcode as Zip, s2.CNTCPRSN as Contact, '(' + SUBSTRING(s2.phnumbr1, 1, 3) + ')' + 
SUBSTRING(s2.phnumbr1, 4, 3) + '-' + 
SUBSTRING(s2.phnumbr1, 7, 4) as Phone

from sop30200 s2 

join sop30300 s3
on s2.sopnumbe=s3.sopnumbe

join sy01200 sy
on s3.itemnmbr=sy.master_id 

join rm00101 r
on r.custnmbr=s2.custnmbr 

where 

s2.sopnumbe like 'INV%'
and s2.docdate between '1/1/2005' and '12/31/2005'
and sy.master_type ='itm'

group by s3.itemnmbr, s2.Custnmbr, 
s2.custname, r.custclas , s2.City, s2.State, s2.zipcode, s3.quantity,
s2.docdate,s2.sopnumbe,sy.inet8,s2.address1,s2.cntcprsn,s2.phnumbr1
)m
OUTER APPLY
(
SELECT  sum(quantity * unitprce) as TotalRunningCost
FROM sop30300 t1
join sop30200 t2 
on t2.sopnumbe=t1.sopnumbe
join sy01200 sy1
on s1.itemnmbr=sy1.master_id 
WHERE t1.sopnumbe=m.sopnumbe
AND t2.docdate <=m.docdate 
and
t1.sopnumbe like 'INV%'
and t2.docdate between '1/1/2005' and '12/31/2005'
and sy1.master_type ='itm'
)b

akas
Starting Member

42 Posts

Posted - 2008-09-25 : 12:05:29

SELECT top 5 m.*,b.TotalRunningCost
FROM
(
select s2.docdate,
sum(s3.quantity * s3.unitprce) as TotalCost,
s2.custname as [Customer Name],
r.custclas as [Customer Class], s2.address1 as Add1, s2.City,
s2.State,s2.zipcode as Zip, s2.CNTCPRSN as Contact, '(' + SUBSTRING(s2.phnumbr1, 1, 3) + ')' +
SUBSTRING(s2.phnumbr1, 4, 3) + '-' +
SUBSTRING(s2.phnumbr1, 7, 4) as Phone

from sop30200 s2

join sop30300 s3
on s2.sopnumbe=s3.sopnumbe

join rm00101 r
on r.custnmbr=s2.custnmbr

where

s2.sopnumbe like 'INV%'
and s2.docdate between '1/1/2005' and '12/31/2005'

group by
s2.custname, r.custclas , s2.City, s2.State, s2.zipcode,
s2.address1,s2.cntcprsn,s2.phnumbr1,s2.docdate
)m

OUTER APPLY
(
SELECT sum(quantity * unitprce) as TotalRunningCost
FROM sop30300 t1
join sop30200 t2
on t2.sopnumbe=t1.sopnumbe

WHERE
t1.sopnumbe like 'INV%'
and t2.docdate between '1/1/2005' and '12/31/2005'
AND t2.docdate <=m.docdate

)b

i modified my query this way - only selected columns but still getting same results..

total cost
---------------
52650.000000000
52650.000000000
52650.000000000
52650.000000000
52650.000000000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources