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.
| Author |
Topic |
|
mdelgado
Posting Yak Master
141 Posts |
 Posted - 2003-01-31 : 15:16:58
|
| Hello all (again)...I need some help in creating a rolling 30 day calculation.I have the following table:CREATE TABLE [tempg2] ( [Date] [smalldatetime] NULL , [Demand] [money] NULL , [Shipped] [money] NULL ) This table contains shipped and Demand Data by Day going back forever.I would like to create a view on this table that selects Date, Demand, Shipped and "Percentage". I would like the new Percentage column to take the sum(shippped) for the last 30 days divided by the sum(demand) for the last 30 days. Thus, every day would be a new number calculated on the total demand and shipped for the last 30 days.For example:1/31/03 would give me Percentage for 1/1 thru 1/302/01/03 would give me Percentage for 1/2 thru 1/31 etc...Any help would be much appreciated...Thanks |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-01-31 : 15:25:07
|
| This should do the trick, or something similiar anyway:SELECT A.Date, SUM(B.Demand) as TotalDemand, SUM(B.Shipped) as TotalShipped, SUM(B.Demand)/SUM(B.Shipped) as PctFROMtempg2 AINNER JOINtempg2 BONB.Date BETWEEN DateAdd(d,-1,A.Date) and A.DateGROUP BY A.Date- Jeff |
 |
|
|
mdelgado
Posting Yak Master
141 Posts |
Posted - 2003-01-31 : 15:43:57
|
| It Works!thank you! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-01-31 : 17:09:22
|
| ooops... didn't know if you noticed, but the DateAdd() function was only subtracting 1 day, not 30 .... see the change, below:SELECT A.Date, SUM(B.Demand) as TotalDemand, SUM(B.Shipped) as TotalShipped, SUM(B.Demand)/SUM(B.Shipped) as Pct FROM tempg2 A INNER JOIN tempg2 B ON B.Date BETWEEN DateAdd(d,-30,A.Date) and A.Date GROUP BY A.Date - Jeff |
|
|
|
|
|
|
|
|
Create a Rolling 30day calculation...