Get data from Previous record (Cumulative Data)-- Urgent help required

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  May 4, 2013 at 2:10 pm

  #1612418

  play area...see if this makes any sense and can be worked upon

  SELECT TOP 1000000

  TranDate = Dateadd(dd, Abs(Checksum(Newid()))%Datediff(dd, '2011', '2014'), '2011'),

  ProdID = 1 + CAST(Abs(Checksum(Newid()) % 50000 ) AS INT),

  Qty = CAST(Rand(Checksum(Newid())) * 99 + 1 AS INT)

  INTO Transdata

  FROM sys.all_columns ac1

  CROSS JOIN sys.all_columns ac2

  CROSS JOIN sys.all_columns ac3

  GO

  CREATE CLUSTERED INDEX [CIX] ON [dbo].[TransData]

  ([ProdID] ASC, [TranDate] ASC )

  GO

  SELECT DATEADD ( MONTH , -1 * N , DATEADD ( mm , DATEDIFF ( mm , 0 , GETDATE ( )) , 0 )) AS TranDate

  INTO jlsdates

  FROM Tally t

  WHERE N >= 1

  AND N <= 24;

  CREATE CLUSTERED INDEX [CIX] ON [dbo].[jlsdates]

  ([TranDate] ASC)

  ;with jlscte as

  (

  SELECT jlsdates.TranDate

  , TransData.ProdID

  , SUM ( TransData.qty ) AS cumtot

  FROM

  jlsdates LEFT OUTER JOIN TransData

  ON jlsdates.TranDate > TransData.TranDate

  GROUP BY TransData.ProdID

  , jlsdates.TranDate

  )

  SELECT ProdID, TranDate, cumtot

  FROM jlscte

  WHERE

  (ProdId =1500)

  order by tranDate

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day