CODEWITHSUNDEEP
sqlsql-server
phoenies
phoenies

Reputation: 547

How to write a SQL to cascadingly accumulate rows of data?

I have a work plan in DB as follows. For instance, the first row means that Team A has 10 hours' work to do in June (SP1006).

Team Sprint  WorkHours
A    1006    10
A    1007    20
A    1008    30
A    1009    40
B    1008    50
B    1009    60
B    1010    70

I want to derive it into the following form to show: at the end of each month, how many hours are left to be done, according to the work plan. As you see, the fields of the derived table depend on the original datatable too.

Team    SP1005  SP1006  SP1007  SP1008  SP1009  SP1010
A       100     90      70      40      0       0
B       180     180     180     130     70      0

I'm trying to do this on SQL Server 2000. This is really hard for me. Can someone give some help? Thanks a lot!

Edit: Or you can ignore the row-to-column transformation part, (which I know how to do,) just concentrate on the accumulation part.

Upvotes: 2

Views: 2510

Answers (2)

Niikola
Niikola

Reputation: 1462

Select Team,
       isNull([1006], 0)+isNull([1007], 0)+isNull([1008], 0)+isNull([1009], 0)+isNull([1010], 0) as SP1005,
       isNull([1007], 0)+isNull([1008], 0)+isNull([1009], 0)+isNull([1010], 0) as SP1006,
       isNull([1008], 0)+isNull([1009], 0)+isNull([1010], 0) as SP1007,
       isNull([1009], 0)+isNull([1010], 0) as SP1008,
       isNull([1010], 0) as SP1009,
       0 as SP1010
  From YourTable
 PIVOT (Sum(WorkHours) For Sprint in ([1005],[1006],[1007],[1008],[1009],[1010])) p

The problem is if you want this to be dynamic. In that case you should use dynamic sql query. Construction of dynamic query depends on several things:

  • Do you want variable number of columns or not

  • Do you want to pass:

    a) first month / last month or

    b) list of months (eg. comma separated)

    c) just start month and query should return columns up to the current month

    d) just start month and query should return columns up to the last month you have in table

UPDATED

Version for SQL SERVER 2000

Select Team,
       sum(SP1005) as SP1005,
       sum(SP1006) as SP1006,
       sum(SP1007) as SP1007,
       sum(SP1008) as SP1008,
       sum(SP1009) as SP1009,
       0 as SP1010
From (       
Select Team, 
       Case when Sprint in (1006, 1007, 1008, 1009, 1010) Then WorkHours  Else 0 end as SP1005,
       Case when Sprint in (1007, 1008, 1009, 1010) Then WorkHours  Else 0 end as SP1006,
       Case when Sprint in (1008, 1009, 1010) Then WorkHours  Else 0 end as SP1007,
       Case when Sprint in (1009, 1010) Then WorkHours  Else 0 end as SP1008,
       Case when Sprint in (1010) Then WorkHours  Else 0 end as SP1009
  From @t
) a  
 Group by Team

UPDATE 2: ADDED DYNAMIC STORED PROCEDURE (For SQL Server 2000+)

I assumed your Sprint column is integer.

Alter Procedure zzzGetTeamMonths( @FirstMonth int )
AS BEGIN
Declare @FirstMonthDate datetime
Declare @ActFirstMonth int
Declare @LastMonth int
Declare @LastMonthChar nvarchar(4)
Declare @nMonths int

Select @LastMonth=max(Sprint),
       @ActFirstMonth=min(Sprint)
  from zzzTest 
 Where Sprint >= @FirstMonth 

If @LastMonth >= @FirstMonth Begin
   -- Next line remove repeating columns from left side (for non-existant data)
   -- You can comment it out if you want to see all columns
   IF @ActFirstMonth>@FirstMonth Set @FirstMonth=@ActFirstMonth
   Set @nMonths = (@LastMonth/100-@FirstMonth/100)*12 + (@LastMonth-(@LastMonth/100)*100-@FirstMonth+(@FirstMonth/100)*100)
   Set @FirstMonthDate = convert(datetime, Right('0'+cast(@FirstMonth as varchar(4)),4)+'01',12) 
   Set @LastMonthChar = Right('0'+cast(@LastMonth as varchar(4)),4)

   Create Table #Months (id int, mnt nvarchar(4), mntp nvarchar(4)) 

   Insert Into #Months
   Select Number,
          Convert(nvarchar(4),Dateadd(month,number,@FirstMonthDate),12),
          Convert(nvarchar(4),Dateadd(month,number-1,@FirstMonthDate),12)
     From master.dbo.spt_values 
    Where type='P' and number<=@nMonths

   Declare @cmd nvarchar(4000)
   Declare @tmp nvarchar(4000)
   Declare @col nvarchar(4000)

   Select @tmp = '',
          @col = ''

   Select @col = @col + '
,sum(SP' + mntp + ') as SP' + mntp,
          @tmp = @tmp + '
,Case when Sprint between ' + mnt + ' and ' + @LastMonthChar + ' Then WorkHours  Else 0 end as SP' + mntp
        from #Months


   Drop Table #Months

   Select @cmd = '
SELECT Team' + @col + '
,0 as SP' + @LastMonthChar + ' 
FROM 
(
Select Team' + @tmp + ' 
From zzzTest 
) a  
Group by Team'  
  Print @cmd
  Exec (@cmd)
End
RETURN 0
END
GO

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453897

For the accumulation part use a cursor. Unless the result set is very small (maybe <100 rows to process) when you can get away with a triangular join like. 

SELECT t1.Team, t1.Sprint,g.wh-SUM(t2.WorkHours)
 FROM tbl t1 JOIN tbl t2 ON t1.Team = t2.Team AND t2.Sprint <= t1.Sprint
 JOIN 
   (SELECT SUM(WorkHours) AS wh, Team  FROM tbl GROUP BY Team) g ON t1.Team=g.Team
 GROUP BY t1.Team, t1.Sprint,g.wh

But as the number of rows grows the amount of work required by the triangular join grows exponentially.

Upvotes: 2

Related Questions