Difference Between 2 Columns and Running Total

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)

Difference Between 2 Columns and Running Total

Author

Topic

island82
Starting Member

5 Posts

Posted - 2012-02-08 : 20:43:52

I am looking for ideas to calculate the difference between two column from different tables based on the a certain date criteria,and get the running total. i am new to SQL and would like input on some ideas on how to best solve this.
Eg:

Table1
TicketsOpen | TicketOpenMonthYear
21 | Aug-11
17 | Jul-11

Table2
TicketsClose | TicketCloseMonthYear
4 | Aug-11
13 | Jul-11

Desired Output:
Month | TicketsOpen | TicketsClose | ActiveTickets | Note(Calc. logic):
Jul-11 | 17 | 13 | 4 | 17-13
Aug-11 | 21 | 4 | 21 | (4+21)-4

Thanks for looking!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-08 : 21:00:33

[code]
; with cte as
(
select Month, TicketsOpen, TicketClose, ActieTickets = sum(TicketsOpen) - sum(TicketsClose)
from
(
select Month = TicketOpenMonthYear, TicketsOpen, TicketsClose = 0
from Table1

union all

select Month = TicketOpenMonthYear, TicketsOpen = 0, TicketsClose
from Table2
) t
)
select c.Month, c.TicketsOpen, c.TicketClose, a.ActieTickets
from cte c
cross apply
(
select ActieTickets = sum(ActieTickets)
from cte x
where x.Month <= t.Month
) a
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-08 : 21:49:57

quote:

Originally posted by khtan


; with cte as
(
    select Month, Sum(TicketsOpen) AS TicketsOpen, Sum(TicketClose) AS TicketClose, ActieTickets = sum(TicketsOpen) - sum(TicketsClose)
    from
    (
        select Month = TicketOpenMonthYear, TicketsOpen, TicketsClose = 0
        from   Table1

        union all

        select Month = TicketOpenMonthYear, TicketsOpen = 0, TicketsClose
        from   Table2
    ) t
 group by Month
)
select c.Month, c.TicketsOpen, c.TicketClose, a.ActieTickets
from   cte c
       cross apply
       (
           select ActieTickets = sum(ActieTickets)
           from   cte x
           where  x.Month <= t.Month
       ) a

KH
[spoiler]Time is always against us[/spoiler]

This?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-08 : 21:58:45

Absolutely. I missed out that

Thanks Visakh

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-08 : 22:00:41

no problem

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

island82
Starting Member

5 Posts

Posted - 2012-02-09 : 14:42:49

thanks for the quick replies guys. i tried out what visakh16 had, and i got this error:Msg 4104, Level 16, State 1, Line 25
The multi-part identifier "t.Month" could not be bound.

i have tried again to manipulate the queries and still got the same error, it parse fine, but when i execute it, i get that error.
Could it be that this:
select ActieTickets = sum(ActieTickets)
from cte x
where x.Month <= t.Month
cant see into the alias t?
appreciate any help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-09 : 17:41:00

i think it should be c. just copied from original suggestion didnt check the aliases


; with cte as
(
    select Month, Sum(TicketsOpen) AS TicketsOpen, Sum(TicketClose) AS TicketClose, ActieTickets = sum(TicketsOpen) - sum(TicketsClose)
    from
    (
        select Month = TicketOpenMonthYear, TicketsOpen, TicketsClose = 0
        from   Table1

        union all

        select Month = TicketOpenMonthYear, TicketsOpen = 0, TicketsClose
        from   Table2
    ) t
 group by Month
)
select c.Month, c.TicketsOpen, c.TicketClose, a.ActieTickets
from   cte c
       cross apply
       (
           select ActieTickets = sum(ActieTickets)
           from   cte x
           where  x.Month <= c.Month
       ) a

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

island82
Starting Member

5 Posts

Posted - 2012-02-10 : 17:23:37

thanks Visakh16, Query runs without error now. However, the output seems to be a little off:
i get this output-
using the same table 1 and table 2 example

Month | TicketsOpen | TicketsClose | ActiveTickets
Jul-11 | 17 | 13 | 21
Aug-11 | 21 | 4 | 17

i'll try and see if there is something incorrect with the
(
select Month, Sum(ExceptionOpenCount) AS ExceptionOpenCount, Sum(ExceptionCloseCount) AS ExceptionCloseCount, ActiveTickets = sum(ExceptionOpenCount) - sum(ExceptionCloseCount)
from
(
select Month = MonthOpen, ExceptionOpenCount, ExceptionCloseCount = 0
from testopen

union all

select Month = MonthClose, ExceptionOpenCount = 0, ExceptionCloseCount
from testClose
) k
group by Month
)

thanks

island82
Starting Member

5 Posts

Posted - 2012-02-10 : 17:59:55

ok, i changed the query to this:
; with k(Month,ExceptionOpenCount,ExceptionCloseCount,ActieTickets)
as
(
select Month, Sum(ExceptionOpenCount) AS ExceptionOpenCount, Sum(ExceptionCloseCount) AS ExceptionCloseCount, ActieTickets = sum(ExceptionOpenCount) - sum(ExceptionCloseCount)
from
(
select Month = MonthOpen, ExceptionOpenCount, ExceptionCloseCount = 0
from testopen

union all

select Month = MonthClose, ExceptionOpenCount = 0, ExceptionCloseCount
from testClose
) k
group by Month
)

select c.Month, c.ExceptionOpenCount, c.ExceptionCloseCount, a.ActieTickets
from k c
cross apply
(
select ActieTickets = sum(ActieTickets)
from k as x
where x.Month = c.Month<----------
) a
order by Month desc

and now output is:
Month | TicketsOpen | TicketsClose | ActiveTickets
Jul-11 | 17 | 13 | 4 <-------first row is correct
Aug-11 | 21 | 4 | 17 <------should be 21, since 4+21-4=21

so now i just have to figure out how to take the activetickets from the previous row and add it to the row below

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 18:36:56

do you've a date field by any chance? i think its skewing result as you've dates stores as string in format Month - Year so its regarding alphabetical sequence i guess

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-10 : 21:43:29

you should keep the MonthOpen/Close in date or datetime data type with day as 01. It will makes things much easier.

What i did here is to convert the Jul-11 to 2011-07-01


; with k (Month,ExceptionOpenCount,ExceptionCloseCount,ActieTickets)
as
(
	select Month, Sum(ExceptionOpenCount) AS ExceptionOpenCount, Sum(ExceptionCloseCount) AS ExceptionCloseCount, ActieTickets = sum(ExceptionOpenCount) - sum(ExceptionCloseCount)
	from
	(
		select Month = convert(date, '01-' + MonthOpen), ExceptionOpenCount, ExceptionCloseCount = 0
		from testopen
		
		union all
		
		select Month = convert(date, '01-' + MonthClose), ExceptionOpenCount = 0, ExceptionCloseCount
		from testClose
	) k
	group by Month
)
select c.Month, c.ExceptionOpenCount, c.ExceptionCloseCount, a.ActieTickets
from 	k c
	cross apply
	(
		select 	ActieTickets = sum(ActieTickets)
		from 	k as x
		where x.Month <= c.Month
	) a
order by Month desc

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-11 : 08:01:52

really emphsizes fact why you need to use proper data types for the fields. storing it as date would have made matters much easier and you can always use format functions to show them in whatever formats you want (in this case Month - Year)

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

island82
Starting Member

5 Posts

Posted - 2012-02-13 : 16:40:03

yea, i realize that now. i will re create the table to store the date as date data type
thanks guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources