Mombu the Microsoft Forum sponsored links

Go Back   Mombu the Microsoft Forum > Microsoft > CrossTab Question
User Name
Password
REGISTER NOW! Mark Forums Read

sponsored links


Reply
 
1 17th July 01:36
jason mackenzie
External User
 
Posts: 1
Default CrossTab Question


Here's a different twist on my previous question. Any help is tremendously
appreciated. If any more information is needed I'll be happy to provide it.

SELECT TOP 100 PERCENT
MonthName,
Month,
SUM(CASE WHEN StatusName = 'Complete' THEN Total ELSE 0
END) AS Complete,
SUM(CASE WHEN StatusName = 'New' THEN Total ELSE 0 END)
AS New,
SUM(CASE WHEN StatusName = 'New' THEN Total ELSE 0
END) - SUM(CASE WHEN StatusName = 'Complete' THEN Total ELSE 0 END) AS
Outstanding
FROM vwNewComplete
GROUP BY MonthName, month
ORDER BY 2


which returns the following results:

MonthName Month Complete New Outstanding
Feb-2003 2 71 98
27
Mar-2003 3 41 63
22
Apr-2003 4 252 306
54
May-2003 5 527 562
35
Jun-2003 6 277 295
18
Jul-2003 7 30
-5


This is essentially what I'm looking for except for one problem. Currently
my outstanding column is just New - Complete. I need it to be:

Last Month's Outstanding + This Month's New - This Month's Complete.

The numbers I'm looking for are below.


MonthName Month Complete New Outstanding
Corrected Outstanding
Feb-2003 2 71 98
27 27
Mar-2003 3 41 63
22 49
Apr-2003 4 252 306
54 103
May-2003 5 527 562
35 138
Jun-2003 6 277 295
18 156
Jul-2003 7 30
-5 151
  Reply With Quote


  sponsored links


2 25th July 01:20
jason mackenzie
External User
 
Posts: 1
Default CrossTab Question


Actually the best way to word it now that I look at it is that I need to do
a running total on the Outstanding column. Can anyone help with that?
  Reply With Quote
3 25th July 01:22
vishal parkar
External User
 
Posts: 1
Default CrossTab Question


Try:
drop table vwname
create table vwname
(monthname varchar(500),
complete int,
new int,
outstanding int
)

insert into vwname values('Feb-2003',71 , 98,27)
insert into vwname values('mar-2003',41 , 63 ,22)
insert into vwname values('apr-2003',252 , 306 ,54 )
insert into vwname values('may-2003',527 , 562 ,35 )
insert into vwname values('jun-2003',277 , 295 ,18 )
insert into vwname values('jul-2003',30 , -5 ,-5 )

Following query is without making use of outstanding amount. ie i m
considering only 3 columns of the table ie complete,new and monthname

select new_mon,complete,new, outstanding,
(select sum(new-complete) from
(select *, convert(datetime,'01-' + monthname,106)new_mon from vwname) v2
where new_mon <= v1.new_mon) correct_outstanding
from
(select *, convert(datetime,'01-' + monthname,106)new_mon from vwname) v1

OR
Following query makes use of outstanding amount column. ie i m considering
all 4 columns of the table ie complete,new, outstanding and monthname

select new_mon,complete,new, outstanding,
(select sum(outstanding) from
(select *, convert(datetime,'01-' + monthname,106)new_mon from vwname) v2
where new_mon <= v1.new_mon) correct_outstanding
from
(select *, convert(datetime,'01-' + monthname,106)new_mon from vwname) v1

--
-Vishal
  Reply With Quote


  sponsored links


Reply


Thread Tools
Display Modes




Copyright 2006 SmartyDevil.com - Dies Mies Jeschet Boenedoesef Douvema Enitemaus -
666