Running total

• Corobori

  Ten Centuries

  Points: 1316

  More actions

  August 17, 2005 at 9:49 pm

  #102712

  Hi,

  I need to make a report where I have got some sort of running total:

  The result should be (the sum might be incorrect I did them by hand), when I am giving 3/31/2005 as parameter.

  Date,Subscriber, Amount

  1/31/2005, 1, 45000

  2/28/2005, 1, 65000

  3/31/2005, 1, 80000

  1/31/2005, 2, 15000

  2/28/2005, 2, 22000

  3/31/2005, 2, 27000

  1/31/2005, 3, 15000

  2/28/2005, 3, 35000

  3/31/2005, 3, 44000

  create table #tblTempRetros(DateOpe datetime, Stock int, Subscriber int, Amount int)

  insert into #tblTempRetros VALUES ('10/1/2005', 1,1, 15000)

  insert into #tblTempRetros VALUES ('10/2/2005', 1,2, 5000)

  insert into #tblTempRetros VALUES ('10/5/2005', 1,3, 5000)

  insert into #tblTempRetros VALUES ('11/1/2005', 1,1, 15000)

  insert into #tblTempRetros VALUES ('11/2/2005', 1,2, 5000)

  insert into #tblTempRetros VALUES ('11/5/2005', 1,3, 5000)

  insert into #tblTempRetros VALUES ('1/1/2005', 1,1, 15000)

  insert into #tblTempRetros VALUES ('1/2/2005', 1,2, 5000)

  insert into #tblTempRetros VALUES ('1/5/2005', 1,3, 5000)

  insert into #tblTempRetros VALUES ('2/5/2005', 1,1, 20000)

  insert into #tblTempRetros VALUES ('2/10/2005', 1,3, 20000)

  insert into #tblTempRetros VALUES ('2/15/2005', 1,2, 7000)

  insert into #tblTempRetros VALUES ('3/4/2005', 1,1, 15000)

  insert into #tblTempRetros VALUES ('3/5/2005', 1,2, 5000)

  insert into #tblTempRetros VALUES ('3/5/2005', 1,3, 9000)

  insert into #tblTempRetros VALUES ('4/4/2005', 1,1, 15000)

  insert into #tblTempRetros VALUES ('4/5/2005', 1,2, 5000)

  insert into #tblTempRetros VALUES ('4/5/2005', 1,3, 9000)

  create table #tblPeriod (DatePeriod datetime)

  insert into #tblPeriod values ('1/31/2005')

  insert into #tblPeriod values ('2/28/2005')

  insert into #tblPeriod values ('3/31/2005')

  
  Jean-Luc
  www.corobori.com
  

• Roy Rogers

  SSCertifiable

  Points: 7712

  More actions

  August 18, 2005 at 7:26 am

  #583008

  I assume the data provided is with little error. See the corrected data and the solution

  SET NOCOUNT ON

  SET DATEFORMAT MDY

  create table #tblTempRetros(DateOpe datetime, Stock int, Subscriber int, Amount int)

  insert into #tblTempRetros VALUES ('10/1/2004', 1,1, 15000)

  insert into #tblTempRetros VALUES ('10/2/2004', 1,2, 5000)

  insert into #tblTempRetros VALUES ('10/5/2004', 1,3, 5000)

  insert into #tblTempRetros VALUES ('11/1/2004', 1,1, 15000)

  insert into #tblTempRetros VALUES ('11/2/2004', 1,2, 5000)

  insert into #tblTempRetros VALUES ('11/5/2004', 1,3, 5000)

  insert into #tblTempRetros VALUES ('1/1/2005', 1,1, 15000)

  insert into #tblTempRetros VALUES ('1/2/2005', 1,2, 5000)

  insert into #tblTempRetros VALUES ('1/5/2005', 1,3, 5000)

  insert into #tblTempRetros VALUES ('2/5/2005', 1,1, 20000)

  insert into #tblTempRetros VALUES ('2/10/2005', 1,3, 20000)

  insert into #tblTempRetros VALUES ('2/15/2005', 1,2, 7000)

  insert into #tblTempRetros VALUES ('3/4/2005', 1,1, 15000)

  insert into #tblTempRetros VALUES ('3/5/2005', 1,2, 5000)

  insert into #tblTempRetros VALUES ('3/5/2005', 1,3, 9000)

  insert into #tblTempRetros VALUES ('4/4/2005', 1,1, 15000)

  insert into #tblTempRetros VALUES ('4/5/2005', 1,2, 5000)

  insert into #tblTempRetros VALUES ('4/5/2005', 1,3, 9000)

  create table #tblPeriod (DatePeriod datetime)

  insert into #tblPeriod values ('1/31/2005')

  insert into #tblPeriod values ('2/28/2005')

  insert into #tblPeriod values ('3/31/2005')

  SELECT A.DatePeriod, B.Subscriber, SUM(Amount) Amount

  FROM

   #tblPeriod A

  JOIN

   #tblTempRetros B

  ON

   A.DatePeriod >= B.DateOpe

  GROUP BY A.DatePeriod, B.Subscriber

  ORDER BY B.Subscriber, A.DatePeriod

  DROP TABLE #tblTempRetros, #tblPeriod

  Regards,
  gova