SQL Query Assistance: How Can I Get 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

General SQL Server Forums

New to SQL Server Programming

SQL Query Assistance: How Can I Get Running Total

Author

Topic

ccuser011
Starting Member

1 Post

Posted - 2014-03-28 : 10:18:41

How can I get running total from transaction log?

I am looking to get WTD, MTD and YTD from sales log.
I am familiar with basic approach of WHERE b.id <= a.id but it gives only YTD. I am stumbling at WTD and MTD running that reset when week or month changes.

i.e.
I am adding two table as an example.
First table is my transaction log
Second is table how I want my query result to look like... I am looking for query that can produce this result.

Currently I am dumping sales log in Excel and calculating there (with SUMIF formula). I am running this query for whole enterprise and excel is now crashing due to large size of data. Therefore I want to to perform running calculation in SSMS instead of excel.

Can someone advise query script to get result back as shown in table 2?

Appreciate any assistance

===========
TABLE 1 = SALES LOG
===========
DayIndex WeekIndex MonthIndex YearIndex Unit_Sold
201401 201401 201401 2014 5
201402 201401 201401 2014 3
201403 201401 201401 2014 7
201404 201401 201401 2014 9
201405 201401 201401 2014 1
201406 201401 201401 2014 9
201407 201401 201401 2014 2
201408 201402 201401 2014 3
201409 201402 201401 2014 4
201410 201402 201401 2014 6
201411 201402 201401 2014 2
201412 201402 201401 2014 5
201413 201402 201401 2014 3
201414 201402 201401 2014 7
201415 201403 201401 2014 9
201416 201403 201401 2014 1
201417 201403 201401 2014 9
201418 201403 201401 2014 2
201419 201403 201401 2014 3
201420 201403 201401 2014 4
201421 201403 201401 2014 0
201422 201404 201401 2014 2
201423 201404 201401 2014 5
201424 201404 201401 2014 3
201425 201404 201401 2014 7
201426 201404 201401 2014 9
201427 201404 201401 2014 2
201428 201404 201401 2014 0
201429 201405 201401 2014 2
201430 201405 201401 2014 3
201431 201405 201401 2014 4
201432 201405 201402 2014 6
201433 201405 201402 2014 2
201434 201405 201402 2014 5
201435 201405 201402 2014 3
201436 201406 201402 2014 2
201437 201406 201402 2014 4
201438 201406 201402 2014 2
201439 201406 201402 2014 1
201440 201406 201402 2014 8
201441 201406 201402 2014 0
201442 201406 201402 2014 3

===========
TABLE 2 = SALES REPORT (Looking for query to get this output
===========

Week MTD YTD Unit_Sold_WTD Unit_Sold_MTD Unit_Sold_YTD
201401 201401 2014 36 36 36
201402 201401 2014 30 66 66
201403 201401 2014 28 94 94
201404 201401 2014 28 122 122
201405 201401 2014 9 131 131
201405 201402 2014 16 16 147
201406 201402 2014 20 36 167

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-03-29 : 08:05:59

Looking at you output example, focusing on week 5, it seems like the Week to date is actually WeekMonth to date. Is this correct?
Eatherway, here's my attempt to solve your problem. This will give you both Week to date and WeekMonth to date:

select a.WeekIndex
      ,a.MonthIndex
      ,a.YearIndex
      ,sum(case when b.WeekIndex=a.WeekIndex then b.Unit_Sold else 0 end) as Unit_Sold_WTD
      ,sum(case when b.WeekIndex=a.WeekIndex and b.MonthIndex=a.MonthIndex then b.Unit_Sold else 0 end) as Unit_Sold_WMTD
      ,sum(case when b.MonthIndex=a.MonthIndex then b.Unit_Sold else 0 end) as Unit_Sold_MTD
      ,sum(b.Unit_Sold) as Unit_Sold_YTD
  from (select WeekIndex
              ,MonthIndex
              ,YearIndex
          from table1
         group by WeekIndex
                 ,MonthIndex
                 ,YearIndex
       ) as a
       inner join table1 as b
               on b.YearIndex=a.YearIndex
              and b.MonthIndex<=a.MonthIndex
              and b.WeekIndex<=a.WeekIndex
 group by a.WeekIndex
         ,a.MonthIndex
         ,a.YearIndex
 order by a.WeekIndex
         ,a.MonthIndex
         ,a.YearIndex

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources